Data Consolidation in Excel is a feature that allows users to combine and summarize data from multiple worksheets or ranges into a single worksheet. It performs calculations such as Sum, Average, Count, Maximum, and Minimum to create a summarized view of scattered data without manually copying or merging datasets.
- It’s especially useful for businesses or analysts who work with departmental budgets, sales reports, or regional performance data spread across different sheets.
- For example: A finance team can consolidate budget data from multiple years like 2016, 2017, and 2018 into one summary sheet using Excel’s built-in Consolidate tool.
Tip: If labels or spellings differ across sheets, Excel treats them as separate categories so consistency matters!
Ways to Consolidate Data in Multiple Worksheets
There are two ways to Consolidation Data:
- By Position: When the data, to be consolidated, presented in different sheets but share the same cell reference like the data in sheet1 starts from B2, and data in sheet2 also starts from B2 and the data has the same labels. Then Data Consolidation by position is used.
- By Category: When the data in sheet1 and sheet2 doesn't share the same cell reference but have the same labels then Data Consolidation by category is used.
Data Consolidation by Position
Here, I'll be taking an example in which I want to sum the data from 3 sheets named 2016, 2017, 2018(as shown below) to another sheet, consolidate, in the same workbook but a different sheet.
The data in the 3 sheets naming 2016, 2017 and 2018 is similar as seen in 2016, the sheet with the name consolidate will have the consolidated output of the previous sheets.
Note: Remember that in this method the data in every sheet must be at the same position i.e they share the same cell reference and same labels.
Step 1: In the Consolidate sheet, click on any cell where you want the consolidated data to appear.
Step 2: In the Data Tab> Data Tools>Consolidate
Step 3: The consolidate dialogue box will appear as shown in the image. In the Function box, you need to select the operation that you want to perform with the data of the sheets. Here, I'm selecting Sum.
Step 4: Click on the Collapse dialogue(marked in the circle in the image below) and open the sheet, that you want to consolidate, and select the data(as seen in Fig2). The selected data will appear in the Reference option and then click Add.
Step 5: After clicking Add, the reference will be seen in the All reference option(as shown in the image below). And add all the sheet ranges that you want to consolidate as above. Check on the Top row and Left column checkboxes under Use labels in and then click OK.
You'll get the consolidated data in the required worksheet.
Note: If you made any changes in the sheets then the changes won't appear in the consolidated data sheet.
Data Consolidation by Category
There is a problem in the previous method that the cell reference of the data in every sheet must be the same, if not so, excel will create another row/column for the data in the final consolidated sheet. this issue is resolved in consolidation by category. To use this method you need to make only one change in the steps mentioned above.
Step 1: In the Consolidate sheet, click on any cell where you want the consolidated data to appear.
Step 2: In the Data Tab> Data Tools>Consolidate
Step 3: The consolidate dialogue box will appear as shown in the image. In the Function box, you need to select the operation that you want to perform with the data of the sheets. Here, I'm selecting Sum.
Step 4: Click on the Collapse dialogue(marked in the circle in the image below) and open the sheet, that you want to consolidate, and select the data(as seen in Fig2). The selected data will appear in the Reference option and then click Add.
Step 5: Select all the checkboxes under Use labels in and then click OK.
The Data Consolidation feature in Excel is a powerful tool to summarize scattered data across multiple sheets. Whether you consolidate by position (when structure matches) or by category (when only labels match), this tool can save time, improve accuracy, and streamline your data analysis process.