Outlines and grouping

An outline groups detail rows or columns under their totals so you can fold the detail away and read the summary — twelve months folded under a year, say. The tools are in Data › Outline.

Group rows or columns

1. Select cells in the rows to group — for example the detail rows above a total, not the total itself.
2. Open Data › Outline › Group and choose Rows. For columns, select cells in the columns and choose Columns.

An outline gutter appears to the left of the row numbers (or above the column letters). In it:

• A bar runs alongside the grouped rows or columns.
• A small box sits beside the row after the group, or the column after it — the summary. It shows − while the group is open and + while it is folded.
• The corner where the gutter meets the headings holds numbered level buttons.

Group rows that are already grouped to nest them one level deeper. An outline can be up to seven levels deep.

Fold and unfold

The status bar says Detail hidden. or Detail shown.

Hide detail and Show detail act on the innermost group that contains the selection, or on the group whose summary the selection is on, for rows and columns together. If the selection isn’t in a group, the status bar says Nothing here is inside a group.

The Level menu appears once the sheet has an outline. Its first item, 1 — summaries only, folds everything; the last, such as 3 — everything, unfolds everything; the numbers between show that many levels. It applies to row and column outlines together, while the numbered buttons in each gutter apply to that gutter’s outline only. The status bar says Showing the summaries only. or Showing followed by the number of levels.

Folded rows and columns are hidden, not deleted: formulas still include them. Rows you fold are kept separate from rows you hid yourself, so unfolding a group doesn’t reveal rows hidden with Hide rows. Folding is saved with the workbook.

Ungroup

1. Select cells in the grouped rows or columns.
2. Open Data › Outline › Ungroup and choose Rows or Columns.

The selected rows or columns move out one level. If that removes a folded group, its rows are shown again.

Clear the outline

Click Data › Outline › Clear (shown once the sheet has an outline). Every group on the sheet, rows and columns, is removed and everything they hid is shown. The status bar says Outline cleared.

Build an outline automatically

Auto Outline works out the groups from the formulas on the sheet: a total that sums the rows above it becomes the summary of a group made of those rows, and a grand total that sums the totals becomes an outer group.

1. Select the block to outline, or a single cell for the whole sheet.
2. Open Data › Outline › Group and choose Auto outline by rows, or Auto outline by columns for totals that sum the columns to their left.

The status bar says, for example, Made 5 groups from the totals. If no formula totals the rows above it, it says Nothing here totals the rows above it, so there is no outline to work out. (or the same for columns).

Subtotals

Subtotal inserts a total row under each run of equal values in a column, and groups each run so it can be folded.

1. Sort the list by the column you want to subtotal by, so equal values sit together.
2. Select the list, including its header row. If you select a single cell, all the data on the sheet is used.
3. Click Data › Outline › Subtotal.
4. In the dialog titled Subtotal and the list’s address, set the options:

5. Click Subtotal.

Each run gets a row labelled with its value and total — for example North total — holding SUBTOTAL formulas. The grand total also uses SUBTOTAL over the whole list; because SUBTOTAL leaves out other SUBTOTAL results, nothing is counted twice. The status bar says, for example, Added 4 subtotal rows and a grand total.

To remove subtotals, select the list, open Subtotal and click Remove all. The status bar says Removed followed by the number of subtotal rows.