How to Use Subtotals in Excel: Automatic Group Totals

Quick answer

Sort the list by the column you want to group by, then go to Data → Outline → Subtotal. In the dialog set At each change in to that same column, pick a function such as Sum, tick the columns to total under Add subtotal to, and click OK. Excel inserts a subtotal row after every group, a grand total at the bottom, and an outline down the left edge¹. If the Subtotal button is greyed out, your data is an Excel Table — convert it back to a range first.

The Subtotal command does several jobs in one click: it inserts formulas, groups the rows and builds a collapsible outline so you can show just the totals. It is also the feature people abandon fastest, because two things go wrong straight away — the groups come out scrambled, or the button will not click at all.

Sort first, or the groups come out wrong

Subtotal starts a new group every time the value in your chosen column changes, going down the list. It does not gather matching values together; it only notices the change. So if Region reads North, South, North, South, you get four groups of one row each instead of two groups of two.

Sort that column first: select a cell in it and use Data → Sort & Filter, or right-click → Sort. Your list also needs a single row of column headings and no blank rows inside it — Excel reads those headings into the dialog, and a blank row cuts the range short.

The Subtotal dialog, field by field

With a cell inside the list selected, open Data → Outline → Subtotal. Four things matter:

1. At each change in — the grouping column. This must be the column you just sorted.
2. Use function — Sum by default, but Count, Average, Max, Min, Product and the standard-deviation and variance options are all there.
3. Add subtotal to — tick every column that should get a total. Excel pre-ticks the last numeric column, which is often not the one you meant.
4. The three checkboxes: Replace current subtotals, Page break between groups (useful when each group should print on its own sheet of paper) and Summary below data, which puts each subtotal under its group rather than above it.

Click OK and the sheet rearranges itself: a labelled subtotal row after each group, a grand total at the bottom, and the outline symbols appear at the left.

Why the button is greyed out

This is the most common dead end. The Subtotal command does not run on an Excel Table, so when your range is formatted as one the button is simply disabled. A Table has its own total row, but that totals the whole Table — it does not group anything.

Convert the Table back to an ordinary range: click any cell in it, then Table Design → Tools → Convert to Range and confirm. The formatting stays; structured references become normal cell references. If you want the Table back later, remove the subtotals first, then select the range and press Ctrl+T.

What Excel actually writes

Look at any subtotal row Excel created and you will not find SUM. You will find something like =SUBTOTAL(9,C2:C8). The first argument is a function number, and 9 means Sum².

Two details make this function worth knowing on its own:

• SUBTOTAL ignores other SUBTOTAL formulas inside its own range. That is how the grand total at the bottom can cover the whole column without counting each group twice².
• The function number has two forms. Numbers 1 to 11 include rows you hid by hand; 101 to 111 leave them out. Both forms ignore rows removed by a filter². So SUBTOTAL(9,…) and SUBTOTAL(109,…) differ only when somebody hides rows manually.

You can write it yourself instead of using the command: put =SUBTOTAL(109,C2:C200) above a filtered list and the total follows the filter. AutoSum already does this — press it at the bottom of a filtered list and Excel writes a SUBTOTAL formula, not a SUM.

Showing just the totals

The numbered buttons in the top-left corner of the outline are the reason to use the command at all. 1 shows the grand total alone, 2 shows the grand total plus each group subtotal, and 3 shows everything. With two levels of subtotals you get a 4 as well. The plus and minus boxes beside individual groups expand and collapse that group only⁵.

Read carefully

Ctrl+8 is the standard Windows shortcut for showing and hiding the outline symbols, and Microsoft’s own Excel shortcuts page lists it³. If it does nothing in your build, the symbols can also be switched from File → Options → Advanced, under the worksheet display options.

Collapsing to level 2 and copying looks like it should give you a clean list of totals. It does not — a normal copy takes the hidden detail rows with it. Select visible cells only before copying, exactly as when copying a filtered list.

Two levels of subtotals

Totals by region and, inside each region, by product is a two-pass job. Sort by both columns first, region primary and product secondary. Run Subtotal with At each change in set to Region, then open it again, set it to Product and this time untick Replace current subtotals. Leave that ticked and the second pass wipes the first.

Nested subtotals are also where the command runs out of road. Once you want totals by two fields, a filter on a third and a layout you can rearrange, a PivotTable does the job better and without rewriting your sheet. Subtotals are for a sorted list you want to print or read; PivotTables are for analysis. The same boundary applies to formula solutions such as lookups on multiple criteria, which answer a question about one row rather than summarising many.

Removing them again

Open Data → Outline → Subtotal again and click Remove All¹. The inserted rows, the formulas and the outline all disappear. Deleting the subtotal rows by hand leaves the outline behind, so use the button.

On the exam

Outlining, grouping and subtotalling sit in the “Manage data cells and ranges” objective group of exam MO-210, one of the two largest at 25–30%⁴. The exam is hands-on inside real Excel and timed, so knowing that Subtotal needs a sort first — and that it refuses to run on a Table — is worth more than remembering the dialog layout. The MO-210 guide lists every objective group.

Related

• Copy only visible cells (skip hidden and filtered rows)
• AutoSum in Excel: what it is and how to use it
• Lookup with multiple criteria
• MO-210 Excel Associate exam guide

Sources