To group rows in Excel by matching values, sort the list by the column you want to group, then choose Data > Subtotal and set At each change in to that column. Excel inserts a subtotal row at each change and adds outline controls so you can collapse or expand each group. Keep a copy of your data first, especially if you need the original layout unchanged.
What You’ll Need
- Excel for Microsoft 365 on Windows, or the desktop version of Excel for Mac. The instructions below use the Windows ribbon; the main commands are also available in desktop Excel for Mac, though their placement can vary slightly by release.
- A rectangular list with one header row and consistent columns, such as Department, Employee, and Sales. Avoid blank rows or columns inside the list.
- A column whose values define the groups, plus any numeric column you want Excel to total, average, or count.
- A saved copy of the workbook. The Subtotal command inserts rows and changes the sheet layout, so a backup makes it easy to return to the original.
Step-by-Step
- Identify the value that defines each group. Decide which column should control the breaks between groups. For example, if you want one section per region, use the Region column; if you want a section for each product category, use Category. Check that the cells use consistent values. A trailing space or a spelling difference can make what looks like one category behave as two different values.
- Check and prepare the list. Make sure there is one header row, with a distinct heading for each column, and no completely blank rows splitting the data. Keep each record on one row. If a record has a region, customer, and sales amount, those details should stay together when sorted. Remove filters if they are hiding records, or make a note that you are working with a filtered subset.
- Convert an Excel table to a regular range if necessary. Excel’s Subtotal command does not work directly on a formatted Excel table. Click a cell in the table, open the Table Design tab, choose Convert to Range, and confirm. This keeps the cell contents and formatting, but removes the table’s structured behavior and automatic table expansion. If the Table Design tab is not visible, the selected cell may not be inside a table.
- Sort the entire list by the grouping column. Click a cell in the data and choose Data > Sort. In the Sort dialog, select the grouping column under Sort by and choose the order you want, such as A to Z. If Excel asks whether to expand the selection, choose Expand the selection so the other fields in each record move with it. Sorting is essential: Subtotal creates a new section only when the selected value changes as Excel moves down the sheet.
- Add a second sort level if the groups need an internal order. In the Sort dialog, choose Add Level. Set the first level to your group field, such as Region, and the second to a field such as Employee or Date. Excel sorts within each region while keeping each region together. Do not sort only one column by itself; that would separate values from the rest of their records.
- Open the Subtotal dialog. Click a cell in the sorted list, then select Data > Subtotal. In Excel for Windows, this command is in the Outline area of the Data tab. If you cannot find it, check whether the list is still an Excel table or whether you are using Excel for the web, which does not provide the same full desktop outline workflow. Open the workbook in desktop Excel when the command is unavailable.
- Choose when Excel should start a new group. In the Subtotal dialog, set At each change in to the header for your grouping column, such as Region. Choose the calculation in Use function. For sales figures, Sum is common; for a count of filled entries, choose Count; for a typical value, choose Average. The available choices include other calculations such as Max and Min.
- Choose the column to calculate and confirm the options. Under Add subtotal to, tick the numeric field or fields to calculate, such as Sales. Leave other fields unticked if they should not receive a subtotal. Keep Replace current subtotals selected for a new setup, and leave Summary below data selected if you want each subtotal beneath its group’s records and a grand total at the end. Click OK. Excel inserts subtotal rows and outline controls.
- Use the outline levels to collapse or expand the detail. Numbered outline buttons appear near the upper-left corner of the sheet, usually beside the row numbers. Click the highest-numbered level to show all detail, a middle level to show group totals with fewer details, or level 1 to show only the overall total. Use the plus and minus buttons next to individual groups to open or close one section at a time. The data is hidden from view, not deleted.
- Review the result before sharing it. Check that every expected group appears once, the subtotal rows show the intended calculation, and the grand total makes sense. Expand all groups if someone needs the individual records. Save the workbook after confirming that the outline works as expected.
For example, suppose a list has Region, Rep, and Sales columns. Sort by Region, then use At each change in: Region, Use function: Sum, and Add subtotal to: Sales. Excel places a sales total after each region’s records. If the West rows show 1,200 and 800 in Sales, its subtotal should be 2,000. The outline lets you hide those two detail rows while keeping the West total visible.
The Subtotal method is useful when you want visible subtotal rows in the worksheet itself. It does not merely label or color matching rows: it adds calculation rows and uses the sorted order to define where groups begin and end.
Common Mistakes
- Not sorting by the group field first. If the same value appears in separate parts of the list, Excel treats each uninterrupted block as its own group. Sort the entire data range by the field selected in At each change in before applying Subtotal.
- Sorting only the grouping column. This can detach names, dates, or amounts from their original records. Use the Sort dialog and expand the selection so whole rows are reordered together.
- Leaving inconsistent labels in the group field. Values such as North, north, or North followed by an extra space may be treated as separate entries. Standardize the labels before sorting. If you suspect hidden spaces, inspect and clean the source values rather than assuming the outline is wrong.
- Trying to apply Subtotal to an Excel table. Convert the table to a range first. The command may be unavailable or inactive while a table is selected.
- Choosing the wrong calculation or column. Sum is appropriate for numeric amounts, while Count is for counting entries. Check the boxes under Add subtotal to carefully; selecting an ID or date field for Sum will not give the result most people expect.
- Applying Subtotal repeatedly without removing the old results. Repeated runs can create nested or duplicate subtotal rows. Use Data > Subtotal > Remove All before rebuilding the outline if the existing result is no longer right.
Tips
- Keep a clean source copy. Subtotal changes the row layout. Duplicate the worksheet before sorting and inserting subtotal rows if you need to preserve the unsorted version for data entry or import.
- Use more than one sort level for readable sections. A primary sort by Department and a secondary sort by Last Name keeps departments grouped while making names easier to scan.
- Use outline levels for quick summaries. The numbered buttons are faster than closing each section individually. The outline is especially useful when you need to switch between a full detail view and a compact total view.
- Choose a PivotTable when you need flexible analysis. A PivotTable can arrange a field into row labels and summarize another field without inserting subtotal rows into the original list. Select the data and choose Insert > PivotTable. This is often a better choice for filtering, rearranging fields, or comparing several measures.
- Choose manual grouping when you only need collapsible sections. Sort values so each section is contiguous, select the detail rows for one section, then choose Data > Group. In the dialog, select Rows if prompted. Repeat for other sections. This hides and shows rows but does not create totals or automatically identify matching values.
- Remember that the outline is based on the current arrangement. If you add records or change group labels later, sort and rebuild the subtotals so the section breaks match the updated data. Subtotal is not a live grouping rule that automatically reorganizes every new entry.
Troubleshooting
- Subtotal is greyed out or missing: Check whether the selected data is an Excel table and convert it to a range. If you are in Excel for the web, open the file in the desktop app for the full Subtotal and outline workflow. Also make sure you selected a worksheet range rather than an object such as a chart.
- The same label has multiple subtotal rows: The label probably appears in separate blocks, or the underlying text is not exactly the same. Sort again by the group column, then check for variations in spelling, spaces, or text-versus-number values.
- Rows do not collapse when you click the outline buttons: Confirm that you applied Subtotal or manual grouping; sorting alone does not create an outline. If outline symbols are hidden, check Excel’s display settings for outline symbols, or remove and recreate the grouping.
- The totals are too high or too low: Look for duplicate records, hidden rows, or an incorrect field selected under Add subtotal to. Expand the groups and compare a small group’s detail values with its subtotal. If filters were active, verify whether the result includes the records you intended to analyze.
- New entries are outside the groups: The existing outline does not automatically absorb and regroup every appended row. Add the records, sort the full range again, remove the old subtotals, and apply Subtotal again.
- The layout looks wrong after a second attempt: Choose Data > Subtotal > Remove All, then repeat the sort and setup steps. Remove All clears Excel’s subtotal rows and outline structure; it does not delete the underlying detail records.
Frequently Asked Questions
Can Excel group rows automatically just because values match?
Not by simply selecting a column. For worksheet outline groups with totals, sort the list and use Data > Subtotal. For collapsible rows without totals, sort first and group the row ranges manually. A PivotTable is another option for a summary organized by matching values.
Does Subtotal change or delete my original records?
It reorders records when you sort and inserts subtotal rows, but it does not remove the detail records. The outline hides details when collapsed; it does not erase them. Keep a copy if the original row order matters.
Can I group by two columns, such as Department and Team?
Yes. Sort first by Department and then by Team. Apply Subtotal to Department, then run Subtotal again for Team with Replace current subtotals cleared. This creates nested subtotal levels. If the result becomes confusing, use Remove All and rebuild it in the intended order.
How do I remove the groups and subtotal rows?
Choose Data > Subtotal and click Remove All. Excel removes the subtotal rows and outline grouping while keeping the detail data. If you only want to expand the groups temporarily, click the outline level that shows all detail instead.
Will the groups update when I add more rows?
No. Subtotal is based on the current sorted range and inserts rows at the current value changes. Add the new records, sort the complete list, remove the old subtotals, and apply the command again to refresh the grouping.
Is manual grouping the same as Subtotal?
No. Manual grouping gives selected rows expand-and-collapse controls, but it does not identify values or calculate totals. Subtotal detects changes in a sorted field, inserts calculation rows, and creates an outline based on those breaks.
Related
- Excel Solver: Add Decision Variables and Set Constraints
- Excel Solver Binary Constraint: How to Set It Up
- How to Create Goal Seek in Excel: A Practical Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Keep Numbers as Text in Excel: A Step-by-Step GuideTo keep numbers as text in Excel, format the destination cells as Text before typing or pasting, or…
- Can’t Block a Sender in Outlook? Fixes That WorkYes, you can usually block a sender in Outlook, but the option and its effect depend on which…
- Excel Merge Blank Cells Below: Steps and AlternativesTo merge blank cells below a value in Excel for Microsoft 365 on Windows, select the cell containing…
- Excel Import CSV as Text: Keep Every Value UnchangedTo import a CSV into Excel as text, use Data > From Text/CSV, choose the file, select Transform…
- Excel Conditional Formatting Question Mark ExplainedIn Excel conditional formatting, a question mark (?) is a wildcard in criteria: it matches exactly one character.…
- How to Add Separate Page Numbers in Word by SectionTo give different parts of a Word document their own page-number sequence, divide the document with a section…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.