What Is Data Consolidation in Excel?
Microsoft Excel features a data-consolidation function that allows
several tables to be consolidated into a single summary report.
Consolidating the data often facilitates easier editing and viewing of
information since it can be seen in aggregate form as a master
spreadsheet. There are three basic ways to consolidate data in Excel: by
position, category and formula. To locate the command, start by
selecting the Data menu and then the Consolidate command, which will
then prompt you to select one of the three consolidation options.
Consolidate by Position
The
consolidate by position function works best when the data in the
separate tables is consistent in position and arrangement. Using the
consolidate by position function will move the contents from column A,
for example, from all of the separate spreadsheets into a single
spreadsheet. In addition, the aggregate consolidated spreadsheet can be
set to automatically update when the data in the source spreadsheet
changes.
Consolidate by Category
Consolidating
by category allows spreadsheets with slightly different layouts, but
consistent labels, to be consolidated into a master spreadsheet. The
data will be extracted from the spreadsheet and organized automatically
into the master spreadsheet. In order for this too to work properly, you
must go into the "Insert" menu and define the label's name, and all
spelling and capitalization must be identical for it to properly mesh.
Consolidate by Formula
Consolidation
by formula uses a 3-D reference, which is a reference to a range that
spans two or more worksheets in a workbook, to execute the
consolidation. This method is most effective when you're working in a
single Excel file with several worksheets. A cell contains a formula
that references portions of another worksheet to be consolidate, which
is effective when the worksheets are not identical in layout. If the
worksheets are identical, then the worksheet name can be used as part of
the consolidation formula.