Skip to content
Tutorials Center
My learning

Topics

Lesson 45 of 93 ยท Tables, Sorting and Filtering

Grouping, Outlines, Subtotals and Consolidate

Group and ungroup rows and columns, use outline levels, insert subtotals with Data > Subtotal, expand and collapse sections, and combine ranges with Consolidate by position or category.

Large lists are easier to read when you can fold away the detail and see the summary. Excel gives you three related tools: Group and Outline to collapse rows or columns, Subtotal to insert totals automatically at each change in a column, and Consolidate to combine several ranges into one summary. None of them needs a formula you write yourself, and all three are quick once you know the rules.

Excel cannot run on this site, so the menus are real Excel but read-only. The TryIts use a JavaScript stand-in that does what Subtotal and Consolidate do to your data, so you can see the structure of the result, including the grand total and the subtotal rows. They are teaching models, not Excel's code.

Group and ungroup

Grouping lets you hide a set of rows (or columns) behind a small +/- button.

  1. Select the rows you want to hide (for example the monthly detail rows under a quarter). Select whole rows by their numbers, or use any cell range and Excel asks Rows or Columns.
  2. Data > Outline > Group (shortcut Alt+Shift+Right Arrow). A bracket appears in the left margin with a - button.
  3. Click the - to collapse, the + to expand.
  4. To remove grouping, select the rows and choose Ungroup (Alt+Shift+Left Arrow), or Data > Outline > Ungroup > Clear Outline to remove it all.

Column grouping works the same way, with the brackets above the sheet. Groups can be nested: group the days inside a month, then group the months inside a year. Each nesting level is an outline level, shown as numbered buttons (1, 2, 3) in the top-left corner. Click 1 for the summary only, 2 for the next level, and the highest number for full detail.

Two details prevent confusion. First, grouped rows are hidden, not deleted: formulas still see them, but SUM over a range that includes hidden rows still counts them (only SUBTOTAL and AGGREGATE can skip hidden rows, see below). Second, the +/- buttons normally sit below the detail rows, so the summary row comes after its details. If you want the summary above, open the Outline dialog launcher (the small arrow at the bottom-right of the Outline group) and untick Summary rows below detail.

Subtotals: Data > Subtotal

The Subtotal command inserts total rows into a sorted list and builds the outline for you.

Prepare: the list needs a header row, no blank rows and no merged cells. Sort it by the column you want to group on (for example Region), because Subtotal inserts a row each time that column's value changes.

  1. Click a cell in the list and choose Data > Outline > Subtotal.
  2. At each change in: the column you sorted on (Region).
  3. Use function: Sum, Count, Average, Max, Min, Product and others.
  4. Add subtotal to: tick the column(s) to total (Amount).
  5. Leave Replace current subtotals ticked (so you can rerun it) and Summary below data ticked. Click OK.

Excel inserts a row after each region titled "North Total" and one Grand Total at the bottom, with outline buttons at the left. Level 1 shows the grand total only, level 2 the subtotals, and level 3 everything.

To copy only the summary rows to another place, collapse to level 2, select the visible cells, press Alt+; (select visible cells only), then copy and paste. To remove the subtotals, open Subtotal again and click Remove All.

The formula Excel writes is =SUBTOTAL(9, C2:C5). The first argument is a function number: 9 is SUM, 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN. Add 100 (for example 109) to also ignore rows that were hidden by hand (by right-click Hide or by grouping); numbers 1 to 11 only ignore rows hidden by a filter. The grand total uses SUBTOTAL too, so it does not double-count the subtotal rows above it, which is the real advantage over a plain SUM.

Subtotal works on a normal range, not an Excel Table: if you click inside a Table, the command is greyed out. Convert the Table to a range first (Table Design > Convert to Range), or use a PivotTable (PivotTable Basics) for a refreshable summary.

Run it: what Subtotal does to a list

JS Sorting, subtotals and a grand total that does not double-count

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Consolidate: combine several ranges

Data > Data Tools > Consolidate merges ranges (from one sheet, several sheets or other workbooks) and summarizes them with a function such as Sum, Average, Count, Max or Min. It is the tool for monthly branch sheets you want to roll into one table without writing formulas.

By position

Use this when every range has an identical layout: the same rows and columns in the same positions (for example, one sheet per month, products down the side, regions across the top).

  1. Create a new sheet and click the top-left cell where the result goes.
  2. Data > Consolidate, choose Function: Sum.
  3. Click Reference, select the first range (or type Jan!B2:E10), then Add. Repeat for every sheet.
  4. Leave Top row and Left column unticked and click OK.

Excel sums cell for cell: B2 in the result is Jan!B2 + Feb!B2 + Mar!B2. The layouts must match exactly, so check that no sheet has an extra row.

By category

Use this when the ranges contain the same labels in different orders or with some missing. Include the header row and the label column in each reference, and tick Top row and Left column under "Use labels in". Excel matches rows by label (and columns by header), so a product missing from one sheet simply contributes nothing. The text labels must match exactly, ignoring case; "Pen" and "Pens" are different.

Also tick Create links to source data if you want live formulas with an outline; without it, Consolidate pastes static numbers and you repeat the command to refresh. With links, the result has SUM formulas pointing at the sources and an outline to expand them.

For tables that keep growing, a formula approach is more flexible: SUMIFS over a combined list (Conditional Aggregates), a 3D reference such as =SUM(Jan:Mar!B2) when sheets have the same layout, or Power Query Append (Power Query Basics) to stack the tables and then pivot.

JS Consolidate by category versus by position

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Outline tips

  • Auto Outline (Data > Group > Auto Outline) creates groups from your formulas when summary rows contain SUMs of the rows above. It needs consistent formulas.
  • Show Detail / Hide Detail buttons act on the selected row; Ctrl+8 toggles the outline symbols.
  • Collapsed rows print collapsed, so you can print the summary view and the detail view from the same sheet (Page Setup, Page Breaks and Printing).
  • Outlines and filters interact oddly; remove one before using the other. For ad hoc slicing prefer AutoFilter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What must you do to a list before using Data > Subtotal?

    Subtotal inserts a row whenever the chosen column's value changes, so equal values must be next to each other. Sorting does that.
  2. 2. What does the first argument 109 mean in =SUBTOTAL(109, C2:C20)?

    Function 9 is SUM; the 100-series ignores rows hidden by hand or by grouping, while 1 to 11 only ignore rows hidden by a filter.
  3. 3. When should you consolidate by category rather than by position?

    By category matches the labels, so order and missing items do not matter. By position adds cell by cell and requires identical layouts.
  4. 4. Which keyboard shortcut groups the selected rows?

    Alt+Shift+Right Arrow groups and Alt+Shift+Left Arrow ungroups. Ctrl+G is Go To and Ctrl+Shift+L toggles filters.

Summary

  • Group (Alt+Shift+Right Arrow) hides rows or columns behind +/- buttons; nesting creates outline levels 1, 2, 3.
  • Data > Subtotal needs a sorted range (not a Table) and inserts SUBTOTAL(9, ...) rows plus a grand total that does not double-count.
  • Function numbers 1 to 11 ignore filtered rows; 101 to 111 also ignore manually hidden ones.
  • Consolidate by position needs identical layouts; by category matches labels; tick Create links to source data for live results.
  • For growing data, prefer SUMIFS, 3D references, Power Query or a PivotTable.

Next: Dynamic Array Basics.