How to Group and Collapse Rows and Columns in Google Sheets

When dealing with extensive datasets, financial models, or quarterly sales figures in Google Sheets, sprawling rows and columns can quickly make your spreadsheet unwieldy and hard to navigate. While hiding rows is a common trick, hidden rows are easily overlooked, lack visual hierarchy, and often lead to accidental overwrites or calculation mistakes. Grouping rows and columns provides an elegant, interactive alternative, allowing you to expand and collapse detailed breakdowns with a single click while keeping summary rows visible.

In this guide, we explore how to group rows and columns in Google Sheets, create multi-level nested groupings, configure collapse toggle positions, and manage keyboard shortcuts to streamline your data analysis workflow.

Why Use Grouping Instead of Hiding Cells

Google Sheets offers two primary methods for concealing data temporarily: hiding and grouping. Understanding their functional differences ensures you choose the right approach for your spreadsheet structure:

• Interactive Visual Indicators: Grouping creates a bracketed outline with clear + and - toggle buttons in the margin. Collaborators can instantly see that underlying data exists without having to spot tiny hidden-row arrows.
• Multi-Level Outlining: You can nest groups up to eight levels deep, letting users drill down from yearly summaries to quarterly reports, and finally to individual weekly transactions.
• Protection Against Data Loss: Unlike hidden rows, which can be inadvertently deleted when deleting surrounding rows, grouped data retains clear boundary indications.
• Keyboard Accessibility: Groups can be toggled using quick keyboard shortcuts, enabling rapid review during client presentations or team meetings.

How to Group Rows in Google Sheets

To establish a collapsible group of rows, follow these simple steps:

1. Open your spreadsheet in Google Sheets on your desktop web browser.
2. Highlight the row numbers on the far-left margin that you want to include in the group. For example, click on row 5, hold down Shift, and click on row 12.
3. Right-click anywhere within the highlighted row headers.
4. From the context menu, select View more row actions > Group rows 5 – 12 (or press Alt + Shift + Right Arrow on Windows/ChromeOS, or Option + Shift + Right Arrow on macOS).
5. A grey bracket line and a minus (-) button will instantly appear in the margin to the left of the row numbers.

Click the minus (-) button to collapse the rows. The button immediately transforms into a plus (+) button, indicating that hidden rows are stored within that section. Click the plus button at any time to expand the detailed view again.

How to Group Columns in Google Sheets

Grouping columns functions identically to row grouping, placing the collapse controls along the top header bar above your sheet letters:

1. Click and drag across the column letter headers at the top of your sheet (e.g. columns C through G).
2. Right-click on any of the selected column headers.
3. Choose View more column actions > Group columns C – G (or use the shortcut Alt + Shift + Right Arrow / Option + Shift + Right Arrow).
4. A bracket bar and a toggle icon will appear directly above the selected column headers.

Click the - icon to fold the columns into a compact display. This is particularly advantageous for hiding monthly breakdown columns while keeping the annual total column visible.

Creating Multi-Level (Nested) Groups

When organising hierarchical data—such as project phases, department budgets, or geographical regions—you can nest groups inside parent groups.

1. First, create your primary outer group by selecting the entire dataset range (e.g. rows 2 to 30) and grouping them as described above.
2. Next, highlight a subset within that range (e.g. rows 3 to 10 for “Phase 1 Tasks”).
3. Right-click and select View more row actions > Group rows 3 – 10.
4. Google Sheets will add a second level of outline brackets in the margin.

At the very top of the row margin, numbered buttons (e.g. 1, 2, 3) will appear. Clicking 1 collapses all levels down to the highest-level summary, while clicking 2 or 3 expands the corresponding nested layers.

Quickly Expanding and Collapsing All Groups

If you have dozens of grouped sections throughout a complex financial sheet, clicking individual plus and minus icons is inefficient. Google Sheets provides built-in controls to adjust all groups simultaneously:

• Expand All Groups: Right-click on any group bracket or toggle button in the margin, and select Expand all row groups (or Expand all column groups).
• Collapse All Groups: Right-click on any group bracket and choose Collapse all row groups.
• Level Controls: Click the small outline level numbers (1, 2, etc.) situated at the top-left intersection of the row and column headers to jump directly to that hierarchical view.

Changing the Plus/Minus Toggle Button Position

By default, Google Sheets places the collapse/expand toggle button at the bottom of a grouped row range or at the right of a grouped column range. Depending on whether your summary total row sits above or below your detailed data, you may wish to reverse this alignment.

1. Right-click on any active group bracket or toggle icon in the margin.
2. Look for the option labelled Move +/- button to top (for rows) or Move +/- button to left (for columns).
3. Click the option to adjust the anchor position.

If your summary formula (such as =SUM(B5:B12)) resides in row 4 above the data, moving the toggle to the top aligns the control button directly beside your header row, creating an intuitive dashboard layout.

How to Remove or Ungroup Rows and Columns

When you no longer need grouping structures or wish to restructure your layout, removing groups is straightforward:

1. Highlight the rows or columns containing the group you wish to disband.
2. Right-click the row or column headers.
3. Select View more row actions > Ungroup rows (or press Alt + Shift + Left Arrow on Windows/ChromeOS, or Option + Shift + Left Arrow on macOS).
4. Alternatively, right-click directly on the grey bracket line in the margin and choose Remove group.
5. To wipe out all groups in one step, right-click any bracket in the margin and select Remove all row groups.

Best Practices and Troubleshooting

• Summary Formulas: Never include your summary total row inside the grouped range itself. If you include the summary row inside the group, collapsing the group will hide the total, defeating the purpose of outlining.
• Filtered Views Compatibility: Grouping operates independently of Google Sheets filters. However, applying a sort order to a sheet while groups are active can alter the ordering within groups. Always structure and group your records after establishing your primary data schema.
• Print Layouts: When exporting your sheet to PDF or printing, ensure you expand or collapse the desired groups beforehand; Google Sheets prints the exact visual state currently displayed on your screen.