10. Grouping Rows and Subtotals

A long table is easier to handle when you can "collapse" it: show only the regional totals on screen and expand the details with a click. In real Excel the outline does this:

• select the rows of a group, then Data › Outline › Group (Shift+Alt+→; to ungroup, Shift+Alt+←). A bracket and a "−" button appear on the left, and the button collapses the rows;
• the level buttons 1 2 3 in the top-left corner show only the grand total, the group totals, or all the details;
• Data › Subtotal does it all at once: it splits a table sorted by region into groups, inserts "South Total" rows and a grand total, and builds the outline.

In the total rows Excel does not write SUM but SUBTOTAL(9, range). The first argument is the function number: 9 is sum, 1 is average, 2 is count, 4 is max and 5 is min. Numbers 101–111 do the same but also skip rows you hid by hand.

It is used for two properties:

• rows hidden by a filter are not counted, so the total shows only what is visible;
• other SUBTOTALs inside the range are skipped, so a grand total over the whole column does not count the group totals twice.

The trainer has no grouping buttons. Here the table is already sorted by region (South is rows 2–4, North is rows 5–6, Central is rows 7–10), and you calculate the group totals in the summary on the right, just as Subtotal would insert them.