How to Collapse an Outline in Excel to Show Only Subtotals

To collapse an outline in Excel so only the subtotals show, click the 2 button in the outline area to the left of the row numbers. Level 2 hides every detail row and leaves the subtotal rows and the grand total. If you don’t see the numbered buttons yet, sort your data by the column you want to group on, then go to Data > Subtotal; Excel builds the outline for you.

This guide covers setting up subtotals, what each outline level shows, collapsing single groups with the keyboard, copying only the totals, and fixing the usual problems, such as a greyed out Subtotal button.

Step 1: Sort the data first

The Subtotal command adds a total each time the value in your grouping column changes. If the data isn’t sorted, the same group appears in several places and you get several subtotals for it.

  1. Make sure your list has a heading row and no completely blank rows or columns.
  2. Click any cell in the column you want to group by, such as Region or Customer.
  3. Go to Data > Sort & Filter and select Sort A to Z or Sort Z to A.

Step 2: Add subtotals with Data > Subtotal

  1. Click any cell in the list.
  2. Go to Data > Outline > Subtotal.
  3. In At each change in, pick the column you sorted by.
  4. In Use function, pick Sum, Count, Average or another function.
  5. In Add subtotal to, tick each column that holds the numbers you want totaled.
  6. Select OK.
Excel Subtotal dialog box
The Subtotal command builds the outline for you. This fictional list is sorted by Region, with Sum applied to Sales.

Excel inserts a subtotal row under each group, adds a grand total at the bottom and creates the outline automatically. You don’t need to group anything by hand. Microsoft’s guide to inserting subtotals in a list of data explains every option in the dialog.

The other options in the Subtotal dialog:

  • Replace current subtotals: leave it ticked to swap old subtotals for new ones. Clear it to add a second layer of subtotals (see nested subtotals below).
  • Page break between groups: starts each group on a new printed page.
  • Summary below data: puts each subtotal under its group. Clear it to put subtotals above.
  • Remove All: deletes the subtotals and the outline, leaving your original data.

Step 3: Collapse to subtotals only

The outline buttons sit in a narrow strip to the left of the row numbers. The small numbered buttons at the top choose how much you see:

ButtonWhat you see
1The grand total only
2Each subtotal plus the grand total
3 (or the highest number)Every row, including all detail

Click 2 and the sheet collapses to one row per group. Nothing is deleted; the detail rows are only hidden. Click the highest number to bring everything back.

Excel outline collapsed to level 2 showing only subtotals
Level 2 shows each subtotal and the grand total.

Collapse or expand one group

Each group has its own button in the outline strip. Click the minus button (-) next to a subtotal to hide that group’s detail, and the plus button (+) to show it again. This is handy when you want most groups collapsed but one open.

Keyboard and mouse shortcuts

  • Alt+Shift+- (minus) collapses the selected group.
  • Alt+Shift+= (equals) expands it.
  • Ctrl+8 shows or hides the outline symbols.
  • Holding Shift and scrolling the mouse wheel over grouped rows also expands or collapses them, according to Microsoft’s list of keyboard shortcuts in Excel.

Some older guides say Alt+Shift+Left Arrow and Alt+Shift+Right Arrow collapse and expand. Those keys actually ungroup and group the selected rows, which changes the outline itself. Microsoft documents the minus and equals versions for collapsing and expanding an outline, so use those.

Nested subtotals for a second level

You can total by two columns, for example by Region and then by Salesperson within each region.

  1. Sort by Region, then by Salesperson (use Data > Sort to add both levels).
  2. Run Data > Subtotal with At each change in set to Region.
  3. Run Data > Subtotal again with Salesperson, and clear Replace current subtotals.

The outline now has four levels. Level 2 shows region totals, and level 3 shows salesperson totals inside each region.

Copy only the subtotals

If you collapse to level 2 and press Ctrl+C, Excel still copies the hidden detail rows, so they reappear when you paste. To copy only what you can see:

  1. Collapse the outline to level 2.
  2. Select the visible range.
  3. Go to Home > Find & Select > Go To Special.
  4. Choose Visible cells only and select OK.
  5. Press Ctrl+C, click where you want the totals and press Ctrl+V.

Microsoft’s steps to copy visible cells only work the same way for filtered lists.

Make a chart from the collapsed totals

With the outline at level 2, select the visible subtotal rows, then go to Insert > Charts > Recommended Charts and pick a chart from the All Charts tab. The chart shows the summary rows. Microsoft notes it updates when you show or hide details, so expanding the outline later adds the detail rows to the chart.

Printing a collapsed outline

Hidden rows generally don’t print, so collapsing to level 2 before printing is a quick way to print a summary. Check File > Print to see the preview first. If you ticked Page break between groups, you may get one short page per group, so clear that option for a one-page summary. To trim the printout further, see how to print only certain columns.

Other outline tools

These live under Data > Outline and are explained in Microsoft’s guide to outlining (grouping) data in a worksheet:

  • Group > Auto Outline builds an outline from data that already has summary formulas.
  • Group > Group > Rows groups selected rows by hand.
  • Ungroup > Clear Outline removes the outline without deleting data. If some rows were hidden at the time, unhide them with Home > Format > Hide & Unhide.
  • The small dialog launcher in the Outline group opens settings such as Summary rows below detail. Clear it if your totals sit above their groups.

A PivotTable is another way to get one row per group, and it doesn’t change your original list.

Common problems

Subtotal is greyed out

The most common cause is that your data is an Excel table, and Subtotal doesn’t work inside tables. (A protected sheet, or several sheets selected at once, can also gray it out.) Click in the table, go to Table Design (or Table) > Convert to Range, select Yes, then try again. Or use a PivotTable instead.

The outline buttons are missing

Press Ctrl+8. If that doesn’t work, go to File > Options > Advanced, scroll to Display options for this worksheet, and tick Show outline symbols if an outline is applied. The setting is per worksheet, so pick the right sheet in the drop-down.

Some subtotals are missing or repeated

Repeated subtotals for the same group mean the data wasn’t sorted. Use Remove All, sort, and run Subtotal again. If subtotals seem to vanish, an active filter may be hiding them: go to Data > Clear.

Removing the subtotals

Click in the list, go to Data > Subtotal and select Remove All. Your data returns to how it was before.

Questions

Does collapsing an outline change my data?

No. Collapsing only hides rows. Formulas, totals and the hidden values stay exactly as they were.

How do I expand everything again?

Click the highest numbered outline button, such as 3. For one group, click its plus button or press Alt+Shift+=. For hidden rows that aren’t part of an outline, see how to expand all rows in Excel.

Do I need to create an outline before adding subtotals?

No. Data > Subtotal creates the outline automatically. You only build an outline by hand when you want groups without the Subtotal command. If your sheet is also cluttered with comments, you can hide all notes and red triangles in Excel for a cleaner summary view.

About the author

Matt Williams is the writer and editor behind TechInfoHome. He holds a degree in computer science and has more than 10 years of experience working in IT. More about TechInfoHome.