Collapsing rows lets you hide the detail in a long worksheet and show only the headings or totals, then expand the detail again when you need it. Excel does this with the Group feature, which adds small plus and minus buttons next to the row numbers. It is ideal for financial statements, project plans, budgets and any list with sections and subtotals. This guide shows how to group and collapse rows in a few clicks, how to create several levels, how to use Auto Outline, the keyboard shortcuts, how to expand or ungroup, the difference between grouping and hiding, and what to do when the outline buttons are missing or the group does not work as expected.
Updated November 2026
In this guide
- What collapsing rows means
- The sample data
- Method 1: group selected rows
- Method 2: keyboard shortcut
- Method 3: Auto Outline
- Method 4: the Subtotal command
- Use the level buttons
- Create several levels
- Expand, ungroup and clear
- Grouping versus hiding rows
- Copy and print only the visible rows
- Why the outline buttons are missing
- Practice problems
- Common mistakes
What collapsing rows means
When you collapse a group of rows, Excel hides them and shows a plus button in the margin, so that you can bring them back. Unlike a manual hide, the group has a visible control, the outline bar, and several groups can be nested to build levels, such as year, quarter and month. A collapsed outline makes a long sheet easy to scan, because you can view only the totals and open the details where you need them. Printing and copying respect the collapsed state, so you can print a summary version of the same sheet.
The sample data
The examples use this sheet, with the headings in row 1 and the sales rows below.
| Region | Month | Sales |
|---|---|---|
| East | Jan | 1,200 |
| East | Feb | 1,300 |
| East | Mar | 1,100 |
| East total | 3,600 | |
| West | Jan | 900 |
| West | Feb | 950 |
| West | Mar | 850 |
| West total | 2,700 |
The East rows add up to 3,600, since 1,200 plus 1,300 plus 1,100 is 3,600, and the West rows add up to 2,700.
Method 1: group selected rows
Select the detail rows that you want to collapse, but not the total row. For the East region, select rows 2 to 4, which are the three months. Go to the Data tab, click Group in the Outline group, and choose Rows if Excel asks. A bracket and a minus button appear to the left of the row numbers. Click the minus button to collapse the rows. They are hidden, the button changes to a plus, and the row for East total remains visible. Click the plus to expand them again. Repeat for the West rows, which are rows 6 to 8. The sheet can now be shown as two total lines.
Method 2: keyboard shortcut
On Windows, select the rows and press Alt, Shift and the Right Arrow key to group them. Press Alt, Shift and the Left Arrow key to ungroup. This is faster than using the ribbon when you have many groups. On a Mac, the shortcut is Command, Shift and K to group, and Command, Shift and J to ungroup. If a prompt appears asking whether to group rows or columns, choose Rows. When you select entire rows, Excel groups them at once without asking.
Method 3: Auto Outline
If your sheet has subtotal formulas below the detail, Excel can build the groups for you. Click inside the data, go to Data, Group, and choose Auto Outline. Excel looks for formulas such as SUM that refer to the rows above and creates a group for each block. In our sample, the East total is the sum of the three rows above it, and Auto Outline groups rows 2 to 4. This method needs consistent formulas, with the total row below the detail rows, which is the default setting. If the totals are above the details, change the setting under Data, Outline settings, and untick Summary rows below detail.
Method 4: the Subtotal command
The Subtotal command inserts total rows and builds an outline in one step. Sort the data by region, click inside the list, go to Data, Subtotal, choose Region in At each change in, Sum as the function and Sales as the column to total, and click OK. Excel adds a total after each group and a grand total, with outline buttons 1, 2 and 3 at the top left. Our SUBTOTAL guide explains the details. Click the 2 button to collapse everything to the totals.
Use the level buttons
When a sheet has an outline, small numbered buttons appear at the top left, above the row numbers. Click 1 to show only the top level, which might be the grand total. Click 2 to show the totals of each group. Click 3 to show every detail row. If you have more levels, the numbers increase. This is the fastest way to expand or collapse the whole sheet at once. It is a good way to present a report: start with level 1 or 2 in a meeting, and open the details when someone asks a question.
Create several levels
To nest groups, group the inner level first, and then group the outer level. For a yearly budget with months inside quarters, group the three monthly rows of each quarter, then group the quarter rows and their subtotals together to form the outer level. The outline bar shows a second line with its own button. Always group the inner rows before the outer ones, and keep the total rows outside the groups so that they stay visible when collapsed. Keep the structure simple, with no more than three levels, so that readers can follow it.
Expand, ungroup and clear
To expand one group, click its plus button. To expand every group, click the highest level number. To remove a group but keep the rows, select the grouped rows and choose Data, Ungroup, or press Alt, Shift and Left Arrow. To remove all groups on the sheet, select the whole sheet, go to Data, Ungroup, and choose Clear Outline. Clearing the outline does not delete data, but it does expand all hidden rows only if they were hidden by the group. If rows stay hidden after you clear the outline, select the rows around them, right-click and choose Unhide.
Grouping versus hiding rows
You can also hide rows by selecting them, right-clicking and choosing Hide. This works, but it leaves no visible sign that rows are hidden, other than the missing row numbers, so the reader may not notice. Grouping adds a clear plus button, so it is better for sheets that other people will use. Hidden rows are ignored by SUBTOTAL 109 but counted by SUM, so the total can differ from what is visible. Use groups for presentation, and filters when you need to show rows that meet a condition, as explained in our FILTER function guide.
Copy and print only the visible rows
When rows are collapsed, copying the range may also copy the hidden rows. To copy only what you see, select the range, press Alt and the semicolon key to select visible cells only, then copy and paste. For printing, collapsed rows are not printed, so you can print a summary page by collapsing to level 2, and a detailed page by expanding to level 3. Use the Page Layout settings to repeat the header row, and consider freezing the header while you work, as shown in our freeze panes guide.
Why the outline buttons are missing
If you do not see the plus and minus buttons, the outline symbols may be turned off. Go to File, Options, Advanced, and under Display options for this worksheet, tick Show outline symbols if an outline is applied. Also check that the sheet has a group at all, and that the zoom level is not so small that the buttons are hidden. If the Group button is greyed out, the sheet may be protected, or an Excel table may be selected, since groups do not work with tables in the usual way. Convert the table to a range, or group rows outside the table.
Practice problems
Try these with the sample data. Problem 1: group the East detail rows and collapse them, and confirm that the East total of 3,600 stays visible. Problem 2: use the level buttons to show only the two totals, which are 3,600 and 2,700. Problem 3: add a grand total of 6,300 below the West total, which is 3,600 plus 2,700. Problem 4: ungroup the West rows, and then group them again with the keyboard shortcut. Problem 5: use Auto Outline and compare the result with your manual groups. Check that SUM formulas still return the same totals after you collapse the rows.
Common mistakes
The most common mistake is to include the total row in the group, so it disappears when collapsed. Another is to group the outer level before the inner one. A third is to confuse hidden rows and grouped rows. A fourth is to forget that the Summary rows setting decides whether the button sits above or below the group. A fifth is to rely on collapsed rows to hide confidential data, since anyone can click the plus. Do not use grouping as a security measure, and remove sensitive rows before sharing a file.
Frequently asked questions
How do I collapse rows in Excel?
Select the rows, go to Data, click Group, then click the minus button.
What is the shortcut to group rows?
On Windows, Alt+Shift+Right Arrow. On Mac, Command+Shift+K.
How do I expand all rows?
Click the highest level number at the top left, or click each plus button.
How do I remove grouping?
Select the rows and choose Data, Ungroup, or use Clear Outline.
Why are the plus and minus buttons missing?
Outline symbols may be turned off in Excel Options, or the sheet has no group.


