Yes. To group records by a column in Excel, sort the rows by that column and use Data > Subtotal to create collapsible groups with summaries, or insert a PivotTable to group categories and aggregate their values. If you only want to hide a selected block of rows, use Data > Group; that creates a manual outline and does not automatically group rows according to their cell values.
What “group rows by a column” can mean
The phrase describes a few different tasks, and choosing the right one matters. You might want to collapse all records for each department, show a sales total beneath each department, or build a compact report with one line per department. Excel handles those goals with different tools.
- Subtotal outlines keep the detail rows in place and add expandable levels, often with a subtotal for each category.
- PivotTables summarize records into a report, such as total sales by department, without adding outline controls to the original list.
- Manual grouping collapses rows you select, regardless of what their cells contain. It is useful for organizing a worksheet, but it is not a rule that follows category values.
Excel does not have a standard command that automatically creates manual row groups whenever repeated values appear in an arbitrary column. For value-based groups, use Subtotal or a PivotTable. The distinction also explains why sorting is usually the first step when creating a subtotal outline.
Prepare the data before grouping
Use a simple, consistent list. Each column should have one header, each row should represent one record, and the category column should use consistent labels. For example, use Sales in every row rather than a mix of Sales, sales, and Sales Department. Small differences create separate categories in summaries.
Before sorting or summarizing, check the following:
- There is a single header row, with no merged cells in the data area.
- There are no completely blank rows splitting the list into separate blocks.
- Each row contains all the fields for one record, such as date, department, product, and amount.
- Values that look alike are actually alike. Extra spaces, inconsistent spelling, and blank category cells can produce unexpected groups.
- If the data is an Excel Table, remember that the Data > Subtotal command is unavailable for tables. You can use a PivotTable directly, or convert the table to a range before using Subtotal.
Keep a copy of the unsorted data if the original order is meaningful. Sorting changes the row order, so it can affect references or workflows that depend on records staying in their current sequence.
Create collapsible category groups with Subtotal
Subtotal is the closest built-in option when you want rows grouped by a field and a subtotal for each group. It sorts and inserts summary rows into the list, then adds outline controls so you can show detail or collapse it. On Excel for Microsoft 365 for Windows, use this process:
- Select a cell in the data list. If the list is well-formed, Excel can identify its data range.
- Sort the full list by the category column. Choose Data > Sort, select the category field, and choose the order you want. Make sure the full records move together; do not sort only the category column by itself.
- Choose Data > Subtotal. In the dialog, set At each change in to the category field, such as Department.
- Under Use function, choose the summary you need, such as Sum for numeric sales amounts or Count to count records.
- Under Add subtotal to, select the numeric field or fields to summarize. Leave Summary below data selected if you want each subtotal after its group.
- Select OK. Use the outline level buttons or the plus and minus controls beside the row numbers to expand or collapse the details.
For example, imagine a list with Department, Employee, and Sales columns. Sort by Department, then set the subtotal to change at each Department and sum Sales. The result keeps every employee row, adds a sales total after each department, and provides controls to hide or reveal employee details.
Subtotal inserts rows into the worksheet, so it is not just a display setting. If you need the original list unchanged, work on a copy or choose a PivotTable instead. To remove the inserted subtotal rows, select a cell in the list and choose Data > Subtotal > Remove All.
Use a PivotTable for a category summary
A PivotTable is usually the better choice when you want a separate report rather than collapsible groups inside the source list. It can group records by a category and calculate totals, counts, averages, and other summaries, while leaving the source data in its original arrangement.
- Select a cell in the source list and choose Insert > PivotTable.
- Confirm the table or range and choose where to place the PivotTable, commonly New Worksheet.
- In the PivotTable Fields pane, drag the category field, such as Department, to Rows.
- Drag a numeric field, such as Sales, to Values. Excel will usually sum numeric values; check the field setting if you need a count or another calculation.
- Optionally drag another field to Columns or Filters to compare categories or narrow the report.
A PivotTable with Department in Rows and Sales in Values displays a summary by department. You can expand or collapse lower-level fields if you add them beneath Department, but the PivotTable is not the same as grouping the original worksheet rows. When source records change, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. New records may require updating the source range unless it is based on an expanding Excel Table.
Make manual row groups
Use manual grouping when you already know which consecutive rows you want to hide and reveal together. It does not inspect a column or identify matching labels. First arrange the rows so the desired records are next to each other, then select their row numbers and choose Data > Group in the Outline area. If Excel asks whether to group Rows or Columns, choose Rows.
For example, you could select rows 5 through 12 and group them to hide a block of detail beneath a heading. The outline controls appear beside the row numbers. Select the minus control to collapse the block and the plus control to expand it. To undo the grouping, select those rows and choose Data > Ungroup. If you group nonadjacent category records without sorting, Excel cannot combine them into one manual outline group.
Choose the right method for your goal
- Use Subtotal when you need category sections in the original list, visible detail, and inserted totals or counts for each section.
- Use a PivotTable when you need a compact, flexible report, want to compare several fields, or prefer not to insert rows into the source data.
- Use manual Group when the rows to hide are already identified and you want a simple outline without category-based calculations.
- Use sorting alone when the goal is just to place matching categories together and you do not need summaries or collapse controls.
These methods can be combined. For instance, sort a list by region to make it easier to scan, then build a PivotTable for totals by region and product. But avoid adding Subtotal rows to a list that will be used as clean source data for later analysis: inserted summary rows can be mistaken for ordinary records by other tools or processes.
Common problems and how to fix them
- Subtotal is unavailable: The data may be formatted as an Excel Table. Use a PivotTable, or select a cell in the table and choose Table Design > Convert to Range before using Subtotal. Converting removes table behavior, so consider making a copy first.
- One category appears as several groups: Check for spelling differences, leading or trailing spaces, and inconsistent blank values. Correct the source labels, sort again, and recreate the subtotals.
- Rows from different records get separated: Sort the entire data range or table, not just one column. Sorting a single column can disconnect values from the rest of their records.
- The outline controls are missing: Check whether outline symbols are displayed in Excel’s settings, and confirm that a subtotal or manual group was actually created. The outline level buttons appear beside the worksheet row numbers when an outline exists.
- Totals do not match expectations: Confirm that the amount field contains numbers rather than number-like text, and check whether the subtotal function is Sum, Count, or another calculation. Also check whether filters or blank category cells are affecting what you see.
Excel for Mac and Excel for the web
The instructions above use Excel for Microsoft 365 on Windows. Excel for Mac has sorting, PivotTables, and row outlining, though ribbon layout and shortcut behavior can differ by release. Look for Subtotal and Group on the Data tab; if a command is not visible, check the ribbon’s available commands or the application’s search box. Keyboard shortcuts can also differ on Mac, so the menu path is the more dependable instruction across systems.
Excel for the web supports sorting and PivotTables, but its available outline and Subtotal features may differ from the Windows desktop application. If you need the Subtotal dialog or to edit an existing outline and cannot find the command, open the workbook in the desktop app. A PivotTable is generally the more portable option for producing a category summary, though its field layout and refresh controls may look different in the browser.
Frequently Asked Questions
Can Excel automatically group rows when a column has the same value?
Not as automatic manual row groups. Sort by the column and use Data > Subtotal for an outline organized around each change in value, or create a PivotTable to summarize the categories.
Do I have to sort before using Subtotal?
Yes, sort by the field you select in At each change in. Subtotal creates a new group when that field’s value changes as Excel moves down the list, so unsorted repeated values may be split into separate groups.
Can I group rows without adding totals?
Yes. Use manual Data > Group to collapse selected consecutive rows, or use an outline created by another process. For value-based sections without numeric totals, manual grouping requires you to select the row blocks yourself.
What is the difference between Group and Subtotal?
Group hides or reveals selected rows but does not classify them by cell values. Subtotal organizes a sorted list at changes in a chosen field, inserts summary rows, and creates outline controls.
Will a PivotTable change my original rows?
No. A PivotTable creates a separate summary report and leaves the source records in place. Refresh it after changing the source data so the report reflects those updates.
Related
- Excel Data Table Cycling Through Results? Fixes Explained
- Excel Data Table Greyed Out? Causes, Checks, and Fixes
- Excel Solver Add-In: How It Works and How to Use It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Stacked Line Chart ExplainedAn Excel stacked line chart plots each series as a running total of the series before it, rather…
- Excel Pivot Table: How to Change the Data SourceTo change an Excel PivotTable’s source in Microsoft 365 for Windows, click inside the PivotTable, select PivotTable Analyze…
- Excel Pie Chart: Change the Color of a SliceTo change one slice in an Excel pie chart, click the chart, then click the slice again so…
- Bubble Chart in Google Sheets: Setup and TipsYes—Google Sheets can make bubble charts in a web browser. Arrange each item in a row with five…
- Excel Vertical Line Chart: How to Create and Format OneYes—you can add a vertical reference line to an Excel chart, most reliably by adding a two-point XY…
- Excel Bar Graph: Sort Values Largest to SmallestTo sort an Excel bar graph from largest to smallest, sort the chart’s source data by its value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.