How to Group Rows in Excel: Every Method, Shortcut, and Fix (2026)

Method 1: The Keyboard Shortcut (Fastest)

1. Click the row number of the first row to include, then Shift + click the last row. Selecting entire rows matters here, because selecting cells instead prompts a dialog box asking whether to group rows or group columns.
2. Press Alt + Shift + Right Arrow.
3. The outline bar appears to the left of the rows in Excel.

To collapse, click the minus sign or press Alt + A + H. To expand rows again, press Alt + A + J. To ungroup rows in the selection, press Shift + Alt + Left Arrow.

Grouping behaves the same way other structural spreadsheet tools do, so anyone comfortable with freezing panes or sorting a data range will recognise the pattern: define the range first, then apply the structure. Grouping is purely a display layer on the worksheet, so Excel formulas, conditional formatting, and data validation dropdown lists inside a collapsed group keep working exactly as before.

Method 2: The Data Tab (Ribbon Menu)

The ribbon route is worth learning because it exposes options the keyboard shortcut hides.

1. Select all the rows to group.
2. Open the Data tab.
3. In the Outline worksheet section on the far right, choose Group.
4. If a dialog box appears, choose Rows and click OK.

The same area of the Data tab holds Ungroup, the Subtotal command, and the small dialog launcher arrow that opens outline settings.

Method 3: Right-click and the Quick Access Toolbar

Microsoft Excel does not place a Group command on the standard worksheet right-click menu, which is a common point of confusion. Selecting cells and pressing Alt + Shift + Right Arrow does open a small dialog box offering Rows or Columns, and that dialog is the closest equivalent. Choose Rows to build row groups, or choose Columns to group entire columns.

For a genuine one-click option, pin the Group button to the Quick Access Toolbar:

1. Right-click Group on the Data tab.
2. Select Add to Quick Access Toolbar.
3. The command now sits at the top of the window, so a single press of the Alt keystroke plus a number triggers it.

Method 4: Auto Outline (let Excel build the row groups)

When a sheet already contains sum formulas beneath each block of data, Excel can build the entire outline automatically.

1. Click any cell inside the data range.
2. Go to Data > Group > select Auto Outline.

Excel reads the Excel formulas, works out which detail rows feed which summary data, and creates each grouped section with the correct nesting. On a clean, consistently structured sheet this takes one click and replaces twenty manual groupings, which makes it the fastest option for large datasets.

Auto Outline fails when the structure is inconsistent, for example when some totals sit above their detail rows, and others sit below, or when blank rows break the pattern. The error message is "Cannot create an outline."

Method 5: Subtotal (Group rows based on a column, and insert summary rows)

The Subtotal feature can group rows based on a column value and insert summary rows at the same time, which suits sorted lists such as sales by region or expenses by department.

1. Sort the data by the column to group on, for example, Region.
2. Go to Data > Subtotal.
3. Set "At each change in" to that column.
4. Choose a function such as Sum, Average, or Count.
5. Tick the columns to summarise, then click OK.

Excel inserts subtotal rows after each block, adds a total at the bottom, and outlines the whole sheet. The numbered buttons in the top left corner switch between grand total only, subtotal rows only, and full detail.

Sorting first is essential. The subtotal command creates a new group every time the value in the chosen column changes, so unsorted data produces dozens of one-row groups.

Method 6: Nested Groups (multiple levels)

Outlines can go up to eight levels deep, which is how quarterly reports show years, then quarters, then months.

1. Build the innermost of the multiple groups first, for example the individual days in a week.
2. Select a wider range that includes those groups plus their summary row.
3. Apply Group again to create the outer group.

A second outline bar appears to the left of the first. The numbered outline levels at the top of the outline column collapse or expand every group at that particular level at once, which is far quicker than clicking individual minus signs. Nested groups are the reason grouping scales to reports with more data than a screen can show.

Method 7: VBA Macro

For sheets that get rebuilt on a schedule, a macro removes the repetition. Press Alt + F11, insert a module, and paste:

Sub GroupRows()
    ' Groups rows 5 to 12 on the active sheet
    Rows("5:12").Group

    ' Collapse the first group immediately
    ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub

To group every block that sits between blank rows:

Sub GroupBetweenBlanks()
    Dim startRow As Long, r As Long
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    startRow = 2

    For r = 2 To lastRow
        If Cells(r, 1).Value = "" Then
            If r - 1 > startRow Then Rows(startRow & ":" & r - 1).Group
            startRow = r + 1
        End If
    Next r
