Excel can automatically group rows when it finds summary formulas linked to detail rows: select a cell in the data, then choose Data > Outline > Group > Auto Outline. It will not reliably guess groups from labels or blank rows alone; for those, select the rows and choose Data > Outline > Group > Group.
What automatic row grouping does
Grouping adds an outline to a worksheet. The outline gives you controls to collapse detail rows and show a smaller set of summary rows, then expand the groups when you need to inspect the underlying data. It does not combine cells, remove rows, or change the values in the worksheet. It changes how rows are organized and displayed.
Excel’s Auto Outline command looks for a recognizable relationship between detail and summary calculations. A common example is a block of monthly sales records followed by a row containing a SUM formula for that block. Excel may use the formula’s references to identify the detail rows that belong together. A heading such as West Region, a blank line, or a change in formatting is not, by itself, a dependable instruction to create a group.
That distinction matters when people ask whether Excel can group rows automatically whenever a worksheet is opened or updated. Auto Outline is a command you run; it is not a standing rule that continuously reorganizes the sheet. If the source data changes, review the outline and rerun or adjust it as needed.
How Auto Outline identifies groups
Auto Outline works best on a report that has detail records and formula-based summaries arranged in a consistent hierarchy. For instance, a worksheet might list individual orders, include a subtotal for each region, and finish with a grand total. Summary formulas that refer to the rows they summarize give Excel clues about where detail groups begin and end.
It is less useful on a plain list with no subtotal formulas. Suppose column A contains department names and column B contains employee names, with each department’s employees listed together. Excel does not necessarily interpret those repeated labels as instructions to create one collapsible group per department. You can group those rows manually, or add suitable summary calculations and use Auto Outline if that fits the report.
The placement of summary rows can also affect the result. Excel’s outline settings include an option called Summary rows below detail. If your report puts subtotals above their detail instead, review that setting from the Data tab’s Outline group using the dialog launcher and Settings before creating the outline. Check the resulting groups rather than assuming the automatic arrangement is correct.
Run Auto Outline in Excel for Microsoft 365
- Save the workbook, then click a cell within the report or data range that contains the detail and summary formulas.
- On the ribbon, select Data. In the Outline group, open the Group menu and choose Auto Outline.
- Wait for Excel to add outline controls along the left edge of the worksheet. If no useful groups appear, inspect the summary formulas and outline settings, or use manual grouping.
- Click a minus sign to collapse a group, or a plus sign to expand it. The numbered outline buttons above the row headings show different levels of detail; selecting a lower level displays fewer levels of detail.
Auto Outline is intended for worksheet outlines built from summary relationships. It may not produce the grouping you expect if the report’s formulas are inconsistent, the summaries are not recognizable, or the data is arranged in a different pattern. Expand the groups and compare them with the report before relying on a collapsed view for review or presentation.
Group rows manually when labels define the sections
Manual grouping is usually the clearest approach when you want to choose exact row boundaries. Click and drag across the row numbers at the left of the worksheet to select the rows for a group. For nonadjacent or nested structures, work with one contiguous section at a time so the outline remains easy to understand. Then choose Data > Outline > Group > Group. If Excel asks whether to group rows or columns, choose Rows.
On Windows, the shortcut for grouping selected rows is Alt+Shift+Right Arrow. To ungroup the selected rows, use Alt+Shift+Left Arrow. In Excel for Mac, the corresponding shortcuts are generally Option+Shift+Right Arrow and Option+Shift+Left Arrow; keyboard settings and Excel versions can affect shortcuts, so use the Data ribbon commands if a shortcut does not work.
For example, if rows 5 through 12 contain the transactions for January and row 13 contains the January total, select rows 5 through 12 and group them. Repeat for the other months. Keep the total rows outside their detail groups if you want those totals to remain visible when the detail is collapsed. The row-number selection is important: selecting cells within a range can produce a different result or prompt Excel to ask what to group.
Use outline levels and nested groups
Outline controls let a reader choose how much detail to display. A minus sign beside a group collapses its rows; the plus sign that replaces it expands them again. Numbered buttons at the top-left of an outlined sheet show outline levels. For example, level 1 might show only an overall total, while a higher level reveals subtotals and then individual records. The exact result depends on how the groups and summary rows are arranged.
You can create nested groups for reports with more than one layer, such as individual transactions inside monthly groups and monthly groups inside quarterly groups. Group the smaller sections first, then group the larger sections that contain them. Excel supports up to eight outline levels for rows or columns, but a report is often easier to use when it has fewer, clearly named layers.
Use the plus and minus controls when you want to open one particular section. Use the level buttons when you want to show or hide a whole layer across the worksheet. Collapsing a group hides its rows from view, but it does not delete them. The outline and the data remain in the workbook until you change or remove them.
When to use grouping instead of filtering
Grouping and filtering solve different problems. Grouping is useful when the worksheet already has a report-like hierarchy and you want to fold away detail beneath visible totals. Filtering is better when readers need to find records that meet changing conditions, such as showing only orders from one customer or dates within a selected period. A filter hides rows that do not meet criteria; it does not create outline levels or summary sections.
For data that needs frequent sorting, filtering, or expansion as new records are added, an Excel Table is often a better foundation than a manually outlined report. Tables provide filter controls and can expand to include adjacent data, but they are not a substitute for a row outline. If outline commands are unavailable or behave unexpectedly, check whether the sheet is protected and whether the selected data is inside a table. Do not convert a table to a range just to force grouping unless you are comfortable giving up table features such as its structured behavior and built-in filter controls.
Common problems and how to fix them
- Auto Outline creates no groups: Check for summary formulas that calculate totals from the detail rows. A list of labels and blank spacer rows alone may not provide Excel with enough information. Try selecting a cell inside the report before running Auto Outline.
- The wrong rows collapse: Expand the outline, inspect which summary formulas refer to which detail rows, and check whether summary rows are above or below the detail. Correct the settings or remove the outline and create the intended groups manually.
- The Group command is unavailable: Check for worksheet protection and review what is selected. A selection involving a table or other worksheet structure can limit the available outline operations. If protection is intentional, do not remove it without permission.
- New rows are not included: A row outline is not a live grouping rule. After adding records or changing the report layout, inspect the group boundaries and update them. Rerunning Auto Outline may rebuild the outline, so check the result afterward.
- Rows seem to have disappeared: They may be collapsed, filtered, or hidden. Look for plus signs in the outline margin, active filter indicators in the headers, or gaps in the row numbers. Expanding groups or clearing the relevant filter can bring the rows back into view.
If you want to remove only one group, select its rows and choose Data > Outline > Ungroup. To remove the outline structure from the worksheet, use Data > Outline > Ungroup > Clear Outline. Clearing an outline removes the grouping controls; it does not erase the worksheet’s cell contents. Take care not to confuse Clear Outline with deleting rows or clearing cell data.
Practical example: monthly sales detail
Imagine a report with transaction rows grouped by month, a SUM subtotal after each month, and a grand total at the bottom. If the subtotal formulas consistently refer to the transaction rows above them, try Auto Outline and verify that each month collapses to its subtotal. If it groups several months together or misses a section, undo or clear the outline and group each month’s transaction rows manually.
Before sharing the workbook, decide what recipients should see when it opens. You might leave the detail expanded for auditing, or collapse it to show monthly and grand totals. Add clear labels to the subtotal rows so the visible summary makes sense without the hidden records. If the report will be updated regularly, document which rows belong to each section or use a repeatable reporting process rather than assuming that the outline will adapt itself.
Frequently Asked Questions
Can Excel group rows automatically based on blank rows?
Not reliably with Auto Outline. Blank rows can make sections easier to read, but the command primarily looks for summary formulas and their relationships to detail. Group blank-row-separated sections manually when those boundaries are the intended groups.
Will Excel automatically group new rows I add later?
Not as a continuing rule. Existing outline boundaries may not include new rows in the way you expect. Review the groups after changing the data, and update them manually or run Auto Outline again if the report has suitable summary formulas.
How do I show all grouped rows again?
Click the plus signs beside collapsed groups, or select a higher outline-level button to display more detail across the sheet. If the outline controls are missing, the rows may instead be filtered or hidden manually.
Does grouping rows delete or change the data?
No. Grouping creates controls that hide or reveal selected rows. The values and formulas remain in the worksheet. Deleting rows or clearing cell contents are separate actions.
Can I group columns as well as rows?
Yes. Select the column letters and use Data > Outline > Group > Group. Column outlines work similarly, with controls above the column headings. Auto Outline can also create column outlines when it recognizes a suitable summary structure.
Related
- Conditional Formatting Rows in Excel: A Practical Guide
- Conditional Formatting in Excel: Yes, and Here’s Why
- Excel Conditional Formatting: How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Create Rules in Outlook 365 to Organize EmailTo create a rule in Outlook 365, open Settings > Mail > Rules in new Outlook or Outlook…
- How to Make One Page Landscape in Word 365: Step by StepTo make one page landscape in Word 365 while keeping the rest of the document portrait, isolate that…
- Google Sheets 2D SUMIF: Sum Values by Row and ColumnGoogle Sheets has no built-in 2D SUMIF function, but you can sum a table by matching a row…
- How to Set Out of Office Replies in Outlook 2011 for MacIn Outlook 2011 for Mac, open Tools > Out of Office, turn on out-of-office replies, write your message,…
- Python Pandas Excel to CSV: Steps, Options, and FixesTo convert an Excel workbook to CSV with pandas, read a worksheet into a DataFrame with pd.read_excel(), then…
- Remove Duplicates in Excel: Keyboard ShortcutIn Excel for Microsoft 365 on Windows, select a cell in your data and press Alt, then A,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.