Excel can group adjacent rows into collapsible sections with Data > Outline > Group; to combine records with the same category or calculate totals by category, use a PivotTable or the Subtotal command instead. These are different tasks: outlining hides and reveals rows, while summarizing organizes records by shared values and calculates results.
What grouping rows means in Excel
People use “group by row data” to mean two things. They might want to fold a block of rows out of view while keeping it in the worksheet, or they might want Excel to organize records such as sales by region and calculate a total for each region. Excel has separate features for these jobs, so choosing the right one matters.
Manual row grouping creates an outline. The rows remain in place, and controls beside the worksheet let you collapse or expand a section. It is useful for hiding supporting detail beneath a summary, such as monthly transactions beneath a quarterly total. It does not inspect cell values or combine rows that share a label.
Grouping records by a field is a data-analysis task. A PivotTable can collect all records with the same region, product, or date period and summarize their values. The Subtotal command can insert a subtotal row whenever a sorted column’s value changes. Power Query can group records as a repeatable data-preparation step. The right choice depends on whether you want a visual outline, a report, or transformed data.
Group adjacent rows into a collapsible outline
Use an outline when the rows you want to hide are already together and you want to retain them in the worksheet. For example, a budget might have a row for each expense and a separate total row. Grouping the expense rows lets a reader show the total alone or expand the underlying detail.
- Select the row numbers for the consecutive rows to group. To group rows 5 through 10, select row numbers 5 to 10 on the left side of the worksheet.
- On the Windows ribbon, choose Data > Outline > Group. If you selected cells rather than whole row headers, Excel may ask whether to group Rows or Columns; choose Rows.
- Use the minus control beside the outline to collapse the group. The control changes to a plus sign; select it to show the rows again.
On Windows, Alt+Shift+Right Arrow groups the selected rows, and Alt+Shift+Left Arrow ungroups them. In Excel for Mac, the corresponding shortcuts are Option+Shift+Right Arrow and Option+Shift+Left Arrow; the Data tab’s Group and Ungroup commands are alternatives. Excel for the web has outline controls in supported workbooks, but ribbon availability can vary, so use the Data tab if the shortcut is unavailable.
Rows in one outline group need to be adjacent. If the same category appears in separate parts of a list, a manual outline does not bring those records together. Sort the records or use a PivotTable if the goal is to group nonadjacent matching values.
Use Subtotal for totals between sorted groups
Subtotal is suited to a plain worksheet list when you want Excel to add a subtotal row after each change in a field. For example, sort sales records by Region, then insert a sum of Sales for each region. Excel adds subtotal rows and an outline, so you can collapse the detail and view only the regional totals.
- Make sure the list has a header row and consistent columns. Sort the records by the field that defines each group, such as Region.
- Select a cell in the list and choose Data > Outline > Subtotal.
- In the dialog, set At each change in to the grouping field, Use function to Sum or another available calculation, and Add subtotal to to the numeric column or columns.
- Select OK, then use the outline level buttons or plus and minus controls to show the detail or subtotals.
The list must be sorted by the field used in At each change in; otherwise, Excel creates a subtotal each time the value changes, not one overall result for every occurrence of a value. If a region appears in several separated blocks, sort first. The Subtotal command is intended for a normal range, not an Excel Table. If it is unavailable for a table, convert the table to a range or use a PivotTable instead. Subtotals alter the worksheet by inserting rows, so keep a copy if you need to preserve the original layout.
Use a PivotTable to group and summarize records
A PivotTable is usually the clearest option when the goal is to group records by one or more fields and calculate summaries without rearranging the source list. It works well for sales, inventory, survey responses, and other tabular data. Unlike manual outlining, it can gather matching categories even when their records are scattered throughout the source.
- Click a cell in the data, then choose Insert > PivotTable. Confirm the table or range and choose where to place the PivotTable.
- In the PivotTable Fields pane, drag the category field, such as Region, to Rows.
- Drag a numeric field, such as Sales, to Values. Excel will summarize it, commonly with Sum when the source values are numeric.
- To add another level, drag another field, such as Product, into Rows below Region. Drag a field into Columns or Filters if that layout better suits the report.
Check the result rather than assuming the calculation is correct. If the Values area shows Count of Sales instead of Sum of Sales, some source entries may be stored as text or blank. Select a value in the PivotTable, open Value Field Settings, and choose the appropriate summary; also check the source data type if Sum is not available. When the source changes, refresh the PivotTable. If you make the source an Excel Table before creating the PivotTable, new rows are easier to include in its source after a refresh.
For date fields, a PivotTable can often group valid dates into periods such as months, quarters, or years. If grouping fails, check whether the source column contains text, blanks, or invalid dates. Grouping dates in a PivotTable changes how the report displays dates; it does not rewrite the original source cells.
Group rows in Power Query
Power Query is appropriate when grouping is part of a data-cleaning or refresh process. It creates a query result rather than adding outline controls to the original worksheet. This helps when you regularly import a file and want the same aggregation steps applied again after the source is updated.
To start, format the source as an Excel Table or select a cell in the data, then choose Data > From Table/Range. In the Power Query Editor, choose Transform > Group By. Select the grouping column and an operation such as Sum, Count Rows, or Average, along with the column to aggregate when the chosen operation needs one. Select OK, then use Home > Close & Load to return the query result to Excel.
Power Query’s grouped output is a new result table, not a set of collapsible source rows. Refresh the query to apply its steps to updated source data. If you need to inspect every original record in the worksheet, keep the source table as well as the grouped result. Power Query is more than necessary for a one-time small summary, but it can save repeated manual work for recurring imports.
Choose the right method
Decide what the reader should see and whether the source records need to change. The main distinctions are:
- Outline grouping: Hides and reveals a selected block of adjacent worksheet rows. It does not calculate totals or find matching labels.
- Subtotal: Inserts subtotal rows into a sorted list and creates an outline. It is convenient for a quick report in a normal range.
- PivotTable: Builds a flexible summary from records, with categories and calculations arranged in a separate report. It is often the best starting point for analysis.
- Power Query: Produces a grouped result as part of a repeatable import and transformation workflow.
For instance, suppose a worksheet has hundreds of order lines with Region, Product, and Sales columns. To fold an already arranged block of rows away, use an outline. To see total sales by region without editing the list, use a PivotTable. To insert subtotal rows into the list, sort by Region and use Subtotal. To repeat the same grouping whenever a monthly file arrives, consider Power Query.
Common problems and practical fixes
If the Group command is unavailable or does not group the intended rows, check what is selected. Selecting row numbers makes the target clear; selecting cells can bring up a Rows or Columns choice. Also check whether the worksheet is protected, since protection can restrict structural changes. Try the Data tab command if a keyboard shortcut is intercepted by another setting or keyboard layout.
If the plus and minus controls are missing, the outline may be collapsed or outline symbols may be hidden. In Excel for Windows, check File > Options > Advanced and the display options for the worksheet to confirm that outline symbols are shown. The exact screen can differ on Mac. You can also select the relevant rows and use Data > Ungroup to remove an outline, then create it again if needed.
If a subtotal appears more than once for the same label, the records are probably not sorted consistently by the grouping field. Sort the full list, not just one column, so each record stays intact. If a PivotTable displays an unexpected Count, inspect the source values for numbers stored as text. If a PivotTable omits newly added records, confirm its data source includes them and refresh it; using an Excel Table as the source can make future additions easier to manage.
Be careful when mixing manual outlines with inserted subtotal rows. The Subtotal command itself creates outline levels, and applying it repeatedly can add extra subtotal rows. If a worksheet has become confusing, use Data > Ungroup on the selected rows or choose Clear Outline from the Data tab’s Outline controls, then rebuild the intended structure. Clearing an outline removes the grouping controls; it does not delete the underlying detail rows.
Frequently Asked Questions
Can Excel group rows automatically by matching text?
Manual Group does not search for matching text; it only groups selected adjacent rows. Sort the list and use Subtotal, or create a PivotTable with the text field in Rows, to collect matching values into groups.
Can I group nonadjacent rows?
Not as one ordinary outline group. An outline group covers a contiguous block. To report on a category whose rows are scattered, use a PivotTable; to create subtotal sections, sort the source by the category first.
Does grouping rows delete or move data?
No. Collapsing an outline hides rows from view but leaves their contents in the worksheet. Subtotal inserts additional summary rows, while a PivotTable or Power Query creates a separate summary output.
How do I ungroup rows in Excel?
Select the grouped rows and choose Data > Outline > Ungroup, or use Alt+Shift+Left Arrow on Windows. On Mac, use Data > Ungroup or Option+Shift+Left Arrow. Removing the outline does not erase the row data.
Why can’t I see the plus and minus buttons?
Check whether outline symbols are enabled for the worksheet and whether the group is already collapsed. If needed, select the relevant rows and use Data > Group to create the outline again. Worksheet protection can also limit changes to grouping.