To group rows in Excel by a column value, sort the records by that column and use Data > Outline > Subtotal to insert collapsible groups at each change in value. If you only need a grouped summary, use a PivotTable; if you need a simple visual outline, you can also group selected rows manually.
What “group rows by value” can mean
Excel uses the word group for more than one task. In the worksheet outline feature, grouping means placing adjacent rows inside a collapsible section controlled by plus and minus buttons. That feature does not automatically search a column and create a separate outline for every repeated value. The rows generally need to be contiguous, and you create the outline yourself or use Subtotal.
People also use “group” to mean sorting records into category blocks, summarizing numbers by category, or filtering the sheet to show one category at a time. Those are different outcomes. For example, a sales list with Region, Salesperson, and Amount columns could be sorted so all West records sit together; outlined so the West rows can be collapsed; or summarized in a PivotTable to show total sales by region. Decide which result you want before changing the sheet.
- Keep the records and make collapsible sections: sort, then use Subtotal.
- Show or hide a selected block of rows: select the rows and use Group.
- Compare totals or counts by category: build a PivotTable.
- See only one category temporarily: apply a filter.
Sort records so matching values are together
Sorting is the necessary first step when you want an outline or subtotals by a column. Excel can only create a meaningful break at each change in value when identical values are next to one another. If the same category appears in several separate places, Subtotal will treat each run as a separate group.
- Make sure the data has one header row and no completely blank rows or columns inside the list.
- Click a cell in the data, then choose Data > Sort.
- In the Sort dialog box, select the column to group by under Sort by, and choose the desired order.
- If the data has headers, confirm that My data has headers is selected. Add another sort level only if you want an order within each category, such as sorting each region by salesperson.
- Choose OK.
For example, sorting a list by Department makes all the Marketing rows adjacent, followed by all the Operations rows, and so on. Sort the entire table, not just the Department column: sorting one column alone can detach values from their related names, dates, and amounts. If Excel asks whether to expand the selection, choose to expand it so each record remains intact.
Make collapsible groups with Subtotal
For a classic worksheet outline with subtotals, first sort the data by the field that defines each group. Then click inside the sorted range and choose Data > Outline > Subtotal. In the dialog box, set At each change in to the grouping column, choose a calculation under Use function, and select the numeric column or columns under Add subtotal to. Click OK.
Suppose a list has Department, Employee, and Hours columns. Sort by Department, set At each change in to Department, choose Sum, and add the subtotal to Hours. Excel inserts a subtotal row after each department and creates an outline. The outline level buttons, usually numbered 1, 2, and 3 near the upper-left of the worksheet, let you show only the grand total, the department subtotals, or the detail rows. The minus and plus controls beside the row headings collapse or expand individual sections.
Subtotal can calculate sums, counts, averages, and other supported summary operations. It adds worksheet rows and uses subtotal formulas in the inserted summary rows, so the displayed result updates when underlying values change. It is suited to a conventional range of records, not every workbook layout. In particular, Excel disables the Subtotal command when the data is formatted as an Excel table. If the command is unavailable, convert the table to a range using Table Design > Convert to Range, or use a PivotTable instead. Converting removes table behavior, so consider whether structured references and automatic table expansion matter to your workbook.
Group selected rows manually
Manual grouping is useful when you already have a block of rows you want to hide and show together, but do not need Excel to calculate a subtotal. Select the row numbers for the section, then choose Data > Outline > Group and select Rows if Excel asks what to group. A bracket and a minus control appear beside the selected rows. Click minus to collapse the section and plus to show it again.
This method does not look at a column’s contents or maintain category membership automatically. If rows are reordered or new records are added, you may need to adjust the selection or remove and recreate the outline. You can remove a grouping by selecting its rows and choosing Data > Outline > Ungroup. To remove all outline levels from the sheet, use the menu’s clear-outline command, taking care not to confuse it with clearing cell contents.
Manual grouping works best for a report whose sections are already arranged and relatively stable, such as monthly detail blocks beneath headings. It is less suitable for a growing transaction list where categories may move or new records arrive regularly.
Summarize categories with a PivotTable
If the goal is to compare totals, counts, or averages by category, a PivotTable is usually more flexible than worksheet row grouping. Click inside the data and 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 Department, to Rows, and drag a numeric field, such as Hours, to Values.
Excel typically summarizes numeric fields by Sum and text fields by Count. If the result is not the intended calculation, open the value field’s settings and select a suitable summary. A PivotTable can display one row per category without inserting or rearranging the source records. You can add fields to Columns or Filters, expand or collapse category levels, and reorganize the report by dragging fields. This makes it useful when readers need a concise summary rather than the underlying rows grouped in place.
A PivotTable is separate from the source data. When the source changes, refresh the PivotTable using Data > Refresh All or the PivotTable refresh command. If new records are added outside the original source range, the PivotTable may not include them unless its source is an expanding Excel table or you update the source range. PivotTables do not normally create worksheet outline groups around the original records; they present a summarized view of the data.
Choose the method that fits the task
Use Subtotal when you want the original records, inserted summary rows, and a collapsible outline based on a sorted field. Use manual Group when the rows to hide are already known and you do not need automatic category breaks or calculations. Use a PivotTable when the main purpose is to summarize, compare, count, or rearrange category results. Use sorting alone when you only want matching values next to one another, and use filters when you want to display selected values without creating outline levels.
These methods are not mutually exclusive. You can sort records for readability and still create a PivotTable summary from them. A filter can also be useful alongside an outline, although filtering hides records according to criteria rather than creating a group that can be expanded with an outline control. For a frequently updated report, an Excel table plus a PivotTable is often easier to maintain than repeatedly inserting subtotal rows into the source list.
Common problems and fixes
- Subtotal is dimmed: The source may be an Excel table. Convert it to a range or create a PivotTable. Also check that the selection is inside a normal data range.
- A category appears in several groups: Sort the full data range by the grouping field before inserting subtotals. Check for spelling differences and extra spaces that make values appear similar but not identical.
- Rows no longer match their other columns: Undo the sort and sort the entire range, including all related columns. Avoid sorting a single field independently.
- The outline controls are missing: The outline may be collapsed, cleared, or hidden by worksheet settings. Check File > Options > Advanced for the setting to show outline symbols if an outline is applied, and look for the level buttons near the row headings.
- Totals are wrong or missing: Confirm that the intended numeric field is selected in the Subtotal dialog and that values are stored as numbers rather than text. Also verify that the sort field matches the field selected under At each change in.
Before using Subtotal on a working sheet, consider copying the data to a new sheet or saving a backup. The command inserts rows and changes the layout. To remove the inserted subtotal rows later, use Data > Outline > Subtotal and choose Remove All in the dialog; this removes the subtotal structure, not the original records.
Excel for Mac and Excel for the web
The instructions above use Excel for Microsoft 365 on Windows. Excel for Mac also has sorting, PivotTables, and row outlining, but the ribbon arrangement and some dialog labels may differ by release; look on the Data tab for sorting and outline commands. On Mac, the Windows Alt ribbon shortcuts do not apply; use the ribbon or the Mac menu and keyboard shortcuts shown by that version of Excel.
Excel for the web supports sorting and PivotTables, but available outline operations and controls can differ from the desktop application. If you need to create or substantially adjust a classic Subtotal outline and the command is unavailable in the browser, open the workbook in the Excel desktop app. Existing workbook structures may also display differently depending on the feature and browser version.
Frequently Asked Questions
Can Excel automatically group every repeated value in a column?
Not with the ordinary Group command. Sort the records by that column and use Subtotal to create collapsible breaks, or use a PivotTable to summarize categories. Manual Group only groups the rows you select.
Do I have to sort before using Subtotal?
Yes, if you want one section per category. Sorting the whole data range by the chosen field places equal values together so Excel can insert a subtotal at each change. Without that order, a category appearing in separate runs can produce separate subtotals.
Can I group rows without adding subtotal calculations?
Yes. Select the row headings for a contiguous block and choose Data > Outline > Group. This creates a collapsible outline only; it does not identify categories or calculate totals for you.
Why can’t I use Subtotal on my Excel table?
The Subtotal command is unavailable for data formatted as an Excel table. Convert the table to a range if you need a traditional outline, or keep the table and create a PivotTable for a separate category summary.
How do I remove row groups in Excel?
Select the grouped rows and choose Data > Outline > Ungroup to remove a selected level. To remove subtotal rows and their outline, open the Subtotal dialog and choose Remove All.
Related
- Excel Text to Columns: Advanced Options and Uses
- Excel Text to Columns: Split Alt+Enter Line Breaks
- Excel Text to Columns and Rows: How to Split Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Change Email View Settings in Outlook: A Step-by-Step GuideTo change how email looks in Outlook, open the view or layout controls for your version, then adjust…
- Excel Cell Won’t Move With Arrow Keys? Fixes That WorkIf an Excel cell will not move when you press an arrow key, first check whether Scroll Lock…
- Google Sheets Checkbox Strikethrough: How to Set It UpTo strike through a task when its Google Sheets checkbox is checked, select the task cells or rows,…
- Excel Solver Tutorial: Build and Solve Better ModelsTo use Solver in Excel for Microsoft 365 on Windows, enable the Solver Add-in through File > Options…
- Budget Template in Microsoft Excel: Find and Use OneYes—Microsoft Excel includes budget templates you can open from File > New and search for with terms such…
- Data Validation Framework: Design, Rules, and ExamplesA data validation framework is a structured way to define, run, and maintain checks that determine whether data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.