What Is Data Consolidation in Excel?

By Melissa Kelly

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.