To group rows in Excel by a category or rule, first sort the data so matching rows are together, then use Data > Subtotal to create collapsible outline groups. If the rule is not already a column, add a helper column that labels each row first. This guide is for Excel for Microsoft 365 on Windows; keep a copy of the original data before sorting or adding subtotals.
What You’ll Need
- A worksheet with one header row and a consistent column for each type of information.
- A field to group by, such as department or status, or a rule you can label in a helper column.
- Desktop Excel for the Subtotal workflow. Excel for the web may not expose the Subtotal command or all outline controls, so open the file in desktop Excel if needed.
- An unfiltered, unsorted copy of your original data, or a saved backup, in case you need to restore the initial row order.
Step-by-Step
- Check and prepare the data. Make sure the list has one header row, no completely blank rows inside the list, and no merged cells in the data area. Each row should represent one record, and the same kind of information should be in the same column. For example, put each order’s region in one column and its amount in another. If your range is an Excel table, select a cell in it, open Table Design, and choose Convert to Range. Confirm the prompt. The Subtotal command works on a regular range, not an Excel table.
- Decide what the grouping rule means. If the rule is simply “one group for each department,” use the department column as it is. If you want groups such as “High value” and “Standard,” create a helper column with those labels. A helper column makes the rule visible and gives Excel a field it can sort and group by. Choose clear labels that will be easy to recognize in the Subtotal dialog.
- Create a helper column when necessary. Add a header such as Value band to an empty column. With amounts in C2:C100 and a header in C1, enter this in D2 to classify amounts of 1,000 or more as High and smaller amounts as Standard:
=IF(C2>=1000,"High","Standard"). For example, an amount of 1,250 returns High, while 640 returns Standard. Fill the formula down through the records, using the fill handle or copying it into the remaining cells. Check a few results against the rule before continuing. If a criterion is already recorded in a column, you do not need a formula. - Sort by the grouping field. Click a cell in the list, then choose Data > Sort. In the Sort dialog, select the field you will group by under Sort by, and select the desired order. For text labels, A to Z puts matching labels together; for dates or numbers, choose the order that suits the report. If Excel asks whether to expand the selection, choose Expand the selection so each row stays intact. Sorting only the criterion column would misalign the data. If the original order matters, add a temporary sequence-number column before sorting so you can restore it later.
- Open the Subtotal command. Select a cell in the sorted list and choose Data > Outline > Subtotal. If the command is unavailable, check that the data is not still an Excel table and that the sheet is not protected. In the dialog, set At each change in to the field you sorted by, such as Value band or Department. Excel uses changes in that field to decide where one group ends and the next begins, so matching values must be adjacent.
- Choose the subtotal calculation and column. Under Use function, select an appropriate calculation, such as Sum, Count, or Average. Under Add subtotal to, tick the numeric column to summarize, such as Amount. If you only need an outline and do not want a meaningful numeric total, you can use Count on a column that has an entry for every record; Excel will add count rows as well as the outline. Choose Replace current subtotals if you are rebuilding an existing outline. Leave Summary below data selected for the usual layout, or clear it if you need the summary row above each group.
- Create the groups. Click OK. Excel inserts a subtotal row at each change in the selected field and adds outline controls along the left edge of the worksheet. The outline buttons usually show levels such as 1, 2, and 3. Click 2 to hide the individual records and show group summaries, or click 3 to display the detail rows. Click the plus or minus control beside an individual group to expand or collapse only that section.
- Review the result before sharing it. Check that every label has the expected records and that the subtotal figures make sense. Click the outline levels to confirm that both detail and summary views are useful. Remember that these are collapsible worksheet rows, not separate worksheets or a permanent data filter. To see only rows matching a condition, use Data > Filter instead; filtering hides nonmatching rows but does not create outline groups.
- Remove or refresh the outline when data changes. If you add records, change labels, or change the criterion, sort the full range again and rebuild the subtotals so the groups reflect the updated data. To remove the inserted subtotal rows and outline, select a cell in the list and choose Data > Subtotal, then click Remove All. This removes the subtotal structure; it does not delete your underlying records. Keep your backup if you need to preserve an earlier layout.
For groups without subtotal rows: Sort by the criterion, select the consecutive detail rows for one group by their row numbers, and choose Data > Group > Group. In the dialog, choose Rows and confirm. Repeat for each group. This creates a collapsible outline without inserting summary calculations, but you must select each group yourself. If you add or rearrange records later, check and rebuild these manual groups.
Common Mistakes
- Skipping the sort: Subtotal groups are made at each change in the chosen field. If values are interleaved, Excel may create several groups with the same label instead of one group per label.
- Sorting only one column: This separates values from their original records. Always expand the selection when sorting a single cell or column inside a list.
- Using the wrong field in the dialog: The At each change in setting must match the field used to organize the rows. Choosing a different field can produce unexpected group boundaries.
- Leaving data in a table: Subtotal is not available for an Excel table. Convert the table to a range before using that command.
- Expecting groups to update automatically: The outline reflects the data and sort order at the time it was created. New or edited records may need a new sort and subtotal operation.
Tips
- Use a helper column for a custom criterion instead of manually selecting scattered rows. Labels such as Overdue and On time make both sorting and reviewing easier.
- For multiple rules, make the helper label express the group you actually need. For instance, combine region and status into a distinct category if each region should have separate status groups. Sort by that category before creating subtotals.
- Keep the original order in a temporary column if users expect records to return to that sequence. Sort by the sequence column after removing the outline.
- Use the outline level buttons for a quick overview, and the plus and minus controls when you need to inspect a specific group. The detail rows remain in the worksheet while collapsed.
- On desktop Excel for Mac, look on the Data tab for Subtotal and outline controls; ribbon placement can differ from Windows. For the Subtotal workflow, use desktop Excel rather than relying on Excel for the web.
Troubleshooting
Subtotal is greyed out or missing: Confirm that the data is a regular range rather than a table, that the worksheet is not protected, and that you are using desktop Excel. Convert a table with Table Design > Convert to Range, then try Data > Subtotal again. If you are in Excel for the web, open the workbook in the desktop app.
The same category appears in several groups: Sort the entire list by the exact field selected in At each change in, then remove the old subtotals and apply them again. Check for labels that look alike but differ because of trailing spaces, spelling, or different number and text formats. Correct inconsistent values before sorting.
The group levels or plus/minus controls are not visible: Check whether outline symbols are hidden in Excel’s settings, and look along the left edge of the worksheet beside the row numbers. If the outline was removed, recreate it with Subtotal or group the row selections manually. Also check whether the relevant rows are hidden by a filter; clear or adjust the filter before diagnosing the outline.
The subtotal is wrong: Confirm that the correct numeric column is selected under Add subtotal to and that the chosen function suits the data. Sum is appropriate for amounts, while Count counts entries. Check that numbers are stored as numbers rather than text, and that the source range includes all records but not unrelated rows. If you used a helper formula, verify its criteria and fill it through every data row before rebuilding the groups.
Frequently Asked Questions
Can Excel group rows automatically based on a cell value?
Yes. Sort by the value, then use Data > Subtotal and set At each change in to that field. Excel creates an outline group at each change. If your rule is not already a field, use a helper column to assign each row a label first.
Can I group rows that match a condition, such as amounts over 1,000?
Yes. Create a helper column that labels each record according to the condition, sort by that label, and create subtotals or manually group the adjacent rows. This is useful when the original values are not themselves the group names.
Can I group rows without adding subtotal calculations?
Yes. Sort the list so each group is contiguous, select the rows for a group, and choose Data > Group > Group, then choose Rows. Repeat for the other groups. This is a manual outline and does not add subtotal rows.
Can I create nested groups for two criteria?
Yes, but apply one level at a time. Sort by the higher-level field and then by the second field, and use Subtotal for the inner field first. Apply Subtotal again for the outer field, leaving Replace current subtotals cleared. Review the outline levels to make sure the hierarchy is useful.
Will the groups change when I edit the source data?
Not reliably. A changed label or newly added row can break the sort order or leave it outside the existing outline. Sort the complete list and recreate the subtotals after changes that affect group membership.
How do I ungroup everything?
For groups created with Subtotal, choose Data > Subtotal and click Remove All. For manually grouped rows, select the grouped rows and choose Data > Ungroup. Removing an outline does not remove the original records.
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
- Excel Max Value in a Column: Find the Largest NumberIf your values are in B2:B100, enter =MAX(B2:B100) in an empty cell; Excel returns the largest numeric value…
- How to Compare Documents in Microsoft WordTo compare two versions in Microsoft Word, open Word and choose Review > Compare > Compare on Windows.…
- Create Labels in Word 365: A Step-by-Step GuideTo create labels in Word 365, open the Mailings tab, choose Start Mail Merge > Labels, select the…
- Excel Data Table Sensitivity Analysis ExplainedExcel data table sensitivity analysis shows how changing one or two input values affects a formula’s result. In…
- Excel: Sum Values Based on Other ValuesYes. In Excel, use SUMIF to add values that match one condition, or SUMIFS to add values that…
- How to Double Space in Word for the WebTo double-space text in Word for the web, select the text, open Home > Line and Paragraph Spacing,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.