What grouping does and when to use it

Grouping in Excel collapses rows or columns so you can hide detail and show only the summary level you need. When you group records, Excel adds outline buttons (small plus and minus signs) on the left side of your spreadsheet. Click the minus sign to hide rows; click the plus sign to show them again. This is different from sorting or filtering — the data stays in place, and you control what level of detail appears on screen.

Grouping works best when your data has a natural hierarchy: months within a year, individual transactions within a department, line items within an invoice. If your spreadsheet is a flat list with no structure, grouping will not help. If you need to see only certain records (like sales over $1,000), filtering is faster than grouping.

Key Takeaways

  • Grouping requires your data to be sorted by the column you want to group on — Excel will not group unsorted data.
  • You select the rows you want to group, then use the Data menu to create the outline; Excel adds collapse buttons automatically.
  • You can create multiple levels of grouping (for example, weeks within months within quarters) by grouping in the right order.
  • Grouping is temporary and tied to that spreadsheet file — it does not change the underlying data and disappears if you remove the outline.

Sort your data before grouping

Excel can only group rows that are adjacent and sorted by the column you want to group on. If you have a list of sales transactions with a date column, sort by date first. If you have employee records with a department column, sort by department. Grouping will not work on unsorted data.

To sort, click any cell in the column you want to sort by, then go to the Data menu and click Sort A to Z (or Sort Z to A for reverse order). Excel will sort the entire row so your related data stays together. Once the data is sorted, you are ready to group.

Select and group rows manually

The most straightforward way to group is to select the rows you want to collapse together. For example, if you have January transactions in rows 5 through 12, click on row 5's row number, then hold Shift and click on row 12's row number. All rows from 5 to 12 will highlight in blue.

With those rows selected, go to the Data menu and click Group. Excel will add outline buttons on the left margin. A minus sign appears next to the selected rows; click it to hide them. A plus sign appears at the level above; click it to show them again. Repeat this process for each group of rows you want to collapse.

Create multiple levels of grouping

If your data has more than one layer of hierarchy, you can create nested groups. For example, you might group individual transactions (rows 5–8) as "Week 1", then group all of Week 1 through Week 4 (rows 5–20) as "January". Excel will show outline buttons at each level: level 1 shows only the month, level 2 shows weeks within the month, level 3 shows individual transactions.

To build nested groups, group the smallest units first (transactions), then group the larger units (weeks), then group the largest units (months). Excel stacks the outline buttons so you can collapse or expand at any level. The numbered buttons at the top left (1, 2, 3) let you jump to a specific level across the entire spreadsheet at once.

Use AutoOutline for data with subtotals

If you have already used the Subtotals feature in Excel (Data menu > Subtotals), the outline structure is already built in. Go to Data > Group > Auto Outline, and Excel will create grouping buttons automatically based on your subtotal rows. This is faster than grouping manually if you have many groups.

AutoOutline only works if your data has subtotal rows inserted by Excel's Subtotals tool. If you have manually typed subtotal labels, AutoOutline will not recognize them.

Collapse and expand groups while working

Once grouping is in place, use the outline buttons to show or hide detail. The minus sign hides the rows in that group; the plus sign shows them. The numbered buttons (1, 2, 3, etc.) at the top left let you jump to a specific outline level across the entire sheet. Click 1 to see only the top-level groups; click 2 to see groups and their first level of detail; click 3 to see everything.

This is useful when you are presenting data to someone who does not need to see every transaction, or when you are working on a large spreadsheet and want to focus on one section at a time. The data is still there — you are just hiding it temporarily.

Remove grouping when you no longer need it

Grouping is not permanent. If you want to remove the outline buttons and return to a normal spreadsheet view, go to Data > Group > Ungroup. Choose Remove All to delete all grouping at once. The data itself does not change — only the outline structure disappears.

You can also remove individual groups by selecting the grouped rows and clicking Data > Group > Ungroup again. This removes the outline buttons for just that selection while leaving other groups intact.

Frequently Asked Questions

Can I group columns instead of rows?

Yes. Select the columns you want to group (by clicking the column letter while holding Shift), then go to Data > Group. Excel will add outline buttons above the columns instead of to the left of the rows. The process is identical.

What if my data is not sorted?

Grouping will not work on unsorted data because Excel needs adjacent rows to collapse together. Sort the column you want to group on first (Data > Sort), then select and group the rows.

Can I group by a column without manually selecting rows?

Not directly. You must either select rows manually and use Data > Group, or use Data > Subtotals first (which creates grouping automatically), then use Data > Group > Auto Outline. There is no "group by column name" button.

Will grouping affect my formulas or calculations?

No. Grouping only hides rows or columns — it does not delete them or change any data. Formulas that reference hidden rows will still include those rows in their calculations. If you need formulas to ignore hidden rows, you would use a different function like SUBTOTAL instead.

Can I save a spreadsheet with grouping in place?

Yes. When you save the file, the grouping outline is saved with it. When you open the file again, the grouping buttons will still be there in the same state you left them (expanded or collapsed).