How to Consolidate Data in Excel Using Tools, Power Query & Formulas
Question details
The user needs to combine or summarize data from multiple ranges, worksheets, or files into a single consolidated view.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining data from multiple worksheets or external sources for reporting, summary, or data analysis.
- Observed behavior
- Data is successfully merged or summarized using built-in consolidation tools, Power Query, specific aggregation formulas, or PivotTables.
Ensure that all data ranges or worksheets you plan to consolidate share a similar structure, such as matching column headers and row labels, for the most accurate results.
Use the Built-in Consolidate Tool
Best for combining data from multiple ranges that share the same row and column labels into one summary range.
The Consolidate command is the quickest built-in method when dealing with similarly structured datasets. It can sum, average, or count data from multiple worksheets based on position or category labels.
Click on the specific cell where you want the upper-left corner of the consolidated data to appear.
Navigate to the Data tab on the ribbon and click on Consolidate in the Data Tools group.
In the Consolidate dialog box, select the function you want to use from the drop-down menu, such as Sum or Average.
Click inside the Reference box, select your first source range on the worksheet, and click Add. Repeat this step for all the ranges you wish to consolidate.
Check the boxes for 'Top row' and 'Left column' under 'Use labels in' if your data has headers. Click OK to generate the consolidated data.
Combine Data Using Power Query
Ideal for merging large datasets from different files, tables, or external sources that require transformation.
Consolidate Across Worksheets Using 3D Formulas
Perfect for summing or averaging the exact same cell reference across multiple identically structured sheets.
Consolidate Data Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful data consolidation tools, advanced 3D formulas, and PivotTables that work exactly like Microsoft Excel. Seamlessly manage large datasets and summarize information from multiple sheets in just a few clicks.
- 1. Open your workbook in WPS: Launch WPS Office Spreadsheet and open the file containing the data ranges you want to consolidate.
- 2. Access the Consolidate tool: Go to the Data tab on the top menu ribbon and click the Consolidate button.
- 3. Select ranges and functions: Choose your calculation function, add your target data ranges, and configure label settings.
- 4. Generate the report: Click OK to instantly merge and summarize the selected datasets into your destination worksheet.
Frequently Asked Questions
Can I consolidate data if the columns are in a different order?
Yes. If you use the Consolidate tool and check the 'Top row' and 'Left column' label options, the tool will match the data based on the category names rather than their strict column or row position.
What happens if the source data changes after consolidation?
If you used the Consolidate tool, you must check the 'Create links to source data' box before clicking OK. This ensures the consolidated summary updates automatically. If you used Power Query, you can simply click 'Refresh' on the Data tab to pull the latest changes.
Can I consolidate data from entirely different workbooks?
Absolutely. With both the Consolidate tool and Power Query, you can select source data ranges located in other workbooks. Make sure the other workbooks are accessible or currently open when establishing the connection.
Is Power Query available in older versions of Excel?
Power Query is built directly into the Data tab in Excel 2016 and newer versions. For Excel 2010 and 2013, it can be added as a free downloadable add-in from Microsoft.
