Different Ways to Group Rows in Excel

In Excel for Microsoft 365 on Windows, the main ways to group rows are to create a collapsible outline with Data > Group, use Data > Subtotal to build groups and summaries from sorted data, or arrange related records in a PivotTable. You can also hide rows manually, but hiding does not create a true group or a reusable summary.

What Grouping Rows Means in Excel

Grouping is a way to temporarily collapse details so a worksheet is easier to scan. In a worksheet outline, Excel adds controls beside the row numbers: a minus sign collapses a group, and a plus sign expands it again. Numbered outline buttons at the top-left of the sheet let you show different levels of detail at once. For example, a monthly report might show one row per region when collapsed and reveal individual transactions when expanded.

Grouping does not delete or rearrange the rows. The underlying values remain in the worksheet, and collapsing a group simply hides its detail rows from view. This makes grouping useful for reports that need both a concise overview and accessible supporting information. It is different from filtering, which hides records because they do not meet a condition, and from sorting, which changes their order.

Excel outlines support up to eight levels. A group can contain smaller groups, so you might create a year-level group containing quarter-level groups. The outline controls make it possible to move between the overview and the detail without maintaining separate copies of the data.

Create a Basic Collapsible Row Group

Use a manual outline when you already know which adjacent rows belong together and want to choose exactly what collapses. In Excel for Microsoft 365 on Windows, select the detail rows, then choose Data > Outline > Group. If Excel asks whether to group rows or columns, choose Rows. A bracket and collapse control appear next to the selected rows.

Make sure the rows to be grouped are next to one another. A group is a continuous range, not a collection of scattered rows.
Select the row numbers for the detail rows. If row 5 contains a department total and rows 6 through 12 contain its transactions, select rows 6 through 12 so the total remains visible when the details collapse.
Choose Data > Outline > Group, and choose Rows if Excel presents a choice.
Click the minus control to collapse the group. Click the plus control to show its rows again.

When selecting cells rather than entire row numbers, Excel may infer whether the selection represents rows or columns based on its shape. Selecting row headers is a straightforward way to avoid grouping the wrong direction. To remove a group without deleting worksheet content, select its rows and choose Data > Outline > Ungroup. The Clear Outline command removes the worksheet outline structure; it does not erase cell values.

Build Nested Groups for More Than One Level

Nested groups are helpful when the data has a hierarchy. Imagine a project-cost sheet where rows 2 through 20 contain project records, with rows 2 through 10 belonging to Project A and rows 11 through 20 belonging to Project B. You could group each project’s detail rows, then group the project sections together if you have a higher-level summary row. Collapsing the outer group hides more information than collapsing either inner group.

For predictable results, create the smallest detail groups first, then create any broader group around them. Keep headings and summary rows outside the detail selection when you want those rows to remain visible after collapsing. The outline level buttons let you display a chosen level across the sheet; for instance, a higher-level button can collapse all nested groups to a compact view. If the brackets or controls appear on the wrong side of the summary rows, use the small dialog launcher in the Outline area of the Data tab to review outline settings such as whether summary rows appear below detail.

Nested outlines are most useful when each group has a clear purpose and the summary rows are consistent. A sheet with arbitrary overlapping ranges can be hard to navigate, even if Excel permits a structure. Add labels such as Department Total or Quarter Summary so readers know what remains visible when details are hidden.

Use Auto Outline When the Sheet Has Subtotals

If a worksheet already contains summary formulas that refer to blocks of detail rows, Excel may be able to build an outline automatically. Choose Data > Outline > Group > Auto Outline. Excel examines the formulas and the relationships between summary rows and details, then creates groups where it detects a structure. Auto Outline is convenient for an established report, but it does not reliably infer the meaning of every worksheet layout. Review the resulting groups and adjust them manually if necessary.

For example, a department report might have a subtotal formula after each department’s list of employees, followed by a grand total. If those formulas consistently refer to the detail rows above them, Auto Outline may group the employee rows under each department subtotal. A plain list with no summary formulas generally gives Excel less information to work from, so manual grouping or the Subtotal command is a better choice.

To remove automatically created groups while retaining data, select the affected rows and use Ungroup, or use Clear Outline if you want to remove the entire outline. Check the sheet before clearing: an outline can contain several levels, and clearing it removes those controls even though the rows and formulas stay in place.

Group and Summarize Records with Subtotal

For a sorted list that needs a subtotal each time a category changes, use Excel’s Subtotal feature rather than grouping every block by hand. First sort the records by the field that defines the groups, such as Region or Department. Then choose Data > Outline > Subtotal. In the dialog, select the field to group by, the calculation to use (such as Sum or Count), and the column or columns to summarize. Excel inserts subtotal rows and creates an outline that can be collapsed.

Suppose a sales list has columns for Region, Rep, and Sales. Sort by Region, then set Region as the field for each change, choose Sum, and select Sales as the column to subtotal. Excel adds a regional total after each block and a grand total, while its outline controls let you show only totals or expand the sales rows. If the records are not sorted by the selected field, repeated categories may be treated as separate groups wherever they occur.

Subtotal is designed for a regular range of data, not an Excel Table. If the command is unavailable because the data is formatted as a table, convert the table to a range first using Table Design > Tools > Convert to Range, or create a PivotTable instead. Converting removes table features such as structured references and automatic table expansion, so consider whether those features matter before changing the format. To remove inserted subtotals, choose Data > Outline > Subtotal and select Remove All; this removes the subtotal rows and outline, not the original records.

Use a PivotTable for Flexible Category Groups

A PivotTable is often the better option when you want to summarize a dataset by categories and change the layout later. Select a cell in a clean data range and choose Insert > PivotTable. Place fields such as Region and Product in the Rows area, and a numeric field such as Sales in Values. Excel produces a summary in which categories can be expanded or collapsed, and you can rearrange fields without manually rebuilding row groups.

Unlike a worksheet outline, a PivotTable groups fields in a summarized report rather than hiding chosen worksheet rows in the original list. It is useful for exploring different views of the same source data, such as sales by region, then by product. A PivotTable can also group certain date or numeric fields, subject to the data and available options. If the source records change, refresh the PivotTable so its summary reflects the updated data.

Choose an outline when the row-by-row worksheet itself is the report and you want to fold away selected details. Choose a PivotTable when the central task is summarizing, comparing, or rearranging categories. They solve related problems, but they are not interchangeable: a PivotTable does not create collapsible groups around the original records.

Alternatives: Filtering and Hiding Rows

Filtering is useful when readers need to display only records that meet a condition, such as a particular region or orders above a threshold. Select the dataset and choose Data > Filter to add filter buttons, then use a column’s menu to select the values to display. Unlike an outline, a filter’s visible rows depend on criteria; it is not a fixed hierarchy of detail and summary rows. Filtering and grouping can be used in the same workbook, but users should understand which rows are hidden and why.

Manual hiding is a quick presentation fix when a few rows should temporarily disappear. Select the row headers and choose Home > Format > Hide & Unhide > Hide Rows. To show them again, select the row headers on both sides of the hidden range and choose Home > Format > Hide & Unhide > Unhide Rows. Hidden rows have no plus/minus group controls or outline levels, so they are less discoverable to another person opening the file.

Use grouping for repeatable expand-and-collapse navigation, filtering for criteria-based views, and manual hiding for a small, temporary adjustment. If users need to see totals by category, Subtotal or a PivotTable is usually clearer than hiding detail rows without adding a summary.

Choose the Right Method for Your Worksheet

Manual outline: Best when you choose the exact adjacent detail rows and want them collapsible without changing the data.
Nested outline: Best for a clear hierarchy, such as transactions within departments within regions.
Auto Outline: Worth trying when a sheet already has consistent subtotal formulas; inspect the result rather than assuming every group is correct.
Subtotal: Best for a sorted list that needs repeated calculations and an outline based on category changes.
PivotTable: Best for flexible summaries and rearranging categories without editing the source list.
Filter or hide: Best when the task is to show records matching criteria or temporarily remove a few rows from view.

Before choosing, consider who will use the file. A colleague reviewing details may appreciate outline controls and visible subtotal labels. A reader comparing categories may find a PivotTable easier. If the workbook is used as a source for other analysis, avoid rearranging or converting the source data simply to make a report look compact; create a separate summary view instead.

Common Grouping Problems and Fixes

If the Group command is disabled or the result is unexpected, first check the selection and worksheet structure. Select contiguous row headers, ensure the sheet is not protected against the change, and check whether the workbook is in a mode or format that restricts editing. Merged cells and inconsistent layouts can also make outlines harder to create or use. For Subtotal specifically, confirm the records are sorted by the chosen grouping field and that the data is not still an Excel Table.

If the outline controls do not appear, open Excel Options and check the display setting for outline symbols when an outline is applied. In Windows Excel, this setting is under File > Options > Advanced, in the display options for the worksheet. The data may still be grouped even if the controls are hidden. If rows are hidden but no outline bracket appears, they may have been hidden manually or by a filter rather than grouped.

If a collapse action hides a heading or total that should remain visible, ungroup the affected rows and recreate the selection so it contains only the detail rows. If an outline seems to have too many levels or confusing brackets, use Ungroup on the relevant range rather than deleting rows. When a worksheet contains several unrelated outlines, Clear Outline removes all outline levels on that sheet, so use it only when you intend to reset the whole structure.

Windows, Mac, and Excel for the Web

The ribbon steps above describe Excel for Microsoft 365 on Windows. Excel for Mac also provides row grouping and subtotal tools on the Data tab, though ribbon layout and keyboard shortcuts can differ by version and settings; use the menu command rather than relying on a Windows shortcut. In Excel for the web, available outline and subtotal controls can differ from the desktop app, and some workbook features may be easier to manage in desktop Excel. If a command is missing in the browser, open the workbook in the desktop app and make the structural change there.

Keyboard shortcuts are optional: on Windows, Alt+Shift+Right Arrow groups selected rows or columns, and Alt+Shift+Left Arrow ungroups them. Keyboard behavior can depend on the selection, so check that the intended row headers are selected first. Use the ribbon when working on a Mac or when shortcut conflicts make the result uncertain.

Frequently Asked Questions

How do I group rows in Excel with a plus and minus sign?
Select the detail row headers, then choose Data > Outline > Group and choose Rows if prompted. Use the minus sign to collapse the selected rows and the plus sign to expand them.

Can I group nonadjacent rows in Excel?
A single outline group covers a continuous range of rows. For separate blocks, create separate groups, or use filtering or a PivotTable if you need to collect records that are scattered through the list.

How do I ungroup rows without deleting data?
Select the grouped rows and choose Data > Outline > Ungroup. Ungrouping removes the outline structure; it does not delete the cell contents. Clear Outline removes all outline levels on the worksheet.

Why is the Group button greyed out?
Check that you selected worksheet rows, the sheet permits editing, and the range is suitable for an outline. If using Subtotal, check that the data is sorted and not formatted as an Excel Table.

What is the difference between grouping and hiding rows?
Grouping adds outline controls that make detail easy to expand and collapse. Hiding simply makes selected rows invisible and provides no group bracket or plus/minus control.

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.