How to Group and Collapse Rows and Columns in Excel

Aseem Kishore
·
6 min read

Help Desk Geek is reader-supported. We may earn a commission when you buy through links on our site. Learn more.

Woman at a wooden desk views a color-coded spreadsheet on a laptop, with a mug and notebook nearby in a cozy home office.

If detail rows are cluttering the summary totals you need to see, Excel can hide them without deleting any data. In a modern desktop version of Excel, select the detail rows and use Data > Group to add a one-click collapse control. Excel for the web also supports basic grouping, as explained below.

Related: Learn to freeze rows and columns in Excel for easier data reference.

How to Group and Collapse Rows (Windows)

The idea is straightforward: you select the detail rows you want to hide, leave the summary row outside the selection, and tell Excel to group them. Here’s what a typical worksheet looks like before grouping. There are three blocks of data, each with a summary cell below it (B6, B13, B20).

An Excel worksheet in Microsoft 365 showing three sets of data rows with summary/total cells at B6, B13, and B20

Step 1: Select the detail rows

  1. Click the row number of the first detail row you want to group, then drag down to the last detail row. Do not include the summary row in your selection. If you do, Excel will either hide it or produce an odd bracket.
  1. For example, to group rows 8–12 (leaving the Average in row 13 visible), drag over row numbers 8 through 12.
Excel worksheet with row numbers 8 through 12 selected (highlighted in blue), summary row 13 not selected

Step 2: Apply the group

  1. Go to the Data tab on the Ribbon.
  1. In the Outline section, click Group.
  1. If Excel asks “Rows or Columns?”, select Rows and click OK.
Excel Data tab with the Outline group visible, Group button highlighted, and the Rows/Columns dialog open with Rows selected

Excel immediately adds a bracket and a minus (–) button in the left margin next to the grouped rows. That minus button is your collapse control.

Excel worksheet showing rows 8–12 with an outline bracket and minus button in the left margin, summary row 13 visible below

Step 3: Collapse and expand the group

  1. Click the – button in the left margin to collapse the group. Rows 8–12 disappear, the row numbers jump from 7 to 13, and the button changes to a +.
  1. Click the + button to expand the group again.
  1. If you have multiple groups, use the numbered level buttons (1, 2, 3…) that appear at the top-left of the row gutter to collapse or expand all groups at once.
Excel worksheet with rows 8–12 collapsed, row numbers jumping from 7 to 13, and a plus button visible in the left margin

Repeat the same steps for rows 1–5 and rows 15–19, and you end up with three collapsed groups, with only the summary rows visible and detail tucked away until you need it.

Excel worksheet with three collapsed row groups, three plus buttons in the left margin, and only summary rows B6, B13, and B20 visible

To create nested groups, group the innermost detail rows first, then select a larger range around that group and apply Data > Group again. The numbered level buttons let you show only top-level summaries or expand progressively deeper detail.

Keyboard shortcuts (Windows)

  • Group selected rows/columns: Alt + Shift + Right Arrow
  • Ungroup selected rows/columns: Alt + Shift + Left Arrow
  • Expand all groups at current level: Alt + A, J
  • Collapse all groups at current level: Alt + A, H

How to Group and Collapse Columns (Windows)

Grouping columns works identically. The bracket and +/– controls just move to the top of the sheet instead of the left side.

  1. Click the first column letter you want to group, then drag to the last column letter in the group. Leave any summary column outside the selection.
  1. Go to Data → Group.
  1. If prompted, choose Columns and click OK.
  1. Use the – button that appears above the column headers to collapse, and + to expand.
  1. To remove the grouping: select the grouped columns, go to Data → Ungroup, choose Columns, and click OK.
Excel worksheet with a group of columns collapsed, showing a plus button above the column headers and column letters skipping the hidden range

How to Group Rows and Columns on Mac

The steps are the same on Excel for Mac: use Data → Group and choose Rows or Columns. Keyboard mappings can vary by Excel and macOS version, so use the Ribbon controls unless you have confirmed the shortcuts shown in your build.

Excel for Mac Data tab showing the Outline group with Group and Ungroup buttons visible

Excel for the Web

Excel for the web supports basic row and column grouping under Data → Group / Ungroup. The +/– outline controls work the same way. Advanced outline options (nested groups, Clear Outline) may be limited depending on your Microsoft 365 tenant version.

How to Ungroup and Remove All Groups

  1. Expand all groups first. If you ungroup while rows are collapsed, Excel may leave those rows hidden and you’ll need to unhide them manually.
  1. Select the rows or columns you want to ungroup.
  1. Go to Data → Ungroup. Choose Rows or Columns and click OK.
  1. To remove every group on the sheet at once, go to Data → Outline → Clear Outline.

Troubleshooting: Grouping Not Working

The +/– outline symbols don’t appear

The grouping is there. Excel is just hiding the outline controls. Fix it in Options:

  1. Go to File → Options.
  1. Click Advanced.
  1. Scroll to Display options for this worksheet.
  1. Check Show outline symbols if an outline is applied.
  1. Click OK.
Excel Options dialog open on the Advanced tab, scrolled to "Display options for this worksheet" section with "Show outline symbols if an outline is applied" checkbox highlighted

This setting is per worksheet, so if you have multiple sheets with groups, you’ll need to enable it on each one.

The Group button is greyed out

A greyed-out Group button often means the sheet is protected. A legacy shared workbook can also block grouping, although that older sharing mode is deprecated and its command may be hidden in current Excel builds.

  1. Go to Review → Unprotect Sheet. Enter the password if prompted.
  1. If you know the file uses a legacy shared workbook, unshare it first: Review → Share Workbook → uncheck Allow changes by more than one user. If that command is not visible, skip this step; modern co-authoring does not use the same legacy dialog.
  1. If neither applies, try disabling add-ins: File → Options → Add-ins → Manage: COM Add-ins → Go → uncheck all → restart Excel.

Rows stay hidden after ungrouping

If you removed a group while it was collapsed, those rows are now just hidden, not grouped. Select the rows on either side of the gap, right-click, and choose Unhide.

Alternatives Worth Knowing

Manual grouping is great for presentation-style sheets. For heavy-duty data work, these options are worth a look:

  • Data → Subtotal: Automatically inserts subtotal rows and creates outline groups, which is useful when you want Excel to handle the grouping structure for you.
  • PivotTables: Built-in expand/collapse for categories, dates, and regions. Better than manual groups for interactive analysis.
  • Power Query: Shapes and summarizes data before it lands on the sheet, often eliminating the need for grouping in the final output.

For most people, grouping rows with Data > Group and clicking the – to collapse is all they need. If your +/– buttons have gone missing, the “Show outline symbols” setting in Advanced Options fixes it almost every time. For anything beyond a few dozen grouped rows, consider a PivotTable instead, as it handles the expand/collapse logic automatically and scales much better with large datasets.