Excel: Group Rows by the Same Value

Yes. In Excel for Microsoft 365 on Windows, sort your records by the value you want to group, then select the matching rows and choose Data > Outline > Group. To create collapsible groups and subtotals automatically, sort by that field first and choose Data > Outline > Subtotal.

What “group rows” can mean in Excel

Excel uses “group” for a few different jobs, so it helps to decide what outcome you want before changing the sheet. You might want matching records next to one another, collapsible sections that hide or show detail, a summary of each category, or a shorter report that combines records. These are related but not interchangeable.

Sort: puts rows with the same value next to one another without hiding or summarizing them.
Outline grouping: adds expand-and-collapse controls to selected, adjacent rows. It does not find matching values by itself.
Subtotal: creates an outline and inserts a summary row whenever a selected field changes.
PivotTable or Power Query: summarizes records by category, usually in a separate report or transformed result.

For example, if a sales list has a Region column, sorting by Region makes all East records adjacent. Grouping those rows lets you collapse the East detail. A PivotTable can instead show one total per region. Choose based on whether you need to keep and inspect the original records or mainly want a summary.

Prepare the data before grouping

Start with a rectangular list: one header row, one record per row, and one type of information per column. Avoid blank rows or columns inside the list, merged cells, and repeated header rows. Excel needs a clear data range so a sort or subtotal applies to complete records rather than shifting only one column.

Check that values intended to match are genuinely consistent. For instance, “North” and “North ” (with a trailing space) can behave like different entries. “North,” “NORTH,” and “north” are generally treated as the same text for ordinary sorting and matching, but inconsistent spelling, hidden spaces, or numbers stored as text can still disrupt a clean result. If a column contains a mixture of labels and blanks, decide whether blanks belong in their own group.

If the list is an Excel table, sorting it is convenient because the table keeps each record together and carries its headers along. However, Excel’s Subtotal command is unavailable for a table. If you need that command, select a cell in the table and use Table Design > Convert to Range, then confirm. Converting removes table features such as automatic structured references, so consider making a copy first if those features matter.

Sort records so identical values are together

Sorting is the essential first step for any method that depends on adjacent groups. Click a cell in the data, then choose Data > Sort. In the Sort dialog, select the column to group by under “Sort by,” choose the appropriate sort order, and click OK. If the list has headers, make sure “My data has headers” is selected so Excel sorts the records rather than treating the headings as ordinary data.

For example, a list with columns Region, Salesperson, and Amount can be sorted by Region. East entries will then sit together, followed by the other regions. To create subgroups, add another sort level for Salesperson. This produces a predictable order, but it does not create expand-and-collapse controls or calculate totals on its own.

When sorting a range that is not an Excel table, make sure the entire data range is included. If Excel asks whether to expand the selection, choose “Expand the selection” so names, dates, and amounts stay attached to their original records. Sorting only the Region column would scramble the relationship between regions and the rest of each row.

Create collapsible groups manually

After sorting, you can group each contiguous block of matching rows. Use the row-number headings at the left of the worksheet to select the detail rows for one value; do not include the column headings. Then choose Data > Outline > Group and, if Excel asks, select Rows. Repeat for each category. A bracket and a minus button appear beside the grouped rows. Click the minus button to hide the detail; click the plus button to show it again.

Suppose rows 2 through 8 are East, rows 9 through 14 are North, and rows 15 through 20 are West. Group rows 2–8, 9–14, and 15–20 separately. The labels in the Region column do not control these groups: Excel stores the selected row ranges. If you later sort the data differently, the outline may no longer correspond to the categories, so remove or rebuild the groups after changing the order.

To remove a manual group, select its rows and choose Data > Outline > Ungroup. The Clear Outline command removes the outline structure from the worksheet. Grouping is a display aid, not deletion: collapsed rows remain in the workbook and can still affect calculations, depending on the function used.

Use Subtotal for automatic category summaries

Subtotal is often the closest match when someone asks Excel to group every row with the same value and show a total for each group. First sort the records by the grouping column. Select a cell in the list, then choose Data > Outline > Subtotal. In the dialog, set “At each change in” to the category field, choose a function such as Sum or Count, and select the numeric column or columns under “Add subtotal to.” Click OK.

For example, if the sorted list has Region in column A and Amount in column C, choose Region under “At each change in” and Sum under “Use function,” then select Amount. Excel inserts a subtotal row after each region and a grand total, and adds outline controls so you can show detail or summary levels. The exact row numbers depend on how many records are in the list.

Subtotal works best on a normal range, not an Excel table, and the grouping field should be sorted so each category is in one uninterrupted block. It can also be used with Count, Average, Max, Min, and other available summary functions. If you run Subtotal again on the same list, review the dialog’s “Replace current subtotals” option so you do not accidentally stack additional subtotal rows.

Choose a PivotTable when you need a report

A PivotTable is usually a better choice when the goal is to compare totals by category, rearrange a report, or summarize a large list without inserting subtotal rows into the source data. Select a cell in the list and choose Insert > PivotTable. In the dialog, confirm the table or range and choose where to place the PivotTable. Drag the category field, such as Region, to Rows, and drag a numeric field such as Amount to Values.

Excel generally summarizes numeric values with Sum and text values with Count, though you can change the calculation through the value field settings. You can add another field under Rows to create nested categories, or place a field in Filters to narrow the report. The PivotTable is a separate summary view: it does not turn the original records into collapsible outline groups. If the source data changes, refresh the PivotTable to update its results.

Use a PivotTable when readers need an interactive summary; use Subtotal when they need the original rows with category totals interleaved. A PivotTable can also be easier to maintain when categories are added or the report layout changes, while manual outline groups are simpler for a short, stable list.

Use Power Query to create a grouped result

Power Query is useful when grouping is part of a repeatable data-cleaning process. Select a cell in the data and choose Data > From Table/Range to open the Power Query Editor. Select the category column, then choose Home > Group By. In the dialog, choose the grouping column and an operation, such as Sum or Count, for the other column. Choose Close & Load to return the result to Excel.

Grouping in Power Query typically produces a transformed summary, rather than a set of original worksheet rows with outline buttons. You can also use the “All Rows” operation to retain nested tables for each group, but the result is still a query output that you refresh, not the same thing as Excel’s worksheet outline. Use it when you want to repeat the same transformation on updated source data. Menu names and available options can vary slightly with the Power Query version.

Common problems and practical fixes

Group is unavailable or the wrong rows collapse: Select the row headings for the detail range, not just cells in a column. For automatic Subtotal, use a normal range rather than an Excel table.
Matching values are split into separate blocks: Sort by the grouping field before using Subtotal or manual outline groups. Check for spelling differences, blanks, and extra spaces.
Rows no longer match their original information: Repeat the sort with the full data range selected, or convert the list to a table before sorting.
The subtotal is not the expected total: Confirm that the amount column contains numeric values and that the correct function and “Add subtotal to” fields are selected.
Filters appear to hide records: Filtering and grouping are different. A filter temporarily hides rows based on criteria; an outline collapses a selected range. You can use both, but a filter does not create category groups.

Outline buttons can also be confusing if a worksheet already has multiple levels. The numbered buttons near the top-left of the sheet show different outline detail levels: a lower level hides more detail, while a higher one reveals more. If the outline becomes hard to manage, choose Data > Outline > Ungroup or Clear Outline and recreate only the groups you need.

Windows, Mac, and Excel for the web

The ribbon paths above are for Excel for Microsoft 365 on Windows. In Excel for Mac, the Data tab also contains grouping and sorting controls, though their placement can differ. The grouping shortcut is Option+Shift+Right Arrow on Mac; the Windows equivalent is Alt+Shift+Right Arrow. These shortcuts group selected rows or columns and do not identify matching values automatically.

Excel for the web may not offer all desktop outline and Subtotal controls. If you cannot find Subtotal or need to manage a complex outline, open the workbook in the desktop app. Sorting and PivotTable capabilities are available in web versions, but the precise options can depend on the workbook and the features supported in that version.

Frequently Asked Questions

Can Excel automatically group rows with the same value?
Yes, if you mean category subtotals and a collapsible outline: sort the records by that field, then use Data > Outline > Subtotal. The basic Group command itself does not detect duplicate values; it groups row ranges you select.

How do I group rows without adding subtotals?
Sort by the category, select the adjacent row numbers for each category, and choose Data > Outline > Group. This creates collapsible sections without inserting summary calculations.

Can I group rows with the same value without sorting?
Not reliably with worksheet outline groups. Outline groups apply to adjacent row ranges, so matching records scattered through the sheet would need to be gathered together first. A PivotTable can summarize scattered records by category without rearranging the source list.

Does collapsing a group delete or exclude its rows?
No. Collapse only hides the rows on screen. The data remains in the worksheet. Some functions, such as SUM, include hidden rows; functions designed to respect filtered or hidden rows can behave differently, so check the function’s rules before relying on a displayed total.

What is the difference between Group and Subtotal?
Group adds expand-and-collapse controls to selected rows. Subtotal detects changes in a sorted field, inserts summary rows, and creates an outline. Use Group for manual sections and Subtotal when you want category summaries alongside the original records.

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.