Excel Group Rows With Matching Values in a Column

To group rows that share a value in Excel, first sort the list by that column, then use Data > Subtotal to insert summaries and create collapsible outline groups. If you only want to collapse rows without inserting subtotals, sort the rows and group each contiguous block with Data > Group; Excel does not automatically create outline groups just because cell values match.

What “group rows” can mean in Excel

The phrase can describe two different jobs. You might want to organize a long list so each value appears together, or you might want to collapse and expand the rows for each value. Sorting does the first job: it places matching values next to one another. It does not add expand-and-collapse controls.

Excel’s outline feature adds those controls along the left edge of the worksheet. Its Data > Group command groups rows you select, but it does not inspect a column and find matching values for you. The Subtotal command is a more automated option for a sorted list: it adds a summary row whenever the selected column’s value changes and creates an outline you can expand or collapse.

For example, imagine a sales list with Region in column A, Salesperson in column B, and Amount in column C. Sorting by Region gathers all East rows, all North rows, and so on. Subtotal can then calculate the amount for each region and create collapsible sections. If you only need to hide or reveal each region’s detail, manual grouping can do that without adding calculations.

Sort matching values into adjacent rows

Rows must be adjacent before you can group them as a block. In Excel for Microsoft 365 on Windows, click a cell in the list, choose Data > Sort, select the column to sort by, and choose the order you want. If Excel asks whether to expand the selection, choose that option so the other columns stay attached to their original records. Sorting only the key column can scramble the relationship between names, dates, and amounts.

For reliable results, include the column headers and confirm that the list has a header row in the Sort dialog. If the data is an Excel table, click a cell in the table and use the sort control in the key column’s header, or use Data > Sort. A table’s sort also moves complete records together.

Sort by the column you want to group, such as Department, Date, or Region.
If you want a second level of order, add another sort level—for example, sort first by Region and then by Salesperson.
Check that similar-looking values are truly the same. Leading or trailing spaces, inconsistent spelling, and different labels such as N. Region and North Region can split what you thought was one group.

Sorting changes row order, not the underlying values. If the original order matters, add an index or sequence column before sorting so you can sort back to the starting order later. A filter is different: it hides records that do not meet a condition but does not create collapsible sections for each distinct value.

Use Subtotal to create collapsible groups and summaries

For a sorted, ordinary cell range, Subtotal is the quickest built-in way to create groups at changes in a value. Select a cell in the range, then choose Data > Subtotal. In the dialog, choose the grouping column in At each change in, select a calculation such as Sum or Count in Use function, and select the numeric or other field to calculate in Add subtotal to. For the sales example, choose Region, Sum, and Amount, respectively, then click OK.

Excel inserts a subtotal row after each region and a grand total, and it displays outline controls beside the row numbers. The outline level buttons let you show just the grand total, the subtotals, or all detail rows. The minus and plus controls beside individual sections collapse or expand those rows. This is useful when readers need both a concise summary and access to the records behind it.

Subtotal is intended for a normal range, not an Excel table. If the command is unavailable or does not behave as expected, convert the table to a range first: click inside the table, choose Table Design > Convert to Range, and confirm. This removes table functionality such as structured references and automatic table expansion, so consider copying the data or converting back to a table after creating the outline if those features matter.

Use the Replace current subtotals option carefully when running the command again. If selected, Excel replaces existing subtotal rows; if cleared, it can create nested subtotal levels when you have sorted by more than one field. For example, sorting by Region and then Salesperson can support salesperson subtotals nested within region subtotals. Check the selected fields before clicking OK, because the result depends on the sort order.

Group rows manually without adding subtotal rows

When you want expand-and-collapse controls but do not want extra summary rows, group the sorted blocks manually. Select the row numbers for the detail rows in the first category—not the column cells—then choose Data > Group. If Excel displays a dialog asking whether to group Rows or Columns, choose Rows. Repeat for each contiguous category block.

For instance, after sorting by Region, select the rows containing East detail records and group them, then repeat for North and West. You can leave header and total rows outside the selected blocks, so those remain visible when the details are collapsed. A minus button appears beside each group; click it to collapse the rows, and click the plus button to expand them.

Manual groups are static. If you add a new record, change its category, or resort the list, the existing row groups do not automatically rebuild themselves to match the new values. Review or remove the groups after changing the data. To remove a group, select its rows and choose Data > Ungroup; to clear all outline levels, use Data > Ungroup > Clear Outline. The exact menu arrangement can vary slightly by Excel build.

When a PivotTable is a better fit

A PivotTable is often the better choice when the main goal is to summarize records by category and refresh the summary as the source data changes. Select the list, choose Insert > PivotTable, confirm the source range, and choose where to place the PivotTable. In the field list, put the category field—such as Region—in Rows, and put a numeric field such as Amount in Values. Excel creates a compact summary without inserting subtotal rows into the source list.

PivotTables can expand or collapse row fields, and users can double-click a summary value to see the underlying records on a new sheet. They are designed for analysis, however, not for rearranging or hiding rows in the original data. After changing the source records, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click it and choose Refresh. If new records fall outside the original source range, use a table as the source or update the source range so those records are included.

Choose Subtotal when you want summaries inserted into a sorted list and an outline attached to those rows. Choose manual grouping when you only need to fold and unfold particular row blocks. Choose a PivotTable when you want a separate, refreshable summary or need to compare multiple fields without changing the source layout.

What happens when the data changes

Neither Subtotal nor manual outline groups should be treated as a live rule that continuously detects categories. Subtotal is based on the sorted values at the time you run it. If a row is added or a category is edited, a new group may not appear in the right place until you sort and run Subtotal again. A manually drawn outline likewise reflects selected row numbers, not the text in a category column.

A practical maintenance routine is to keep an untouched source copy, sort the working range by the grouping field, and then rebuild the subtotals or manual outline after significant changes. If new records are added often, an Excel table can make the source range expand automatically, while a PivotTable can provide a refreshable summary. Those features help with updating the data, but they do not turn manual row outlines into automatic value-based groups.

Be especially cautious when editing a range that already contains inserted subtotal rows. Sorting only part of the range can separate subtotals from their records. If the list becomes difficult to manage, remove the existing subtotals with Data > Subtotal and use Remove All, then sort the underlying records and recreate the summaries. Keep a backup if the layout or calculations are important.

Common problems and how to fix them

The rows are together but there are no plus and minus buttons: Sorting only orders the records. Use Subtotal for automatic summary groups or select row blocks and choose Data > Group.
The Subtotal command is unavailable: The range may be an Excel table, or the selection may not be a standard list. Convert the table to a range if appropriate, then try again.
One category appears in several sections: The rows may not be sorted by the grouping column, or labels may differ because of spaces, spelling, or inconsistent data entry. Sort again and inspect the labels.
Other columns no longer match their original records: Only part of the data was sorted. Undo immediately if possible, then sort the complete range or table rather than a single column.
Totals appear for the wrong field: Reopen Subtotal and check Use function and Add subtotal to. For a sum by region, the grouping field and the field being summed should be different selections.

Excel’s comparisons for grouping are based on the values in the selected field, so visually similar text is not necessarily identical. For example, a trailing space can make a label behave differently from the same label without that space. Cleaning inconsistent source data before sorting can prevent confusing duplicate-looking groups.

Choosing the right method

Start by asking whether the goal is organization, visibility, or analysis. If you only need matching records next to one another, sort. If you want an outline on the original rows and a subtotal or count for each category, use Subtotal after sorting. If you want to hide detail but do not need calculations, select each adjacent row block and use Group. If the result should be a flexible report, build a PivotTable.

On Excel for Mac, the same core ideas apply, but ribbon layout and dialog placement can differ from Windows. Look for Sort, Subtotal, Group, and Ungroup on the Data tab; some commands may be arranged differently depending on the Excel version. Excel for the web supports sorting and PivotTables, but its available outline and Subtotal controls can differ from the Windows desktop app. If a command is missing in the web app, open the workbook in desktop Excel to create or manage the outline.

Frequently Asked Questions

Can Excel automatically group rows with the same value?
Not with the ordinary Data > Group command: it groups selected row numbers, not matching values. Sort the list and use Data > Subtotal to create groups at value changes, or use a PivotTable for a separate category summary.

How do I group rows by a column without adding totals?
Sort by that column, select the row numbers for each contiguous block, and choose Data > Group. If Excel asks, select Rows. Repeat for the other blocks; this creates collapsible outlines but does not insert subtotal calculations.

Can I group rows in an Excel table?
You can sort and filter a table, but the Subtotal command is designed for a normal range. To use Subtotal, click in the table, choose Table Design > Convert to Range, and confirm. Consider keeping a copy because conversion removes table behavior.

Why are there no plus and minus signs after sorting?
Sorting does not create an outline. Run Data > Subtotal on a suitable sorted range, or select the rows you want to collapse and choose Data > Group. The outline controls appear beside the row numbers.

Will new rows join the correct group automatically?
Not reliably with manual groups or existing Subtotal outlines. Sort and rebuild the outline after changes. For frequently updated category summaries, use an Excel table as a PivotTable source and refresh the PivotTable when records change.

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.