While working with Microsoft Excel, we may have some data that we need to group and summarize. By minimization and maximization, the gathered data can be enlarged or deflated, accordingly. Summarized data are those that include all occurrences within a certain group. In this article, we will demonstrate different ways to group and summarize data in Excel.
How to Group and Summarize Data in Excel: 3 Suitable Ways
For this, we are going to use the following dataset. The dataset contains three months; January, February, and March. Also some products and the sales of each product in those three months. Now, we want to group the dataset by the month and summarize the total sales of each month.
1. Grouping and Summarizing Data with Excel Subtotal Tool
The original dataset must be properly organized and must not have any empty cells in able to use the Excel Subtotal tool. This will summarize and group the data. Let’s look at the procedures to use this tool to group and summarize data in Excel.
STEPS:
- Firstly, select any cell of your dataset. In our case, we select cell B5.
- Secondly, go to the Data tab from the ribbon.
- Thirdly, click on the Subtotal tool under the Outline category.
- Consequently, the Subtotal dialog box will appear.
- As we want to group them by month, so we select Month in At each change in: the drop-down section. We need to summarize the Sales of each month so, we select the Sum in Use function: drop-down menu.
- Sequentially, in the Add subtotal to: option, checkmark the Sales box.
- Make sure you check the Replace current subtotal and Summary below data box.
- Further, click on the OK button to complete the procedures.
- Thus, you will be able to see that the month of January is in a group and the total sales of January will be calculated below the January group. Similarly, this will group and summarize the February and March months.
- As a bonus, this will also calculate the Grand Total.
- If you don’t want to display the detailed information, you can click on the minus (–) button.
- This will hide the detailed information of the dataset.
- You can also undo this by clicking on the plus (+) button.
Read More: How to Summarize Subtotals in Excel
2. Combine Excel IF and SUMIF Functions to Summarize Data by Group
The IF function enables us to compare values. And the SUMIF function returns the total of the fields that satisfy a specific condition. We can combine those functions to summarize data by group. Let’s see the steps down.
STEPS:
- To begin with, select the cell where you want to put the formula.
- After that, enter the formula into that selected cell.
=IF(B5=B4,"",SUMIF(B:B,B5,D:D))- Then, press Enter to see the result.
🔎 How Does the Formula Work?
- SUMIF(B:B,B5,D:D): B:B is the columns we would like to total depending on, and D:D is the column you would like to total the entries in. Cell B5 is the relative cells we would really like to total depending on. This will calculate the total of the fields.
- IF(B5=B4,””,SUMIF(B:B,B5,D:D)): B4 is the column heading, and cell B5 is the relative cell. If the condition matches this will return the result.
- Drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.
- Finally, you can see the sum of data by group.
Read More: How to Create a Summary Sheet in Excel
3. Categorize and Summarize Data in Excel with Pivot Table
Large volumes of data may be easily summarized interactively using pivot tables. Excel PivotTable is a fantastic tool to group and summarize data. Let’s follow the instructions to use pivot tables for the group and summarize data.
STEPS:
- In the first place, select the whole dataset.
- Then, go to the Insert tab from the ribbon.
- Further, click on PivotTable.
- Thus, this will display the PivotTable from table or range dialog box.
- The range will automatically be placed, as we select the data previously. So, you can see that selected range in the Table/ Range field under Select a table or range option.
- In Choose where you want the PivotTable to be placed, select New Worksheet.
- Furthermore, click on the OK button.
- This will take you to a new sheet where you can organize the pivot table.
- To display the PivotTable Fields click on any cell of PivotTable1 as shown in the screenshot below.
- Now, place the Month and Product in the Rows field and Sales in the Values field. You can do this by dragging the item.
- Finally, you can see the table is created in your worksheet. And the table is grouped and summarized.
Read More: How to Summarize Text Data in Excel
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above ways will assist you to group and summarize data in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.
