Grouping rows and inserting subtotals helps you organize, collapse/expand, and summarize sections of data. When you have long lists with categories or repeated groupings, this makes your worksheet much more navigable and readable.
In this article, you’ll learn how to group rows and insert subtotals in Excel, using the SUBTOTAL function and nested subtotals.
Here’s how to group rows and add subtotals in Excel using the Subtotal feature:
➤ Open your dataset in Excel.
➤ Sort the data by the column you want to group, such as Region.
➤ Go to the Data tab and click Subtotal.
➤ In the dialog box, select At each change in: Region, choose Use function: SUM, and check Add subtotal to: Revenue.
➤ Click OK. Excel will insert subtotal rows and a Grand total at the bottom.
➤ Use the + and – buttons on the left to expand or collapse grouped rows.
Using the Subtotal Feature to Group Rows and Add Subtotals in Excel
In the following dataset, we have sales data by region and product. Column A lists the Region Names, Column B shows the Product Names, Column C contains the Units Sold, and Column D records the Revenue generated from each product.
We’ll use this dataset to demonstrate how to group rows and add subtotals in Excel step by step.
The Subtotal feature in Excel is the easiest way to group rows automatically and calculate totals for each category. It allows you to quickly summarize large datasets without writing any formulas. When you use this option, Excel inserts subtotal rows for each group and adds an outline that lets you collapse or expand the grouped sections.
Here’s how to do it:
Step 1: Sort the Data by the Grouping Column
Before you can insert subtotals, Excel requires the dataset to be sorted by the column you want to group by.
➤ Open your Excel file and select any cell in the dataset.
➤ Go to the Data tab and click Sort A to Z in the Sort & Filter group.
➤ Choose the Region column for sorting.
➤ This ensures all entries for the same Region appear together, which is necessary for Excel to recognize the groups.
Step 2: Open the Subtotal Dialog Box
➤ Click any cell inside the dataset.
➤ Go to the Data tab on the Ribbon.
➤ In the Outline group, click the Subtotal button.
➤ This will open the Subtotal dialog box where you can set the conditions for grouping and totaling.
Step 3: Configure the Subtotal Options
In the Subtotal dialog box, you’ll see three main dropdown lists and a few checkboxes.
Set them as follows:
➤ At each Change in box, set Region. This tells Excel to insert a subtotal every time the Region value changes.
➤ Select Sum in Use function box. Choose Sum since we want to total Units Sold and Revenue.
➤ Check both Units Sold and Revenue in Add subtotal to. This means Excel will add subtotals for both these columns.
➤ If this is your first subtotal leave this option Replace current subtotals unchecked.
➤ Check this option Summary below data. It ensures the subtotal row appears below each group.
➤ Finally, click OK.
Step 4: View the Grouped and Subtotaled Data
➤ After clicking OK, Excel will instantly insert subtotal rows after each Region group, display outline buttons (1, 2, 3) on the left to collapse or expand groups, and add a Grand Total row at the end.
➤ Each Region now has its own subtotal, and the outline levels on the left let you control how much detail you want to see.
Step 5: Use the Outline Buttons to Collapse or Expand Groups
➤ On the left side of your worksheet, you’ll notice small minus (–) and plus (+) icons. These are outline symbols that let you collapse or expand the grouped rows.
➤ Click the – sign next to the all regions subtotal to hide its details.
➤ Click the + sign to show the details again.
➤ Use the outline level buttons (1, 2, 3) at the top-left corner to expand or collapse all groups at once.
➤ For example: Level 1 shows only the Grand Total, Level 2 shows all Region subtotals, and Level 3 shows full details with all products.
Using the SUBTOTAL Function to Group Rows Manually in Excel
The SUBTOTAL function gives you more flexibility when summarizing data. Unlike the automatic Subtotal feature, this method lets you create your own subtotal rows, choose which rows to include, and decide how hidden or filtered rows are treated.
Here’s how to do it:
Step 1: Insert Blank Rows for Subtotals
Before you start grouping, insert blank rows where you’ll display the subtotals.
➤ Place your cursor below the last entry for each Region. For example, below row 4 for East.
➤ Right-click on row 5 and choose Insert.
➤ Repeat for each region block.
➤ You should now have a blank row after each region group where subtotals will appear. For example, East Total, North Total, South Total, and West Total.
Step 2: Apply the SUBTOTAL Formula
The SUBTOTAL function can perform different calculations such as SUM, AVERAGE, COUNT, MIN, or MAX.
Here’s the syntax:
=SUBTOTAL(function_num, range1, [range2], …)
➤ In this syntax, function_num determines the type of calculation such as 9 refers to SUM, 1 refers to AVERAGE, and 2 refers to COUNT.
➤ For this example, we’ll use 9 to calculate the SUM.
➤ Now, insert the subtotal formulas in the blank rows you created. Example for the East Total.
➤ Click the blank cell under the Units Sold column such as cell C5.
➤ Enter this formula:
=SUBTOTAL(9, C2:C4)
➤ Press Enter. Excel will show 33, the total units sold in the East Total.
➤ Next, for the Revenue column D5:
=SUBTOTAL(9, D2:D4)
➤ Press Enter. This returns 1,650, the total revenue for East.
➤ Repeat similar formulas for the North, South, and West regions.
➤ Example for North Region:
=SUBTOTAL(9, C6:C7)
=SUBTOTAL(9, D6:D7)
➤ Example for South Region:
=SUBTOTAL(9, C9:C10)
=SUBTOTAL(9, D9:D10)
➤ Example for West Region:
=SUBTOTAL(9, C12:C14)
=SUBTOTAL(9, D12:D14)
➤ You can also change function numbers as 109 to exclude hidden rows as needed.
Step 3: Add a Grand Total Row
To calculate a grand total that includes all regions, use SUBTOTAL function again because it automatically ignores other subtotal formulas within the range.
➤ In the cell below your last subtotal such as cell C16, enter:
=SUBTOTAL(9, C2:C15)
➤ Press Enter. This will return 87 for Units Sold.
➤ And in cell D16 type this formula:
=SUBTOTAL(9, D2:D15)
➤ Press Enter. This will return 4,350 for Revenue without double-counting the regional subtotals.
Step 4: Group the Rows Manually
Once your subtotal formulas are ready, you can group the detail rows to make your dataset easier to collapse and expand.
➤ Select the detail rows for one region. For example, rows 2 to 4 for East.
➤ Go to the Data tab and click Group under the Outline section.
➤ In the Group dialog box, select Rows and click OK.
➤ Repeat for other regions such as North, South, and West. Now, you’ll see small – and + buttons on the left, allowing you to hide or show each group’s details.
➤ Finally, select the entire range A2:D15 excluding the Grand Total row and click Data >> Group to create an outer group for the whole dataset.
➤ To ungroup later, select the grouped rows and click Data >> Outline >> Ungroup >> Check Rows >> Ok.
Creating Multi-Level Subtotals (Nested Subtotals) in Excel
Sometimes your dataset has more than one level of grouping. For example, you may want to see subtotals by Region and also by Product within each region.
Excel allows you to do this easily by using nested subtotals, which means adding multiple subtotal levels in the same dataset.
Here’s how to do it:
Step 1: Sort the Dataset
Before applying multi-level subtotals, sorting is very important.
➤ Click any cell in your dataset.
➤ Go to the Data tab and click Sort in Sort & Filter.
➤ In the Sort dialog box, first, sort by Region and Order (A to Z). Then, click Add Level, select sort by Product and Order (A to Z).
➤ Click OK. Now your data is grouped properly for nested subtotals.
Step 2: Apply the First Subtotal (by Product)
We’ll first subtotal the Units Sold and Revenue for each Product.
➤ Go to the Data tab and click Subtotal in the Outline group.
➤ In the Subtotal dialog box, select Product in At each change in, SUM in Use function, and check Units Sold, Revenue in Add subtotal to.
➤Leave this option Replace current subtotals unchecked if this is your first subtotal .
➤ Make sure the Summary Below Data is checked.
➤ Click OK.
➤ Excel will insert subtotal rows for each Product (A, B, C, D) showing total Units Sold and Revenue.
Step 3: Add the Second Subtotal (by Region)
Now we’ll add another subtotal level for Region without removing the first one.
➤ Go back to the Data tab and click Subtotal again.
➤ This time, in the dialog box, select Region in At each change in, SUM in Use function, and check Units Sold, Revenue in Add subtotal to.
➤ Uncheck Replace current subtotals.
➤ Click OK.
➤ Excel now adds a second level of subtotals for each Region above the product-level totals.
➤ Now your data has two subtotal levels. Each Product subtotal shows totals within a region and each Region subtotal sums all products in that region.
➤ Finally, a Grand Total appears at the bottom of the dataset.
Step 4: Collapse and Expand Groups
➤ You’ll notice small + and – buttons appear on the left side of the worksheet.
➤ Click the – button beside a region to hide its detailed rows.
➤ Click the + button to expand and view the details again.
➤ At the top-left corner, you’ll also see 1, 2, 3, 4 outline levels where Level 1 shows only the Grand Total, Level 2 shows Region totals, Level 3 shows Product details and Level 4 shows all subtotals.
➤ Click these level numbers to switch views quickly.
Step 5: Remove Subtotals (If Needed)
If you want to remove the subtotals later:
➤ Go to Data and click Outline >> Subtotal >> Remove All.
➤ This clears all subtotal rows and outline levels, returning your data to its original form.
Frequently Asked Questions
How do I add subtotals in Excel?
To add subtotals, select any cell in your dataset. Then go to the Data tab and click Subtotal. In the dialog box, choose the column to group by, pick the function such as Sum or Average, and select the columns where you want totals. Click OK and Excel will insert subtotal rows automatically.
Can I use the Subtotal feature in an Excel Table?
No. The Subtotal option does not work when your data is formatted as an Excel Table. To use subtotals, first click anywhere inside the table, go to the Table Design tab, and click Convert to Range. After that, the Subtotal button becomes available.
Do subtotals update when I change data?
Yes. When you edit any cell in the dataset, the subtotal values update automatically. You do not need to reapply them. However, if you add or delete rows, you may need to remove and insert the subtotals again to refresh the structure.
Wrapping Up
Grouping rows and adding subtotals in Excel helps you summarize large datasets quickly. It makes reports easier to read and understand. You can group data, show totals for each category, and collapse or expand sections as needed.
This feature is useful for sales reports, financial summaries, or any data that has repeating categories. You can also create multiple levels of subtotals to see detailed and overall results at the same time.
Once you finish your analysis, you can remove the subtotals to get back the original dataset.
