What grouping does in Excel

Grouping in Excel collapses rows or columns so you can hide detail and see only the summary. When you group data, Excel adds outline buttons on the left side of your sheet — you click them to expand or collapse sections. This is useful when you have a large table with repeating categories: sales by month, expenses by department, inventory by location.

Grouping is different from sorting or filtering. Sorting rearranges your rows. Filtering hides rows that don't match your criteria. Grouping organizes rows into collapsible sections that stay in place — you control what level of detail you see without changing the data itself.

The most common use is pairing grouping with subtotals. Excel can automatically insert subtotal rows (like sum, average, or count) at the end of each group, then let you collapse to see only those subtotals. This turns a 500-row table into a 20-row summary you can click to expand.

Key Takeaways

  • Grouping works best when your data is already sorted by the column you want to group on — Excel groups consecutive rows with the same value.
  • The Data menu's Subtotals feature automatically groups rows and inserts summary rows (sum, average, count, etc.) in one step.
  • You can manually group rows by selecting them and using Data > Group, which gives you more control but requires you to insert subtotal rows yourself.
  • Outline buttons (numbered 1, 2, 3) appear on the left after grouping — click them to show only that level of detail across the entire sheet.
  • Grouping is temporary and doesn't change your original data; you can remove it anytime with Data > Ungroup.

Using Subtotals to group and summarize at once

The fastest way to group data is to let Excel do both grouping and subtotals together. First, sort your data by the column you want to group on. For example, if you have sales data with columns for Date, Region, Product, and Amount, sort by Region so all the Northeast sales are together, then all the Southeast, and so on.

Select your entire data range including headers. Go to the Data menu and click Subtotals. A dialog box opens. In the "Group by" dropdown, choose the column you sorted on (Region, in this example). In the "Use function" dropdown, choose what calculation you want: Sum, Average, Count, Max, Min, or others. In the "Add subtotal to" section, check the columns you want summarized (Amount, for instance). Click OK.

Excel inserts a subtotal row after each group and adds outline buttons on the left. Click the number buttons (1, 2, 3) to collapse to different levels: button 1 shows only the grand total, button 2 shows each group's subtotal, button 3 shows all detail rows. The minus signs next to each group let you collapse just that group.

Manually grouping rows when subtotals don't fit your needs

Sometimes you don't want automatic subtotal rows, or you want to group by something more complex than a single column. In those cases, manually select and group the rows you want to collapse together.

Select the rows you want to group (click the row number on the left, then hold Ctrl and click other row numbers to select non-consecutive rows, or click and drag for consecutive rows). Go to Data > Group. Excel adds outline buttons but does not insert subtotal rows. You now have a collapsible section — click the minus sign to hide those rows, click the plus sign to show them again.

You can create multiple levels of grouping. For example, group rows 5–10 as one section, rows 11–20 as another, then select both groups and group them together as a larger section. The outline buttons will show 1, 2, 3 levels, letting you collapse to see only the largest groups or expand to see everything.

When grouping works and when it doesn't

Grouping works best on data that is already sorted and organized. If your data is scattered — Region A in rows 5 and 23, Region B in rows 8 and 40 — grouping will not consolidate them. Sort first, then group.

Grouping also assumes your data is in a straightforward table format: columns are fields, rows are records. If your data has merged cells, blank rows in the middle, or irregular structure, the Subtotals feature may not work correctly. Manual grouping is more forgiving but requires you to set it up yourself.

If you need to group by multiple columns at once (for example, Region and then Product within each Region), use Subtotals multiple times. First group by Region and insert subtotals. Then, with the grouped data still selected, go to Subtotals again, choose Product in the "Group by" dropdown, and make sure "Replace existing subtotals" is unchecked. Excel will add a second level of grouping.

Removing grouping and getting back to your original data

Grouping does not change your data — it only hides and shows rows. To remove grouping entirely, select your data range and go to Data > Ungroup. If you used Subtotals, a dialog asks whether you want to remove only the grouping or also delete the subtotal rows. Choose accordingly.

If you manually grouped rows and want to remove only one group, click the minus sign next to that group to collapse it, then select the collapsed group and go to Data > Ungroup. The rows expand again and the grouping is gone.

Grouping columns instead of rows

You can also group columns to hide and show them. This is less common but useful if you have many similar columns — for example, sales data for 12 months, and you want to collapse months 1–3 into one collapsible section, months 4–6 into another, and so on.

Select the columns you want to group (click the column letter at the top, then hold Ctrl and click other letters, or click and drag). Go to Data > Group. Outline buttons appear above the columns. Click them to collapse and expand column groups the same way you would with rows.

Frequently Asked Questions

Do I have to sort my data before grouping?

Yes, if you use the Subtotals feature — it groups consecutive rows with the same value in the "Group by" column. If your data is not sorted, the subtotals will be wrong. Manual grouping does not require sorting because you select exactly which rows to group.

Can I group by more than one column?

Yes. Use Subtotals once for the first column, then use it again for the second column without replacing the first set of subtotals. Excel creates nested groups with multiple outline levels. You can then collapse to see only the top-level groups or expand to see all detail.

What if I want subtotals but not the grouping buttons?

You can insert subtotal rows manually without using the Subtotals feature. Add a blank row after each group, type a label like "Region Total", and use a SUM formula to add up the rows above. This gives you subtotals without outline buttons, but it takes longer and requires you to update formulas if data changes.

Does grouping work with pivot tables?

Grouping and pivot tables are separate tools. Pivot tables already organize and summarize data by groups, so you do not need to group them. If you want collapsible sections in a pivot table, that is a different feature called "grouping" within the pivot table itself, accessed through the Data menu while the pivot table is selected.