Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
SUBTOTAL
AggregationAggregate only the visible (filtered) rows, ignoring other subtotals.
Difficulty
1What is it?
SUBTOTAL applies a function (SUM, AVERAGE, COUNT…) to a range but skips rows hidden by a filter and ignores other SUBTOTALs nested inside — so totals don't double-count. Use function number 9 for SUM of filtered rows (109 also skips manually hidden rows).
2What it looks like
SUBTOTAL(function_num, range)- function_num
- Which aggregation: 9 = SUM, 1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MIN.
- range
- The range to aggregate.
3When you use it
- A total that updates as you filter a table.
- Sum a column without double-counting subtotal rows.
- COUNT visible rows after applying a filter.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
SUBTOTAL sums only the visible rows. Filter to a region to see it react.
=SUBTOTAL(9, B2:B5)$10,000 (visible rows)| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Result |
| 2 | West | $10,000 (visible rows) | |
| 3 | East | ||
| 4 | South | ||
| 5 | North |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
Counts hidden rowsRows were hidden manually, not by a filter.Fix: Use 101–111 (e.g. 109 for SUM) to ignore manually hidden rows too.
6Better functions & alternatives
- AGGREGATE — Like SUBTOTAL but can also ignore error cells — more robust.
- SUM + FILTER — In Microsoft 365, sum a filtered spill directly.
Want SUBTOTAL already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →