To group rows in Excel by a cell value, first sort the list by the column containing that value, then select the row numbers for one contiguous set of matching records and choose Data > Outline > Group. For automatic groups with subtotal lines, sort by the value and choose Data > Outline > Subtotal, then specify the column and calculation.
What grouping rows by value means
Excel’s row grouping creates an outline: rows can be collapsed to hide their detail and expanded again when needed. It does not, by itself, search for matching values or rearrange records. That distinction matters because the standard Group command works on selected, adjacent rows, not on a condition such as “group every row where Department equals Sales.” Sorting first puts matching records together so each set can be grouped.
For example, imagine a list with Department in column A, Employee in column B, and Hours in column C. If the department values are mixed throughout the list, sort by Department so all Sales rows sit together, all Support rows sit together, and so on. You can then outline each department’s detail rows. Keep the heading row outside the selection, and leave the data itself intact: grouping hides rows; it does not delete them.
There are two common meanings of “group by value.” You may want collapsible sections in the original worksheet, or you may want a summary of values and totals. Manual grouping is suited to the first need. The Subtotal command or a PivotTable is often better for the second.
Prepare and sort the data
Before grouping, make sure Excel can identify the records and the column to use. A single header row, one record per row, and consistent values in the grouping column make the task easier. Remove completely blank rows inside the list if they split the data into separate blocks, and check that values which should match do not differ because of extra spaces or inconsistent spelling.
- Click a cell in the data range. If Excel does not identify the whole list correctly, select the range yourself, including its headers.
- On the ribbon, choose Data > Sort. In the Sort dialog, check that My data has headers is selected if your first row contains field names.
- Choose the column you want to group on, such as Department or Region, and select the desired sort order. Choose OK.
- Review the result. Each distinct value should now appear in one continuous block. If you need to preserve a meaningful order, add a secondary sort column in the Sort dialog.
Sorting changes the order of worksheet rows, so consider whether that is acceptable before proceeding. If the original order matters, add an index column before sorting: fill it with 1, 2, 3, and so on, then sort by the grouping field. To restore the initial order later, sort by the index. Save a copy first if the workbook is important or shared.
Create collapsible groups manually
Once matching records are adjacent, select the row headers for the detail rows in one block. Row headers are the numbered gray boxes at the left edge of the worksheet. Do not select the column header row or a subtotal row that you want to remain visible. Then choose Data > Outline > Group. If Excel asks whether to group Rows or Columns, choose Rows and confirm.
A bracket and a minus button appear beside the selected rows. Click the minus button to collapse the detail; it changes to a plus button that expands the rows again. The outline level buttons, usually numbered 1, 2, and so on above the row numbers, let you show a broader level of the outline. A level button affects all groups at that level, so use an individual plus or minus control when you only want to open or close one department.
Repeat the process for each block of matching values. If a department has a label row or a subtotal row immediately above its detail, select only the detail rows. With a simple flat list, the first row of each block may itself contain the department value; it remains visible when you group the remaining rows only if you deliberately leave that row unselected. Decide what you want visible while collapsed before choosing the boundaries.
To remove a group, select its rows and choose Data > Outline > Ungroup. To remove the worksheet’s outline structure, use Data > Outline > Ungroup and select Clear Outline, where available in the ribbon menu. Ungrouping does not remove the underlying records. It only removes the outline controls and the hide/show arrangement.
Build automatic groups with Subtotal
When the goal is to see a total for each value and expand its underlying records, Subtotal can create the outline and insert summary rows in one operation. First sort the list by the grouping column. Then choose Data > Outline > Subtotal. In the dialog, set At each change in to the field being grouped, such as Department; choose a function such as Sum or Count; and select the column or columns to summarize. Confirm the dialog to insert subtotal rows and outline levels.
For the sample list, select Department in At each change in, Sum in Use function, and Hours in Add subtotal to. Excel inserts a subtotal after each department’s records and a grand total at the end. The outline controls can show only the grand total, department totals with their detail hidden, or all rows. Check the selected columns carefully: choosing Employee for a sum would not produce the useful total you intended.
Subtotal is different from manual grouping. It inserts worksheet rows containing summary formulas, so the worksheet layout changes. If the source list is updated, the subtotals may need to be removed and created again. The command is designed for a normal range, not an Excel table. If the list is formatted as a table, use Table Design > Convert to Range before applying Subtotal; doing so removes table behavior such as structured references and automatic table expansion, even if much of the formatting remains. For a table you want to keep, consider a PivotTable instead.
Choose between manual groups, Subtotal, and PivotTables
Choose based on what the finished worksheet should do. Manual groups are appropriate when you want to hide and reveal existing rows without inserting summary calculations. Subtotal is useful for a quick outline with totals in a conventional range. A PivotTable is usually preferable for interactive analysis, especially when you want to rearrange categories, change calculations, or keep the source data in a table.
- Manual Group: Keeps the list as it is and adds collapsible row outlines. You sort and create each group yourself; new records do not automatically join the correct outline group.
- Subtotal: Adds subtotal rows and outline levels based on changes in a sorted field. It is convenient for a report, but changes the layout and may need to be rerun after source data changes.
- PivotTable: Summarizes data without inserting subtotal rows into the source list. Choose Insert > PivotTable, then put the category field in Rows and a numeric field in Values. Refresh the PivotTable after the source data changes.
- Sort or Filter: Sort arranges matching values together, while Filter displays selected records and hides others. Neither creates an outline group, but filtering can be faster if the only goal is to view one category at a time.
These approaches are not interchangeable. A PivotTable groups and summarizes data in a separate report layout; it does not add the same row-outline controls to the original records. A filter is temporary viewing control, not a saved hierarchy of collapsible row blocks. If the request is specifically to collapse sections in the source worksheet, use Group or Subtotal.
Keep groups useful as data changes
Excel’s outline is attached to worksheet row positions, not to the meaning of a cell value. If you insert, delete, or move records, inspect the group boundaries afterward. A newly added record may land outside the existing block, even if its category matches. When the list changes often, rebuilding the manual groups or using a PivotTable that can be refreshed is generally more reliable than expecting the outline to update itself.
For more than one level, sort by the outer category first and the inner category second. For example, sort by Region and then Department. Group the department detail blocks first, then group the larger region blocks if you want a hierarchy. Plan which rows should remain visible at each level; an outline can become confusing if its groups overlap in unexpected ways.
When sending a workbook to someone else, explain what the outline levels show and whether the records have been sorted. Collapsed rows can make data appear missing to a reader who does not notice the plus signs. A clear title, visible subtotal labels, and a note that rows are hidden by grouping can prevent that misunderstanding.
Common problems and fixes
- Group is unavailable or the wrong dialog appears: Make sure you selected row headers or the intended range, then choose Group on the Data tab. If Excel asks whether to group rows or columns, select Rows. If the worksheet is protected, protection settings may prevent structural changes; check Review > Unprotect Sheet if you are authorized to do so.
- Matching values are separated: The Group command only groups adjacent rows. Sort by the target column again and check for blanks, spelling variations, or values that look identical but contain extra spaces.
- Some rows disappear after collapsing: That is the purpose of the outline, not deletion. Click the plus control beside the group or choose a more detailed outline level to reveal the records.
- The outline symbols are missing: Check File > Options > Advanced and, under Display options for this worksheet, make sure Show outline symbols if an outline is applied is enabled. The setting is worksheet-specific.
- Subtotal is unavailable: The data may be an Excel table. Convert the table to a range if you want to use Subtotal, or create a PivotTable while keeping the source as a table.
- New records are not included: Manual groups are not value-based rules. Sort and regroup the expanded data, or use a refreshable summary tool when the data changes regularly.
Frequently Asked Questions
Can Excel automatically group rows that have the same cell value?
Not with the ordinary Group command alone. Sort the rows by that field, then group each adjacent block manually. For automatic outline sections with subtotal rows, use Data > Outline > Subtotal on a sorted range.
Can I group rows without sorting the data?
Only if the matching rows are already next to one another. A single outline group cannot collect separated rows while leaving intervening records in place. If preserving the current order is important, use a filter or create a separate summary instead of rearranging the source list.
How do I expand or collapse every group at once?
Use the numbered outline level buttons above the row numbers to show a broad or detailed level across the worksheet. To control just one section, click that section’s plus or minus button.
Does grouping hide or delete the rows?
Grouping hides rows when you collapse an outline, but it does not delete their contents. Expand the group to display them. This differs from deleting rows, which removes the records from the worksheet.
Will groups update when I add more rows?
Not reliably as a rule based on the cell value. The outline applies to row positions and existing group boundaries. After adding records, check the sort order and group ranges; for recurring updates, a PivotTable or another summary approach may be easier to maintain.
Excel for Mac and the web
The instructions above use Excel for Microsoft 365 on Windows. Excel for Mac also provides row outlining and grouping through the Data tab, though ribbon arrangement and available keyboard shortcuts can differ by release. On either desktop platform, use the visible Data-tab commands if a shortcut differs. Excel for the web may expose fewer outline and subtotal controls than the desktop app; if a command is missing, open the workbook in desktop Excel to create or manage the outline.
Related
- Excel Unable to Remove Duplicates? Common Causes and Fixes
- Excel Text to Columns: How to Undo and Restore Data
- Excel Pasting: Stop Tab-Delimited Text Splitting on Paste
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Hide Columns With an Expand Button: Grouping GuideTo hide columns that readers can reopen with a plus button, select the columns, choose Data > Group,…
- How to Add a Picture to a Cover Page in Microsoft WordTo add a picture to a Word cover page, open the first page, choose Insert > Pictures >…
- Outlook Signature: How to Create and Set It Up for EmailTo create a signature message in Outlook, open the signature settings for your version, add your name and…
- Excel File Data: What It Contains and How to Use ItExcel file data is the information stored in a workbook: values, text, dates, formulas, tables, and sometimes charts,…
- How to Remove Data Validation Rules in Google SheetsTo remove data validation in Google Sheets, select the affected cells, open Data > Data validation, then remove…
- Excel Find and Replace in Selected CellsYes. In Excel for Microsoft 365 on Windows, select a multi-cell range, press Ctrl+H, enter the text to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.