Group Rows: How to Expand and Collapse Them in Excel

Yes. In Excel for Microsoft 365 on Windows, grouped rows can be expanded to show their detail or collapsed to hide it, using the outline controls beside the worksheet. Select the detail rows and choose Data > Outline > Group to create a group; then click its minus button to collapse it or its plus button to expand it.

What row grouping does

Grouping creates an outline: a reversible way to hide and reveal related worksheet rows without deleting their contents. It is useful when a worksheet contains detail that some readers need occasionally but that makes the main view harder to scan. For example, a monthly sales report might show one total row for each quarter while keeping the three monthly rows available underneath.

When Excel groups rows, it adds outline controls along the left edge of the grid. The plus and minus buttons operate a particular group, while numbered outline buttons control how much detail is shown across the worksheet. The hidden rows remain in the workbook; grouping changes their visibility, not their values or formulas. You can still edit the data after expanding the group.

Row outlines can have multiple levels, such as daily transactions inside monthly groups inside quarterly groups. Excel supports up to eight outline levels for rows or columns. A simpler outline has just one group level, so readers can show or hide all the detail in one action.

How to group rows

First, make sure the rows you want to hide are contiguous: an outline group covers a continuous block rather than several separated selections. For instance, to group the January through March detail in rows 5 to 7, select those rows and leave the quarter total outside the selection if you want that total to stay visible.

Select the row numbers for the detail rows. Selecting the row headers helps Excel recognize that you intend to group rows rather than columns.
Open the Data tab and, in the Outline group, select Group. If Excel displays a choice between Rows and Columns, choose Rows.
Check the outline controls that appear beside the row numbers. Use the minus button to collapse the detail when you want a shorter view.

On Windows, Alt+Shift+Right Arrow groups the selected rows, and Alt+Shift+Left Arrow ungroups them. On a Mac, the corresponding shortcuts are generally Option+Shift+Right Arrow and Option+Shift+Left Arrow; keyboard layouts and Excel settings can affect shortcut behavior. The ribbon route, Data > Group or Data > Ungroup, is the straightforward alternative on either platform.

Expand and collapse a group

Click the minus sign beside an expanded group to hide its detail. The sign changes to a plus; click that plus to show the rows again. Excel may place the control beside the group or near its summary row depending on the outline settings and where that summary sits.

To change several groups at once, use the numbered outline buttons displayed above the row numbers. Selecting a lower outline level hides deeper detail throughout the outline; selecting a higher level reveals more. In a one-level outline, clicking level 1 typically shows summary rows only, while level 2 displays the grouped detail as well. If the worksheet has nested groups, additional numbers represent additional levels of detail.

These controls affect what is displayed, not what is included in the workbook. A collapsed group is not the same as deleted data, and it does not remove the hidden rows from a calculation. For example, a SUM formula covering rows 5 through 7 continues to refer to those cells while the rows are collapsed.

Nested groups and summary rows

Nested grouping is helpful when readers need more than one zoom level. Imagine a yearly budget with transaction rows grouped by month and months grouped by quarter. A reader can collapse each month to see monthly totals, then collapse the quarterly detail to see only quarter summaries. Plan the outline before adding groups, because overlapping or incorrectly nested selections can make the controls harder to understand.

A useful layout keeps each summary row outside the detail group it summarizes. For example, place a quarterly total after the three monthly sections, then group only the rows containing those months. This makes it possible to hide the months while leaving the quarter total visible. If your sheet places summary rows above rather than below their details, Excel has a setting for that arrangement.

To check the outline arrangement in desktop Excel for Windows, open the Data tab, select the small dialog box launcher in the Outline group, and review the Settings options. The Summary rows below detail setting determines whether Excel expects summary rows below their detail. Match the setting to the worksheet structure before creating or adjusting an outline. Changing the arrangement setting does not itself move rows or calculate totals; it changes how Excel treats summary placement in the outline.

Grouping rows versus hiding rows

Manually hiding rows also removes them from the visible grid, but it does not create the same navigable outline structure. A user can hide rows by selecting them and choosing Home > Cells > Format > Hide & Unhide > Hide Rows. That can work for a one-off presentation, but grouped rows provide visible plus and minus controls and level buttons that make the hidden detail easier to find and reveal.

Grouping is therefore a good choice for repeat use, such as a report that alternates between summary and detail views. Manual hiding may be adequate when there is no need for a structured outline. Neither approach protects confidential information: hidden or collapsed rows can be revealed by someone with access to the workbook. Use appropriate file permissions or other data-protection measures when information must not be visible to a reader.

Row groups, filters, and PivotTables

Filters and row grouping solve different problems. A filter shows only records that match chosen criteria, such as transactions for one region. Grouping hides a planned block of rows while leaving its summary structure in place. Filtered-out records still exist, but the visible set depends on the filter choices; a collapsed outline hides detail according to the worksheet’s outline levels.

PivotTables have their own expand and collapse controls for fields and grouped items. Those controls operate within the PivotTable, rather than creating a normal worksheet row outline. If the plus or minus button appears inside a PivotTable next to a label, use the PivotTable controls or the PivotTable Analyze options instead of assuming the ordinary Data tab grouping command applies. This distinction matters because changing a PivotTable’s field arrangement is not the same as hiding a block of worksheet rows.

Common problems and fixes

If no plus or minus buttons appear, first check whether outline symbols are hidden. In Excel for Windows, open File > Options > Advanced and look in the Display options for this worksheet section for Show outline symbols if an outline is applied. Also confirm that the rows were grouped: selecting rows alone does not create an outline.

If the wrong rows disappear, click the plus sign to restore the detail and inspect the selected row range. A common mistake is including a total row in the group, which hides the total along with its detail. Ungroup the incorrect selection and regroup only the intended rows. Use Data > Ungroup to remove the selected outline level; use Data > Ungroup > Clear Outline when you intend to remove the worksheet’s outline structure altogether. Clearing an outline does not delete the underlying rows or their contents.

If grouping is unavailable or behaves unexpectedly, check for worksheet protection, merged cells within the selection, or an existing outline that does not match the new selection. Protected sheets may restrict structural changes, and merged cells can complicate row operations. Save a copy before making broad outline changes in an important workbook, especially if its nested structure is difficult to rebuild.

Good practices for readable outlines

Make the visible summary useful on its own. A collapsed view should tell readers what the hidden detail represents, using clear labels and totals rather than a series of unexplained numbers. Keep groups consistent in size where possible, and avoid grouping unrelated sections simply because they are adjacent.

Keep headings and summary rows outside the detail groups they describe.
Use consistent labels, such as month names or department names, so collapsed sections remain understandable.
Test the outline at each level before sharing the workbook, including the summary-only view.
Remember that filters, formulas, charts, and print settings may interact with hidden rows differently; check the workbook’s intended output after changing visibility.

For a printed report, test Print Preview after collapsing groups. The worksheet view and printed pages are related but not identical: page breaks, print areas, and settings for printing hidden rows can affect the result. If readers need a fixed summary report as well as an interactive detail sheet, consider keeping a separate presentation sheet rather than relying on one outline state for every use.

Frequently Asked Questions

Can I expand or collapse all grouped rows at once?
Yes. Use the numbered outline level buttons beside the row numbers. Selecting a summary level hides lower-level detail across the outline; selecting a more detailed level reveals it. Individual plus and minus buttons let you control groups separately.

Do collapsed rows still count in formulas?
Generally, yes: collapsing an outline group changes visibility, not the cells or the references in formulas such as SUM. Some functions, including SUBTOTAL, can treat filtered or manually hidden rows differently depending on the function number, so check the function’s behavior if the result must exclude hidden data.

How do I remove grouping without deleting data?
Select the grouped rows and choose Data > Ungroup. To remove all outline levels on the sheet, choose Data > Ungroup > Clear Outline. Both actions remove the outline structure; neither is a command to delete the row contents.

Why can I see hidden rows but not the outline buttons?
The worksheet may have outline symbols turned off, or the rows may have been hidden manually rather than grouped. On Windows, check File > Options > Advanced and the Show outline symbols if an outline is applied setting for that worksheet. If the rows were manually hidden, unhide them and create a group with Data > Group.

Can I group nonadjacent rows together?
Not as one ordinary outline group. A group represents a continuous block of rows. Create separate groups for separate blocks, or consider filtering the data if the goal is to show records that match a condition rather than hide a fixed range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.