How to Use Excel Consolidate to Summarize Data by Category
Question details
The user needs to summarize data, such as ticket counts or average days open by priority, across multiple worksheet ranges using the Consolidate tool.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing structured data by category (e.g., ticket counts, satisfaction ratings) across different sheets or ranges to create a unified report.
- Observed behavior
- The goal is to successfully combine and calculate totals, averages, or counts from multiple data ranges based on shared left-column category labels.
Ensure that all data ranges you plan to consolidate have consistent column structures, and verify that the category labels you want to group by are placed in the leftmost column of each range.
Use the Consolidate Tool to Summarize Data
Utilize the built-in Data Consolidate feature to combine and calculate values from multiple worksheets based on category labels.
The Consolidate tool is ideal for combining data from multiple ranges when the data shares the same row or column labels. It allows you to quickly calculate sums, counts, averages, and other metrics without writing complex formulas.
Open your workbook and click on the upper-left cell where you want the summarized data to be generated.
Navigate to the 'Data' tab on the ribbon and click on 'Consolidate' in the Data Tools group.
In the Consolidate dialog box, select the desired function (e.g., Count, Average, or Sum) from the 'Function' drop-down menu.
Click inside the 'Reference' box, then highlight your first source range on the worksheet. Make sure to include both the category column (leftmost) and the value column, then click 'Add'. Repeat this process for all other data ranges.
Under the 'Use labels in' section, check the box for 'Left column' so the data is grouped by your category labels, and click 'OK' to run the consolidation.
Effortlessly Consolidate Data with WPS Office Spreadsheets
WPS Spreadsheets provides a highly compatible and intuitive Consolidate tool, allowing you to seamlessly summarize and analyze data by category across multiple sheets. It is lightweight, free to use, and functions identically to Microsoft Excel.
- 1. Open your Workbook in WPS: Launch WPS Office, open your spreadsheet file, and select the destination cell for your data summary.
- 2. Access the Consolidate Tool: Navigate to the 'Data' tab in the top menu ribbon and click the 'Consolidate' button.
- 3. Configure the Calculation: Choose your calculation function (Sum, Count, Average) and add all the source data ranges containing your categories and values.
- 4. Apply Labels and Finish: Check 'Left column' under the labels setting to group by category, then click 'OK' to instantly generate your summarized table.
Frequently Asked Questions
Why is the Excel Consolidate tool returning incorrect or zero values?
This usually happens if your category labels are not in the leftmost column of the selected ranges, or if the value columns contain text instead of numbers. Ensure that text labels are strictly in the left column and values are to the right.
Can I use Consolidate to link back to the source data automatically?
Yes. In the Consolidate dialog box, you can check the 'Create links to source data' option. This will create an outline in your destination sheet that updates automatically if the original data changes.
How do I clear old references in the Consolidate tool?
Open the Consolidate dialog, click on the specific reference you no longer need inside the 'All references' list, and click the 'Delete' button on the right side before adding new ranges.
Does the Consolidate feature work across different workbooks?
Yes, you can consolidate data from entirely different workbooks. Ensure all source workbooks are currently open, click inside the 'Reference' box, and simply navigate to the other workbook to select the desired data range.
