MO-211 Expert Prep
Module 2 · Lesson 2.7

Calculate Data by Inserting Subtotals and Totals

Edit on GitHub

SQL's GROUP BY clause aggregates rows by a category and computes a summary for each group. Excel's Subtotal feature and SUBTOTAL() function do the same thing — but they work inside the grid itself, with the added intelligence to ignore other subtotal rows (preventing double-counting) and to respect filtered and hidden rows when you want them to.

Part 1: The SUBTOTAL Function

=SUBTOTAL(function_num, ref1, [ref2], ...)

SUBTOTAL() performs an aggregate calculation on a range, with two unique behaviors that distinguish it from SUM(), AVERAGE(), etc.:

  1. It ignores other SUBTOTAL() cells — so if you sum a column that already contains subtotal rows, those subtotals are not counted twice.
  2. It can ignore hidden or filtered rows — controlled by the function_num argument.

Function Number Reference

Function Include hidden rows Exclude hidden/filtered rows
AVERAGE 1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV 7 107
STDEVP 8 108
SUM 9 109
VAR 10 110
VARP 11 111
IMPORTANT
Function numbers **1–11** include rows hidden manually (via right-click > Hide). Function numbers **101–111** exclude ALL hidden rows — whether hidden manually or by a filter. For most use cases with AutoFilter, use **101–111**.

Examples

=SUBTOTAL(9, B2:B100)    → SUM of B2:B100, ignoring hidden rows (1–11 style)
=SUBTOTAL(109, B2:B100)  → SUM of B2:B100, excluding ALL hidden/filtered rows
=SUBTOTAL(3, A2:A100)    → COUNTA — counts non-blank visible cells only

Part 2: Inserting Automatic Subtotals via the Menu

The Data > Subtotal feature automatically inserts subtotal rows into your dataset, grouped by a column value. It also applies automatic outline grouping.

Requirements Before Using This Feature

  • Your data must be sorted by the column you want to group on. Subtotals are inserted every time the value in that column changes.
  • Your data must be in a plain range (not a formatted Excel Table — the feature is disabled for Tables).

Steps

  1. Click anywhere in your data range.
  2. Sort the data by the grouping column first (e.g., sort by Department).
  3. Go to Data tab > Outline group > Subtotal.
  4. Configure the dialog: - At each change in: Choose the column that defines your groups (e.g., Department). - Use function: Select the aggregate (Sum, Count, Average, etc.). - Add subtotal to: Check the columns you want totalled (e.g., Sales, Units).
  5. Click OK.

Excel inserts a subtotal row after each group and a grand total at the bottom. Outline grouping is applied automatically — you get +/- buttons to collapse each group.

Subtotal Dialog Options

Option Effect
Replace current subtotals Removes existing subtotals before adding new ones
Page break between groups Inserts a page break after each group (for printing)
Summary below data Places subtotal rows below each group (default); uncheck for above

Part 3: Removing Subtotals

To remove all automatically inserted subtotal rows:

Data tab > Outline group > Subtotal > Remove All

This removes the subtotal rows, the outline grouping, and the grand total row — restoring the dataset to its original flat state.

Part 4: SUBTOTAL vs. SUM — Why It Matters

Scenario Use SUM() Use SUBTOTAL()
Simple total, no filters ✓ ✓
Total that updates when filter is applied ✗ ✓ (use 109)
Total in a column that already has subtotal rows ✗ (double-counts) ✓ (ignores subtotals)
Total for visible rows only when rows are hidden ✗ ✓ (use 101–111)
TIP
When Excel's AutoFilter or table filter is active, the summary row at the bottom of an Excel Table automatically uses `SUBTOTAL()` — that's why the total updates when you filter. You can see this by clicking the total cell and checking the formula bar.

Part 5: The AGGREGATE Function — A Modern SUBTOTAL Superset

AGGREGATE() was introduced as a more capable successor to SUBTOTAL(). It does everything SUBTOTAL does, plus two important upgrades: it can ignore error values in the source range, and it can compute the LARGE/SMALL/PERCENTILE/QUARTILE family — which SUBTOTAL cannot.

Syntax

=AGGREGATE(function_num, options, ref1, [ref2], ...)

For the array form (used with LARGE, SMALL, PERCENTILE, QUARTILE):

=AGGREGATE(function_num, options, array, [k])

Both arguments are required. function_num selects the aggregation; options controls what to ignore.

Function Numbers — All 19

The first 13 mirror SUBTOTAL. Numbers 14–19 are exclusive to AGGREGATE.

Num Function Notes
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV.S
8 STDEV.P
9 SUM
10 VAR.S
11 VAR.P
12 MEDIAN New in AGGREGATE (not in SUBTOTAL)
13 MODE.SNGL New in AGGREGATE
14 LARGE Requires k argument
15 SMALL Requires k argument
16 PERCENTILE.INC Requires k (a percentile 0–1)
17 QUARTILE.INC Requires k (0–4)
18 PERCENTILE.EXC Requires k
19 QUARTILE.EXC Requires k

Options — What to Ignore

The options argument is a single number from 0–7 controlling which kinds of cells to skip during the calculation.

Option Ignores
0 Nested SUBTOTAL and AGGREGATE only (the default behavior of SUBTOTAL)
1 Hidden rows + nested SUBTOTAL/AGGREGATE
2 Errors + nested SUBTOTAL/AGGREGATE
3 Hidden rows + errors + nested SUBTOTAL/AGGREGATE
4 Nothing (treat as a normal range)
5 Hidden rows
6 Errors
7 Hidden rows + errors
IMPORTANT
The most common option for filtered data with messy values is **6** (ignore errors) or **7** (ignore hidden rows + errors). Pick **3** when you also want to suppress nested subtotal rows.

Why Use AGGREGATE Instead of SUBTOTAL?

  • Error tolerance — A single #DIV/0! or #N/A in the source range breaks SUM, AVERAGE, and SUBTOTAL. AGGREGATE with option 6 walks straight past errors and returns the aggregate of the clean rows.
  • LARGE/SMALL on filtered data — SUBTOTAL cannot answer "what is the second-largest value among the visible rows?" AGGREGATE can.
  • Single function for many statistics — Median, mode, percentile, and quartile all live behind one function rather than calling each by name.

Examples

=AGGREGATE(9, 5, A2:A100)         SUM ignoring hidden rows (same as SUBTOTAL(109,...))
=AGGREGATE(9, 6, A2:A100)         SUM ignoring errors — SUBTOTAL cannot do this
=AGGREGATE(1, 7, A2:A100)         AVERAGE ignoring hidden rows AND errors
=AGGREGATE(14, 6, A2:A100, 1)     LARGEST value, ignoring errors (k=1)
=AGGREGATE(15, 6, A2:A100, 3)     3rd-SMALLEST value, ignoring errors
=AGGREGATE(17, 6, A2:A100, 2)     QUARTILE 2 (median) ignoring errors
=AGGREGATE(16, 6, A2:A100, 0.9)   90th PERCENTILE ignoring errors
TIP
The exam scenario "your dataset contains some `#N/A` lookup errors but you still need the running total" maps directly to `=AGGREGATE(9, 6, range)`. Memorise that `9` = SUM and `6` = ignore errors.
NOTE
`AGGREGATE` was introduced in Excel 2010. It is fully supported in all current Excel versions but **does not exist** in Excel 2007 and earlier — the Compatibility Checker (lesson 1.9) flags it as a "significant loss of functionality" when saving to legacy formats.

Official Resources