When working with Excel, there will almost always come a time when you need to take the massive amount of data that you’re tracking and summarize it in one way or another. This summary can be achieved through data consolidation. That is, taking detail data and summarizing it in another area of the workbook.
Data Consolidation is always very useful when you have several users filling in key areas and the data from all users needs to be displayed on one master worksheet. Through consolidation, each user is able to maintain their own version of the spreadsheet.
Excel offers three methods for consolidation of your data.
Of the three consolidation methods we’re discussing in this section, Three Dimensional Formulas offer you the most flexibility. Three Dimensional Formulas are simply formulas that reference cells located in other worksheets or workbooks.
With Data Consolidation by Position, the only caveat is that each worksheet that you are using to consolidate into a master worksheet MUST be identical in both structure and layout. This works best if you can create a master worksheet, then make copies for all of the users. This way, when you bring the data back together for consolidation, everything lines up and totals where it’s supposed to.
Data Consolidation by Category works really well when you’re working with lists of data that may not be laid out identically but, at the very least, contain IDENTICAL field (or column) labels.