Grouping and outlining in Excel - Cell-by-Cell Formula Trace

Sample Data

This data shows categories of items with their names and amounts. We will group rows by category to outline the data.

Cell	Value
A1	Category
B1	Item
C1	Amount
A2	Fruits
B2	Apple
C2	10
A3	Fruits
B3	Banana
C3	15
A4	Vegetables
B4	Carrot
C4	12
A5	Vegetables
B5	Broccoli
C5	8
A6	Vegetables
B6	Spinach
C6	5

Formula Trace

SUBTOTAL(9, C2:C3)

Step 1: C2:C3 values = [10, 15]

Step 2: SUM of [10, 15]

Cell Reference Map

    A        B         C
1 Category  Item     Amount
2 Fruits    Apple       10  <-- Included in subtotal
3 Fruits    Banana      15  <-- Included in subtotal
4 Vegetables Carrot     12
5 Vegetables Broccoli    8
6 Vegetables Spinach     5

The formula SUBTOTAL(9, C2:C3) references cells C2 and C3 which contain amounts for Fruits category.

Result

    A        B         C
1 Category  Item     Amount
2 Fruits    Apple       10
3 Fruits    Banana      15
4          Total:      25  <-- Result of SUBTOTAL(9, C2:C3)
5 Vegetables Carrot     12
6 Vegetables Broccoli    8
7 Vegetables Spinach     5

The subtotal 25 is the sum of amounts for Fruits (10 + 15). This can be shown as a total row after the grouped rows.

Sheet Trace Quiz - 3 Questions

What does the number 9 mean in the formula SUBTOTAL(9, C2:C3)?

A It means SUM function

B It means AVERAGE function

C It means COUNT function

D It means MAX function

Key Result

SUBTOTAL(function_num, range) calculates a summary like SUM or AVERAGE on a range, useful for grouped data.