Buzz
If you work in Excel with large datasets and need to organize and consolidate data into groups for easy control and accurate reporting, Excel has a helpful feature called Subtotal that facilitates convenient and useful data grouping. Using this feature provides quick results, allowing you to rely on and group the newly compiled data for detailed, comprehensive, and accurate reporting.
How to Group Data in Excel
Data Compilation by Groups in Excel
* Preparation Steps:
- Computer with current popular Excel programs: Excel 2013, Excel 2016
- An Excel file with multiple data sets to be grouped.
To compile data into groups in Excel, follow these steps:
Step 1:
Step 2: The Subtotal dialog box appears -> make selections as follows:
- Add each change in: Select the data field for grouping, For example, choose to group by Region.
- Use Function: Select the function for consolidation, For example, use the Sum function -> Calculate the sum of Quantity and Amount.
- Add subtotal to: Select the data fields for consolidation, here we need to calculate the sum of Quantity and Amount so you check the boxes next to Quantity (Column E) and Amount (Column F):
Step 3: After making selections -> click OK for the result as shown below:
- To make the grouping visually appealing, you can highlight the newly consolidated section:
- Thus, the data is consolidated by Region, concluding with an overall consolidated value for all 3 regions: North, Central, and South.
- Notice that Excel provides a handy collapse tool for Subtotal at the top left corner of the screen. For large datasets, simply click Number 1-> The application will display the overall sum of all grouped data:
- If you click Number 2-> The application displays summaries for each group along with the overall total for all groups:
The content above is a guide on how to Consolidate Data by Groups in Excel, which is simple yet effective. Review the detailed steps provided above to execute it yourself! Besides, organizing data in an Excel spreadsheet is crucial for effective data management. You can learn more about sorting data in Excel here.
Furthermore, you can also use the Subtotal function to calculate for a group in an Excel list. To understand more about this function, refer to the article on SUBTOTAL Function, a calculation function for a group in the list, previously shared by Mytour. Good luck!
