Group Rows or Columns in Excel: The Right Way

Grouping rows or columns in Excel lets you collapse and expand sections of your sheet with a single click, making large datasets easier to navigate and present. You can group either dimension in the same way, but the choice matters depending on whether you want to organize data vertically (rows) or horizontally (columns). This guide explains how grouping works, when to use rows versus columns, and how to avoid common mistakes.

What Grouping Does in Excel

Grouping in Excel creates an outline that lets you hide or show a set of rows or columns with a plus/minus button or a summary row/column. It’s a built-in feature under Data > Outline > Group (or Shift+Alt+Right Arrow on Windows). When you group rows 2 through 10, for example, Excel adds a bracket on the left side of the row numbers; clicking the minus sign collapses those rows, and the plus sign expands them again. Columns work identically, with the bracket appearing above the column letters.

The key point is that grouping does not delete or hide data permanently—it just changes the view. This makes it ideal for reports where you want to show summary totals but allow viewers to drill into details if needed.

When to Group Rows

Grouping rows is the most common use case because most datasets are organized with records in rows and fields in columns. You should group rows when:

You have a list of transactions or entries that belong to a category, and you want to collapse each category to its subtotal.
You have monthly or quarterly data that you want to collapse to yearly totals.
You have a long worksheet with sections (e.g., “Sales”, “Expenses”, “Profit”) and you want to collapse each section.

For example, if you have a sheet with 12 monthly columns of revenue and you want to show only quarterly totals, you’d group the three columns for each quarter—but if you have 12 rows of monthly data and want quarterly totals, you’d group the rows. The rule of thumb: group the dimension that contains the detail you want to hide.

When to Group Columns

Grouping columns is less frequent but equally useful when your data is spread horizontally. Common scenarios include:

You have a wide table with many periods (e.g., 52 weeks) and you want to collapse all but the current month.
You have multiple versions of a calculation (e.g., “Plan”, “Actual”, “Forecast”) and you want to show only one at a time.
You have auxiliary columns (e.g., formulas, lookup keys) that you don’t want visible in a printed report.

For instance, a financial model might have columns A–D for assumptions, E–H for calculations, and I–L for outputs. Grouping columns E–H lets you hide the calculation logic and present only assumptions and outputs.

How to Group Rows or Columns (Step-by-Step)

Select the rows or columns you want to group. For rows, click and drag the row numbers; for columns, click and drag the column letters.
Go to Data > Outline > Group (or press Shift+Alt+Right Arrow).
In the dialog, choose Rows or Columns and click OK.
To collapse, click the minus sign (−) on the bracket; to expand, click the plus sign (+).
To ungroup, select the grouped rows/columns and go to Data > Outline &gt> Ungroup (or press Shift+Alt+Left Arrow).

You can create multiple levels of grouping (e.g., group months into quarters, then quarters into years). Excel supports up to 8 levels of nesting.

Common Mistakes and How to Avoid Them

One frequent mistake is grouping the wrong dimension—for example, selecting the entire row range instead of the specific rows you want to hide. If you accidentally group all rows, you’ll get a single bracket that collapses everything. Always select only the detail rows (not the summary row) when grouping.

Another issue is that grouping doesn’t work well with filtered data. If you have an AutoFilter applied, grouping can behave unpredictably because hidden rows are not included. Clear the filter before grouping.

Also, be careful when copying grouped data to another sheet: the grouping outline is often lost, so you may need to re-group in the destination. Finally, if you have merged cells, grouping can be tricky—unmerge before grouping to avoid errors.

Grouping vs. Hiding vs. Subtotals

Grouping is often confused with hiding rows/columns (right-click > Hide) or using the Subtotal feature. Hiding is a one-way action: you can unhide manually, but there’s no expand/collapse button. Grouping is more user-friendly for interactive reports.

The Subtotal feature (Data > Outline > Subtotal) automatically inserts subtotal rows and creates groups for each category. It’s great for quick summaries, but it only works with numeric data and requires sorted categories. Manual grouping gives you full control over which rows or columns to include.

Keyboard Shortcuts and Tips

Group: Shift+Alt+Right Arrow
Ungroup: Shift+Alt+Left Arrow
Collapse all groups: Alt+A, H (or click the minus signs manually)
Expand all groups: Alt+A, J
To group only the selected rows/columns without the dialog, use the shortcut directly—it defaults to the selection type.
You can also use the Outline buttons (1, 2, 3…) at the top-left of the sheet to collapse/expand all levels at once.

If you have a large dataset, grouping can dramatically improve readability. But remember: grouping is a view tool, not a data organization tool. It doesn’t change the underlying data, so you can always ungroup and return to the full view.

Frequently Asked Questions

Can I group both rows and columns at the same time?

Yes, you can group rows and columns independently. For example, you might group rows by quarter and columns by year. Each dimension has its own outline brackets, and you can collapse/expand them separately.

Why is the group button greyed out?

The Group button is greyed out when the sheet is protected or when the selection includes entire rows/columns that are already grouped. Unprotect the sheet (Review > Unprotect Sheet) or check if the selection overlaps an existing group.

Does grouping work in Google Sheets?

Yes, Google Sheets has a similar feature: right-click on row numbers or column letters and choose “Group rows” or “Group columns”. The behavior is nearly identical, though the keyboard shortcuts differ (Ctrl+Alt+Shift+Right Arrow on Windows).

How do I remove all groups at once?

Select all data (Ctrl+A), then go to Data > Outline > Ungroup > Clear Outline. This removes all grouping levels but leaves your data intact.

Does grouping affect calculations or formulas?

No, grouping only changes the view. Formulas continue to calculate normally, even if the referenced cells are collapsed. However, if you use SUBTOTAL functions (like =SUBTOTAL(9, range)), they ignore hidden rows, which can be useful for grouped reports.

Can I print with groups collapsed?

Yes, when you collapse a group and then print, only the visible rows/columns are printed. This is a good way to create summary reports without copying data to a new sheet.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →