How to Group Rows and Columns in Excel (Collapse and Expand)

Big spreadsheets get hard to navigate. Grouping lets you collapse sections, like the detail rows under each department, or individual months under each quarter, into a single line. Unlike hiding rows, groups show a clear + and – button, so nobody misses what's tucked away.

1. Select the rows you want to group by clicking and dragging down their row numbers. Select the detail rows only, not the heading or total row for that section.
2. Go to Data > Group (in the Outline group), or press Shift + Alt + Right arrow.

A bracket and a – button appear to the left of the row numbers.

• Click – to collapse the group.
• Click + to expand it again.

On a Mac, the shortcut is Command + Shift + K.

Exactly the same, but select column letters instead. The buttons appear above the column letters. It's perfect for collapsing monthly columns down to quarterly totals.

You can group within groups (up to eight levels). For example:

1. Group the rows for January, February and March.
2. Group April, May and June.
3. Then select all six months and group them again.

Now you can collapse to quarters, or collapse the whole half-year.

Once you've grouped, small numbered buttons, 1, 2, 3, appear in the top-left corner above the row numbers.

• 1 collapses everything to the highest level (often just the grand totals).
• 2 shows the next level down.
• The highest number shows everything.

If your data is sorted by a category, Excel can add totals and groups automatically:

1. Sort your data by the column you want to group by, e.g. Department.
2. Click inside the data and go to Data > Subtotal.
3. At each change in: Department. Use function: Sum. Add subtotal to: tick the columns to total.
4. Click OK.

Excel inserts a subtotal row under each department, adds a grand total, and groups everything in one go. Click level 2 to see just the department totals.

To remove it, go back to Data > Subtotal and click Remove All.

Select the grouped rows or columns and go to Data > Ungroup, or press Shift + Alt + Left arrow.

To remove every group on the sheet: Data > Ungroup > Clear Outline.

By default, the collapse button sits below the group, next to where a total row would be. If your headings are above the detail, you may prefer it at the top:

1. Click the small arrow in the corner of the Outline group on the Data tab.
2. Untick Summary rows below detail.
3. Click OK.

Group asks "Rows or Columns?". You selected cells rather than whole rows or columns. Pick one and click OK, or select row numbers first.

Group is greyed out. Your data is formatted as a Table. Tables can't be grouped. Convert to a range with Table Design > Convert to Range.

The buttons disappeared. Press Ctrl + 8 to show or hide the outline symbols.

Select the detail rows and press Shift + Alt + →. Use the 1 2 3 buttons to switch between summary and detail, and try Data > Subtotal to group and total sorted data automatically.