Excel Subtotal can insert total rows after each group in a sorted list and add a grand total at the bottom. It also creates an outline so you can collapse detail rows and show only summary totals.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac when a list is sorted by category and needs group totals without hand-built SUM formulas.
Add automatic subtotals to a sorted list
Sort by the grouping column first so Excel knows where each group begins and ends.
- Sort the data by the column you want grouped, such as Region.
- Click inside the data range.
- Open Data > Subtotal.
- Set At each change in to the sorted grouping column.
- Choose the function, such as Sum, Average, or Count.
- Under Add subtotal to, choose the numeric column or columns to total.
- Leave Summary below data checked if you want totals after each group.
- Click OK.
Excel inserts subtotal rows and a grand total, plus outline buttons in the margin for expanding or collapsing each group.
Use the SUBTOTAL formula directly
Use this when you want a subtotal in one cell without inserting outline rows.
- Click an empty cell near the data.
- Enter =SUBTOTAL(.
- Use a function number such as 9 for Sum, 1 for Average, or 3 for Count non-blank cells.
- Add the range, such as B2:B20, and close the formula.
- Press Enter.
Unlike a plain SUM, SUBTOTAL ignores rows hidden by filters and avoids double-counting other subtotal rows.
More control
Remove automatic subtotals
Go back to Data > Subtotal and click Remove All. Excel removes the inserted subtotal rows and outline.
Create nested subtotals
Sort by the outer and inner grouping columns first. Run Subtotal for the outer group, then run it again for the inner group with Replace current subtotals unchecked.
Copy visible totals only
Collapse the outline, select the visible rows, then use the visible-cells-only shortcut before copying so hidden detail rows are skipped.
Choose other functions
Common SUBTOTAL function numbers include 9 for Sum, 1 for Average, 2 for counting numbers, 3 for non-blank count, 4 for Max, and 5 for Min.
Sources
- Microsoft Support – Calculate subtotals and grand totals in a list of data (2025)
- Microsoft Support – SUBTOTAL function (2025)
- Microsoft Support – Group or ungroup rows or columns in a worksheet (2024)