Course contents · Sort, filter and tables

Filter and subtotals

Open the practice in the trainer →

AutoFilter adds ▾ buttons to the headers: each holds the list of column values with check boxes and conditions ("greater than", "contains", "top 10"). Rows that do not match are hidden, not deleted; the numbers of the visible rows turn blue.

Totals of visible rows

A filter hides the whole row, so totals go above the table or in the header row, not beside the data. SUM adds all rows, even hidden ones. For a total that follows the filter, use SUBTOTAL(9, D2:D11): the first argument says what to calculate (9 — sum, 1 — average, 2 — count of numbers, 4 — max, 5 — min). AutoSum on a filtered table inserts exactly this function.

Tasks in the trainer

  1. Turn on the filter and keep only the South region.
  2. In G1 sum the visible rows only: SUBTOTAL with code 9.
  3. In I1 — a plain SUM of the same column. Compare: it ignores the filter.
  4. Change the condition: clear the region filter and keep orders with an amount greater than 1000.

Solve the tasks in the trainer →

Check yourself

  1. What happens to rows that do not pass the filter?
  2. Which formula sums only the rows visible after filtering?