What grouping does and when you need it
Grouping in Excel lets you collapse and expand sections of rows or columns with a single click, hiding the detail work while keeping the summary visible. This is useful when you have a spreadsheet with subtotals, nested categories, or supporting data you want to tuck away temporarily — like a budget broken into departments, a sales report organized by region, or a project timeline with task breakdowns underneath milestones.
Grouping is not the same as filtering or sorting. It does not remove data or rearrange it. Instead, it adds small collapse buttons (minus signs) next to your rows or columns so you can toggle the detail on and off. The data stays in place; you just control what you see.
Excel offers two ways to group: automatic grouping (which works if your data is already structured with subtotals) and manual grouping (which you do by selecting the rows or columns you want to hide together). Most people use manual grouping because it works with any layout.
Key Takeaways
- Manual grouping works on any selection of rows or columns — select what you want to hide, go to Data menu, and choose Group.
- Grouped rows or columns show collapse buttons (minus signs) on the left or top edge that toggle visibility with one click.
- You can create multiple levels of grouping so that clicking one button hides a section, and clicking another hides even larger sections.
- Automatic grouping through the Subtotals feature works only if your data is sorted by the category you want to group by.
Manual grouping: the step-by-step path
Start by selecting the rows you want to hide together. Click the row number of the first row in your group, then hold Shift and click the row number of the last row. The entire range will highlight in blue. If you are grouping non-consecutive rows, hold Ctrl and click each row number instead.
Once your rows are selected, go to the Data menu at the top. Click it, then find and click Group. A small dialog box will appear asking whether you want to group by rows or columns — since you selected rows, "Rows" will already be chosen. Click OK.
Excel will now add a minus sign (collapse button) to the left of your selected rows, usually in a gray box with a number. Click that minus sign and the rows disappear. Click the plus sign that appears in their place and they come back. The row numbers stay visible so you know which rows are hidden.
You can group multiple separate sections in the same spreadsheet. Each group gets its own collapse button. This is helpful when you have several categories of detail you want to hide independently.
Creating nested groups (groups within groups)
If you have data with multiple levels of detail — for example, a budget with departments, and within each department, individual line items — you can create nested groups so that one click hides all line items, and another click hides entire departments.
Start by grouping the smallest detail level first. Select all the rows that make up the first detail section (say, all the line items under "Marketing"), group them, then do the same for the next section. Once all the detail rows are grouped, select all the rows that make up each larger category (all the Marketing rows plus all the Sales rows) and group those too.
Excel will now show numbered buttons on the left edge — usually 1, 2, and 3. Click button 1 to show only the largest categories. Click button 2 to show categories and their subtotals. Click button 3 to show everything. This three-level structure is the most common, but you can create more levels if your data is deeply nested.
Grouping columns instead of rows
The same process works for columns. Select the columns you want to group by clicking the column letter of the first column, then Shift-clicking the last column letter. Go to Data > Group, and Excel will add collapse buttons across the top of your spreadsheet instead of the left side.
Column grouping is less common than row grouping, but it is useful when you have many months of data (January through December) and want to hide them to show only quarterly summaries, or when you have detailed product specifications you want to tuck away while viewing sales figures.
Automatic grouping with the Subtotals feature
If your data is already sorted by a category (like all Marketing expenses together, all Sales expenses together), Excel can create groups automatically using the Subtotals feature. This is faster than manual grouping if your data is already organized this way.
First, select all your data including headers. Go to Data menu and click Subtotals. A dialog box opens asking three things: which column to group by (choose the category column), which function to use (Sum, Average, Count, etc.), and which columns to calculate (usually the number columns). Click OK and Excel creates groups and subtotal rows automatically.
The Subtotals feature adds both the grouping buttons and summary rows showing totals for each group. If you only want the grouping buttons without the summary rows, manual grouping is the better choice.
Removing groups and ungrouping data
To remove grouping from selected rows or columns, select them again, go to Data menu, and click Ungroup. The collapse buttons disappear and all rows or columns become visible again. The data itself does not change — ungrouping only removes the hiding feature.
If you have nested groups and want to remove only one level, select just the rows or columns in that level before ungrouping. If you want to remove all grouping at once, select all data and ungroup.
Common mistakes and how to avoid them
The most common mistake is selecting non-consecutive rows and forgetting to hold Ctrl while clicking. If you click row 5, then click row 10 without holding Ctrl, Excel will select only row 10, not both. Always hold Ctrl when selecting rows that are not next to each other.
Another mistake is grouping rows that are not in the order you want them hidden. Grouping works on the rows you select in their current position — it does not rearrange them. If you want to group by category, sort your data by that category first, then group.
If you create nested groups and the buttons do not appear as you expected, check that you grouped from smallest detail to largest. Excel builds the nesting structure based on the order you create groups, so grouping the detail rows first, then the category rows, gives you the clearest button structure.
Frequently Asked Questions
Can I group rows and columns at the same time?
No, you group rows or columns separately. Select rows and group them, then select columns and group those. Both sets of grouping buttons will appear on the same spreadsheet — the row buttons on the left and the column buttons on top.
What happens to grouped data when I save the file?
The grouping structure saves with the file. When you reopen it, the grouping buttons are still there in the same state — if you had collapsed a group before saving, it will still be collapsed when you open the file again.
Can I print only the visible (ungrouped) rows?
Yes. When you collapse a group, the hidden rows do not print. Only the rows currently visible on screen will appear in the printed output. This is useful for printing summary views without the supporting detail.
Is there a limit to how many levels of nesting I can create?
Excel allows up to eight levels of nested grouping. Most spreadsheets need only two or three levels, so this limit is rarely a problem in practice.
What if I group rows but the collapse buttons do not appear?
Check that you selected at least two rows before grouping — you cannot group a single row. Also make sure the Data menu shows "Ungroup" as an option (not grayed out), which confirms that grouping is active. If buttons still do not appear, try selecting the rows again and grouping them a second time.