The Consolidation tool in Excel is used to summarise data from multiple worksheets into a single worksheet. For example, the expenses from different offices can be totaled on another worksheet.
Data can be consolidated in two ways:
- Consolidate by position: Used when the data is arranged in the same order and location on all worksheets.
- Consolidate by category The row and column label is used to match the data.
Consolidating by position or category
Before consolidating, ensure that the data is arranged in labelled rows and columns without blank rows or columns on seperate worksheets.
- Select the cell in the upper left corner of where you want the consolidated data to appear
- Click the Consolidate button in the Data Tools group of the Data tab
- Click the Function list arrow and select the function you want to use to summarise the data
- Click in the Reference field, click the worksheet tab, and then select the first range of data to consolidate
- Click the Add button
- Repeat steps 4 and 5 to select all the ranges you want to consolidate
- To copy the labels to the consolidated worksheet, click the Top row or Left column options
- Click the Create links to source data checkbox if you want the consolidation to automatically update, whenever the source ranges change.
- Click Ok
The Consolidate dialogue box appears
If the range of data is located on a different workbook, click the Browse button and locate the file
This refers to where the labels are located in the source ranges
The values from the source ranges are summarised using the chosen function on the consolidation sheet
Consolidating by formula
Using a formula to consolidate data is a more powerful method as you do not need to worry about any prescribed layout for the consolidating data, or ensuring they have the same labels.
- Select the cell where you want to put the consolidated data
- Type the formula to reference and summarise the source data
The formula below sums the total cell on three different cells from three different worksheets.
The consolidation will automatically update when the source data changes.