What grouping does and when you need it

Grouping in a spreadsheet lets you collapse and expand sections of rows or columns to hide detail and show only the summary you want to see. When you group, you create a collapsible outline — click a button and ten rows of individual sales disappear, leaving only the monthly total visible. You can then click again to see the detail underneath.

Grouping is different from sorting or filtering. Sorting rearranges your data. Filtering hides rows that don't match a condition you set. Grouping organizes rows or columns into nested levels so you can toggle between seeing everything and seeing only the summaries. It's most useful when you have data that naturally breaks into sections — months within a year, departments within a company, or individual transactions within a category.

You need grouping when you're looking at a spreadsheet with hundreds of rows and you want to focus on one level of detail at a time without deleting anything or creating a separate sheet. It's also useful when you're presenting data to someone else and want to let them drill down into detail only where they're curious.

Key Takeaways

  • Grouping works best when your data is already sorted by the category you want to group — if sales are sorted by month, you can group by month.
  • In Excel and Google Sheets, you select the rows or columns you want to group, then use the Data menu to create the group.
  • Grouped data shows outline buttons (usually numbered 1, 2, 3) on the left margin that let you collapse or expand each level.
  • Grouping does not change your data — it only hides and shows rows, so you can undo it or remove groups at any time.

How to group rows in Excel

Start by sorting your data by the column you want to group. If you want to group by month, sort by the month column first. If your data is already in the order you want, you're ready to group.

Select all the rows you want to include in the group. Click the row number of the first row, then hold Shift and click the row number of the last row. The entire rows will highlight in blue. Then go to the Data menu at the top and click Group. A dialog box will appear asking whether you want to group by rows or columns — choose Rows.

Excel will add outline buttons to the left side of your spreadsheet. You'll see numbers (usually 1, 2, and 3) and minus signs. Click the number 1 to see only the grand total. Click 2 to see each group's subtotal. Click 3 to see all detail. The minus signs let you collapse individual groups.

If you have nested groups — for example, months within quarters within a year — Excel will create multiple outline levels automatically if your data is structured that way. Each level gets its own button.

How to group rows in Google Sheets

Google Sheets grouping works similarly but the menu path is slightly different. First, sort your data by the column you want to group, just as in Excel.

Select the rows you want to group by clicking the row number of the first row, then holding Shift and clicking the row number of the last row. Go to the Data menu and click Create a filter if you haven't already (grouping works best with filters enabled, though it's not required). Then go back to Data and click Group rows.

A small menu will appear asking which rows to group. Select the range and click Group. Google Sheets will add outline buttons on the left side. Click the minus sign next to a group to collapse it. Click the plus sign to expand it again. If you have multiple levels of grouping, you'll see numbered buttons (1, 2, 3) that let you collapse or expand all groups at once.

Grouping columns instead of rows

You can also group columns the same way you group rows. This is useful when you have many months of data across the top and you want to collapse them into quarters, or when you have many product categories and want to show only the totals.

Select the columns you want to group by clicking the column letter of the first column, then holding Shift and clicking the column letter of the last column. In Excel, go to Data > Group and choose Columns. In Google Sheets, go to Data > Group columns. The outline buttons will appear above your columns instead of to the left of your rows.

Column grouping is less common than row grouping, but it's helpful when you're comparing data across time periods and want to hide the detail columns to see only the summary.

Removing groups and ungrouping data

Grouping is not permanent. If you want to remove groups and go back to a flat spreadsheet, select any cell in the grouped range. In Excel, go to Data > Ungroup and choose whether to ungroup rows or columns. In Google Sheets, go to Data > Ungroup rows or Ungroup columns. All outline buttons will disappear and your data will display normally again.

You can also remove a single group without removing all groups. In Excel, right-click on the outline button for that group and choose Ungroup. In Google Sheets, click the minus sign next to the group you want to remove, and a menu will appear with the option to ungroup.

Common mistakes when grouping

The most common mistake is trying to group data that isn't sorted. If your data is in random order, grouping won't work the way you expect. Always sort by the column you want to group first.

Another mistake is selecting too many or too few rows. If you select only part of a group, the outline buttons won't work correctly. Make sure you select complete sections — all rows for January, all rows for February, and so on.

A third mistake is forgetting that grouping hides rows, not deletes them. If you collapse a group and then copy and paste the visible cells, the hidden rows won't be included in the paste. This is usually what you want, but it can be surprising if you're not expecting it.

When grouping isn't the right tool

Grouping works well for data with clear hierarchies — months, departments, product categories. It doesn't work well if your categories are scattered throughout the sheet or if you need to group by multiple different columns at once.

If you need to see different groupings of the same data — for example, both by month and by region — create separate sheets instead of trying to group the same data multiple ways. Grouping is meant to show one hierarchy at a time.

If your data changes frequently and you need to regroup often, consider using a pivot table instead. A pivot table automatically groups and summarizes data, and you can change the grouping without manually selecting rows each time.

Frequently Asked Questions

Can I group data that has blank rows in the middle?

No. Blank rows break the grouping. Remove or fill blank rows before grouping. If you have intentional spacing in your data, consider using a different organization method like a pivot table or a helper column to mark sections.

What happens to my groups if I add new data below the grouped range?

New rows added below the grouped range won't be included in the group. You'll need to regroup to include the new data. Select the expanded range and group again.

Can I group by a column that has text instead of numbers?

Yes. Grouping works with any data type — text, numbers, dates. The important thing is that the data is sorted by the column you want to group, not what type of data it contains.

Does grouping work on filtered data?

Yes, but be careful. If you group filtered data and then remove the filter, the groups may not look the way you expect. It's safer to remove filters, group, and then filter again if needed.

Can I print a spreadsheet showing only the grouped summaries, not the detail?

Yes. Collapse the groups to show only the level you want to print, then print normally. Only the visible rows will print. The hidden rows won't appear on the page.