Subtotals and grouping rows: reports that collapse and expand

Sort the list by the column that defines the groups

Subtotal creates a total every time the value changes in a column. If the list isn't sorted, you'll get a “North Total” every few rows. Click any cell in the Region column and go to Data › Sort & Filter › Sort…. Sort by Region, A to Z, and click OK.

In the example, the two North sales and the three South sales end up together.

Open Data › Outline › Subtotal

With any cell in the list selected, go to the Data tab and, in the Outline group (on the right of the ribbon), click Subtotal. Excel selects the whole data block for you and opens the Subtotal window.

An outline is the structure of levels that lets you collapse and expand rows; Subtotal builds it for you.

Pick the group, the function and the column to total

Fill in the three fields at the top:

• At each change in: Region (the column you sorted by).
• Use function: Sum. You can also choose Count, Average, Max or Min.
• Add subtotal to: check Amount and clear the rest.

Leave Replace current subtotals and Summary below data checked. If you'll print one region per page, also check Page break between groups. Click OK.

Check the totals: Excel uses the SUBTOTAL function

Excel inserts a North Total row and a South Total row under each group, plus a Grand Total at the end. Click C4 and look at the formula bar: it doesn't say SUM, but =SUBTOTAL(9,C2:C3). The 9 means “sum”.

The clever part is the grand total: =SUBTOTAL(9,C2:C8) also covers the total rows, but SUBTOTAL ignores other subtotals, so nothing is counted twice. Check it: 7,300 + 8,250 = $15,550.

Collapse and expand with the 1, 2 and 3 buttons

To the left of the row numbers you now have buttons labeled 1, 2 and 3, plus − signs next to each group:

• 1: only the grand total.
• 2: the totals by region (what your boss wants to see).
• 3: all the detail.

The + and − signs open or close a single group. You can also use Data › Outline › Show Detail and Hide Detail with a cell in the group selected.

Copy only the totals with Go To Special

Click button 2 to see only the totals. If you copy and paste now, Excel also pastes the hidden rows. To avoid that, select the visible block and go to Home › Editing › Find & Select › Go To Special…. Choose Visible cells only and click OK.

Now copy (Ctrl+C) and paste (Ctrl+V) into another sheet or an email: only North Total, South Total and Grand Total come through.

Group rows or columns by hand with Data › Outline › Group

You don't always want automatic totals. If your sheet already has its own sums (for example, a budget with columns from January to December and one column per quarter), you can build the collapsing yourself. Select the January to March columns and go to Data › Outline › Group. If you selected loose cells instead of whole columns, Excel asks whether to group Rows or Columns.

A bar with a − sign appears above the columns: one click and the quarter collapses down to its total.

Remove the subtotals or the outline when you're done

To get back to the original list, select a cell in the data, open Data › Outline › Subtotal again and click Remove All. Excel deletes the total rows and the outline, and leaves your data untouched.

To remove a manual grouping, use Data › Outline › Ungroup with the rows or columns selected, or Ungroup › Clear Outline to remove every level on the sheet.