End Sub

The macro walks from the first row of data to the last row, closing one group each time it hits a blank. Save the workbook as .XLSM afterwards, otherwise, the code is discarded. A loop over ThisWorkbook.Worksheets applies the same logic across multiple sheets.

How to Ungroup Rows

Clear Outline strips the entire outline structure while leaving all the rows and data intact.

Common Issues and Troubleshooting

The Group button is greyed out. The most frequent cause is that the range is formatted as an Excel Table. Outlining works on ranges, and an Excel table uses its own structure instead. Click inside the table, go to Table Design > Convert to Range, then group. A protected worksheet or a shared workbook also disables the button.

Nothing happens when Group is clicked. Partial rows were selected rather than entire rows. Click the row numbers in the left margin instead of dragging across cells.

The collapse buttons have vanished: Outline symbols are hidden. Go to File > Options > Advanced, scroll to the display options for this worksheet, and tick "Show outline symbols if an outline is applied." The toggle is press Ctrl + 8.

The plus and minus buttons appear in the wrong place: Excel assumes summary rows sit below their detail rows. If totals sit above instead, open the Outline dialog launcher on the Data tab and clear the Summary rows below detail checkbox. The same dialog holds the automatic styles check box, which lets Excel apply styles such as bold headers to each outline level when Create is clicked. Leaving automatic styles switched off keeps existing formatting untouched.

Collapsed groups cannot be expanded on a protected sheet - Sheet protection blocks outlining by default. Either allow it when protecting the sheet, or run this line once before protecting:

ActiveSheet.EnableOutlining = True

Groups disappear after saving - CSV files store values only. Save as .XLSX or .XLSM to keep the outline, and the same applies to formatting such as cell borders and merged cells.

Hidden rows get swallowed by a group. Grouping includes hidden rows inside the selection, and those rows remain hidden once the group is expanded. Unhide everything first with Ctrl + Shift + 9 to confirm exactly what is being grouped.

Copying a collapsed group copies the hidden rows too. To select visible cells only, highlight the range, press Alt + semicolon, then copy.

Grouping is not available in a pivot table the same way: A pivot table has its own grouping mechanism reached by right-clicking a field, and Power Query offers a Group By step for summarising before the data ever reaches the sheet. Outline grouping applies to worksheet rows and columns only.

Grouping Rows Programmatically with IronXL

Grouping by hand works well for a single report. It stops working when the same report has to be generated for fifty branches every Monday morning, or when the file is produced by an application rather than a person.

IronXL handles that case in C# without Excel installed on the machine and without Interop.

using IronXL;

WorkBook workBook = WorkBook.Load("quarterly-report.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Group rows 5 through 12 and collapse them
workSheet.GroupRows(4, 11);
workSheet.CollapseRow(4);

workBook.SaveAs("quarterly-report-grouped.xlsx");

using IronXL;

WorkBook workBook = WorkBook.Load("quarterly-report.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Group rows 5 through 12 and collapse them
workSheet.GroupRows(4, 11);
workSheet.CollapseRow(4);

workBook.SaveAs("quarterly-report-grouped.xlsx");

Imports IronXL

Dim workBook As WorkBook = WorkBook.Load("quarterly-report.xlsx")
Dim workSheet As WorkSheet = workBook.DefaultWorkSheet

' Group rows 5 through 12 and collapse them
workSheet.GroupRows(4, 11)
workSheet.CollapseRow(4)

workBook.SaveAs("quarterly-report-grouped.xlsx")

The same outline structure, the same collapse buttons, generated on a server, in a scheduled job, or inside a web application. The file opens in Microsoft Excel exactly as a manually grouped workbook would, across multiple sheets if needed.

Wrapping up

Grouping takes one keyboard shortcut, Alt + Shift + Right Arrow, and everything else is a variation on it: the Data tab for the full option set, select Auto Outline when sum formulas already describe the structure, the subtotal command when summary data is needed alongside the groups, and nested groups when the report has layers. When the group button greys out, the culprit is almost always an Excel table or a protected worksheet.

For readers whose next step is automating the workbook rather than clicking through it, this IronXL tutorial shows how to read, write, and outline spreadsheets in C#, and the same approach extends to formulas, charts, and cell styling. A free trial of IronXL is available for anyone who wants to test the grouping API against a real report.