Consolidation by position is performed when it is planned to combine data located in the same cells of different ranges. Consolidation by category is performed when there are several ranges and the goal is to combine these data by rows or columns with identical labels.
Along with consolidation, it is also useful to apply outlining, which can be created automatically. Importantly, the worksheets intended for consolidation do not necessarily have to share the same structure.
Let us describe the process of data consolidation using the example of creating a consolidated table of expenses for the company Alliance LLC for the reporting period from January to March.
- Make sure that all ranges of the data to be consolidated are presented in list format.
- If consolidation is performed by position, ensure that the layouts of all ranges match.
- If consolidation is performed by category, ensure that the column or row labels to be combined are identical (case-sensitive).
So, check that your workbook contains three sheets — January, February, March — with the data in the format.
In addition, the workbook must also contain a Totals sheet, where the resulting table after consolidation will be placed.
- Select the top-left cell of the range where the consolidated data should be placed. In our case, select cell A2 of the Totals worksheet.
Select the Consolidate command located in the Data Tools group on the Data tab of the ribbon. The Consolidate dialog box will appear.
- From the Function drop-down list, choose the so-called summary function. This function defines the type of calculation performed when combining the data in the consolidation table. The following functions are available: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev (biased), StdDev (unbiased), Var (biased), Var (unbiased).
In this case, choose Sum. - Click in the Reference field, open the sheet containing the first data range for consolidation, enter the reference to this range (in this case January!$A$2:$E$8), and click Add. As a result, the reference to the range will be added to the All references list. Repeat this step for all ranges to be consolidated (in this case, February!$A$2:$E$8 and March!$A$2:$E$8).
- If the consolidation table should be updated automatically whenever the source data changes, and later there will be no need to change or add data ranges, select the checkbox Create links to source data (this is what we should do in this case).
- If consolidation is performed by position, leave all fields in the Use labels in group empty. In MS Excel, the labels of source rows and columns are not copied into the consolidated data. If you need labels in the consolidated data, copy them manually. In our case, this checkbox is not selected.
- If consolidation is performed by category, in the Use labels in group select the checkboxes corresponding to the location of labels in the source ranges: top row, left column, or both. Any labels not matching those in other source areas will appear in separate rows or columns in the consolidated data. In our case, this checkbox is selected.
- Click OK.
As a result, a consolidated table will be created, shown :
