How to Group and Summarize Data in Excel (3 Suitable Ways)

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.

group and summarize in excel

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.


  • 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.

Group and Data Summarizing with Excel Subtotal Tool

  • 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.


  • To begin with, select the cell where you want to put the formula.
  • After that, enter the formula into that selected cell.
  • Then, press Enter to see the result.

Combine Excel IF and SUMIF Functions to Summarize Data by Group

🔎 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.


  • In the first place, select the whole dataset.
  • Then, go to the Insert tab from the ribbon.
  • Further, click on PivotTable.

Categorize and Summarization of Data in Excel with Pivot Table

  • 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.

group and summarize in excel output

  • Now, place the Month and Product in the Rows field and Sales in the Values field. You can do this by dragging the item.

group and summarize in excel output

  • Finally, you can see the table is created in your worksheet. And the table is grouped and summarized.

group and summarize in excel output

Read More: How to Summarize Text Data in Excel

Download Practice Workbook

You can download the workbook and practice with them.


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.

Related Articles

<< Go Back to Summarize Data In Excel | Data Analysis with Excel Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF