Excel Auto Group Rows by Cell Value: What You Can Do

How-to basics · Updated November 18, 2026

No—not as a built-in rule that continuously groups rows whenever a cell value changes. In Excel for Microsoft 365 on Windows, you can sort by the value and use Data > Subtotal to create collapsible outline groups at each change, but this is a one-time operation that you must refresh when the data changes.

What Excel means by grouping rows

Excel’s row grouping is an outline feature. It adds controls beside the row numbers so you can collapse a section and expand it again. The controls organize the worksheet’s display; they do not create a permanent relationship between a row and a category value.

That distinction matters when you ask Excel to group by a cell value. A normal outline group is a set of adjacent rows that you select. Excel does not watch a category column and automatically move rows into matching groups as you edit or append records. The Subtotal command is the closest built-in option: it looks for changes in a selected field and creates outline levels and summary rows at those changes.

For example, a sales list with several consecutive rows for East, followed by several for West, can be outlined at the East–West boundary. If East appears again farther down the list, Excel sees another change, not a continuation of the first East section. Sorting the list first is therefore essential.

Create collapsible groups with Subtotal

Use this method when you want grouped sections with a count, sum, or other summary calculation. Work on a copy if the worksheet contains important formatting or formulas: Subtotal inserts rows and changes the layout of the list.

  1. Make sure the data is a simple range with one header row and no completely blank rows or columns inside the list. Include every related column when sorting.
  2. Sort the complete list by the field you want to group, such as Region or Department. Select a cell in the list, choose Data > Sort, select the field, and confirm that Excel is sorting the full range rather than only one column.
  3. Select a cell in the sorted list, then choose Data > Outline > Subtotal.
  4. In the Subtotal dialog, set At each change in to the grouping field. Choose a function under Use function, such as Count or Sum.
  5. Under Add subtotal to, select the field to summarize. For a count of records, choose a column that has a value on every record, such as an order number. For a sales total, select the sales amount column and choose Sum.
  6. Leave Replace current subtotals selected for a first pass, then select OK. Excel inserts summary rows and displays outline controls at the left of the worksheet.

The outline level buttons above the row numbers let you show different amounts of detail. The most collapsed level typically shows the overall result, the next level shows the category summaries, and the most detailed level displays the records. You can also use the plus and minus controls beside individual sections. The precise outline display depends on the choices in the Subtotal dialog.

Example: grouping a sales list by region

Suppose a worksheet has columns for Region, Order ID, and Sales, with one order per row. Sort the whole list by Region. In the Subtotal dialog, choose Region for At each change in, Count for Use function, and Order ID for Add subtotal to. Excel adds a count at each region boundary and creates collapsible sections for the rows in each region. Choosing Sales and Sum instead gives a sales total for each region.

If the same region occurs in separate portions of the unsorted data, the results will be split into separate sections. For example, East, West, East produces three sections. Sorting makes equal region values adjacent so each region has one continuous block. Keep the header row intact and do not sort just the Region column by itself; doing so can disconnect values from their associated orders and sales.

Subtotal is designed for a list with records, not merely a blank worksheet where you want to hide blocks of rows. It inserts summary rows as part of its work. If you need only the expand-and-collapse controls, without added totals or count rows, use manual grouping or an automation method instead.

Does the grouping update when values change?

No. Subtotal creates an outline from the data as it exists when you run the command. If you change a category, add records, or change the sort order afterward, the outline does not reliably reorganize itself to match the new values. A new row may also sit outside the existing subtotal structure, depending on where it is inserted.

To rebuild the result after editing the list, remove the old subtotal rows with Data > Outline > Subtotal > Remove All, sort the complete list again, and run Subtotal with the updated settings. Removing subtotals removes the generated summary rows and outline structure; it does not delete the original records. Check the worksheet before proceeding if it contains other outline groups that you need to preserve.

For occasional reports, this refresh process is usually straightforward. For a recurring report that changes every day or is fed by a growing data source, repeatedly rebuilding the outline may be inconvenient. A PivotTable or a macro-driven workflow may be a better fit, depending on whether you need an interactive summary or actual worksheet row groups.

Group rows without adding subtotal lines

For a one-off layout, select the rows in a contiguous section and choose Data > Outline > Group. If Excel asks whether to group rows or columns, choose Rows. Repeat for each section. You can then collapse and expand the selected sections with the outline controls.

This approach gives you the outline appearance without inserting subtotal calculations, but it does not read the category cells or create groups from their contents. You must identify the row ranges yourself. If you insert, delete, or move rows later, review the outline because its groups may no longer match the categories they were intended to represent.

Manual grouping is useful when the sections are editorial or structural—for example, grouping the detail lines beneath a heading in a budget worksheet. It is less suitable for a raw transaction list that gets sorted or refreshed regularly. In that case, the selected row numbers can stop representing the intended category, even though the outline controls remain.

PivotTables and other alternatives

A PivotTable is often the better built-in choice when the real goal is to browse or summarize records by category. Insert a PivotTable from the data, place the category field in Rows, and add a suitable field to Values. The PivotTable organizes records by category and supports expanding or collapsing items. It is a report view, however, not the same as grouping the original worksheet rows. It can be refreshed when its source data changes, but you may need to adjust the source range or use an Excel Table so new records are included.

Power Query is useful for repeatable importing, cleaning, sorting, and reshaping of data. It can produce a cleaned table for analysis, but loading a query result does not by itself create expandable outline groups on the source worksheet. Conditional formatting can highlight matching values, but it also does not group or hide rows.

If a process specifically requires actual outline groups to be recreated automatically after each update, VBA can be written to sort the data, find changes in a chosen column, and group the corresponding row ranges. That is a custom solution rather than an Excel setting. A macro needs to account for headers, blank cells, changing data length, and whether the source has been sorted. Save a copy before running any macro: macro actions cannot be undone with Ctrl+Z.

Tables, platforms, and practical limits

Excel Tables are helpful for maintaining structured data because they expand as you add records and make sorting and filtering easier. However, the Subtotal command is not available while the data is an Excel Table. If you need that command, select a cell in the table and use Table Design > Convert to Range, then apply Subtotal to the resulting range. Converting to a range removes table behavior, so consider whether automatic table expansion and structured references are more valuable than worksheet outline subtotals.

The steps above describe Excel for Microsoft 365 on Windows. Desktop Excel for Mac also has outline and subtotal features, though ribbon placement can differ by build. Excel for the web has more limited support for creating or rebuilding subtotal outlines; if the Subtotal command is unavailable, use desktop Excel for that operation. An existing outline may display differently across versions, so check the result in the version where the workbook will be used.

Also consider how the worksheet will be shared. A collapsed outline can make a report easier to scan, but it can hide detail from someone who opens the file. Add a clear note or keep a visible summary so readers understand that rows are hidden by grouping rather than missing from the data.

Common problems and fixes

  • Groups are split into repeated sections: Sort the entire data range by the field selected in At each change in, not just that one column.
  • Subtotal is unavailable: Check whether the records are formatted as an Excel Table. Convert the table to a range if you specifically need the Subtotal command.
  • Counts look too small: The selected count field may contain blanks. Choose a field that is populated for every record, such as a required ID.
  • Rows appear in the wrong categories: Confirm that the sort included all columns and that the category values are consistent. Extra spaces or spelling differences can make apparently similar labels behave as separate values.
  • New data is not included: Rebuild the subtotals after adding records, and check that the new records were included in the sorted range.
  • The list layout changes unexpectedly: Subtotal inserts summary rows. Remove the subtotals and undo only if the operation is still the most recent action; otherwise restore a saved copy or rebuild the list carefully.

Frequently Asked Questions

Can Excel automatically group rows when a cell value changes?
No, not as a continuously updating built-in rule. Data > Subtotal can create outline groups at changes in a sorted field, but you must run it again after relevant changes.

Do I have to sort before using Subtotal?
Yes, if you want each category to appear as one continuous group. Sorting the complete data range by the selected field brings matching values together before Excel inserts subtotal rows.

Can I group rows by value without adding totals?
Yes, select the appropriate adjacent rows and use Data > Outline > Group. That creates manual outline groups, but Excel does not automatically find or maintain the category-based row ranges for you.

Can I use Subtotal inside an Excel Table?
No. Convert the Table to a range with Table Design > Convert to Range before using the Subtotal command. Remember that the converted range no longer has the Table’s automatic expansion behavior.

What is the best option for a report I update frequently?
Use a PivotTable when you need refreshed category summaries and expandable report items. If you need the original worksheet rows themselves grouped automatically, that requires a custom macro or another automation workflow, not a standard live grouping setting.

Related

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.