Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 29 ยท Working with Data

Group and Hide Rows and Columns

Group and ungroup rows and columns in Google Sheets, collapse sections, hide versus filter, and total only visible rows with SUBTOTAL, with fixes for common problems.

A long sheet is hard to read when every row of detail is on screen. A monthly report may list every order, but the person reading it wants the month totals first and the detail only when they ask. Google Sheets has two tools for this. You can hide rows and columns, which removes them from view until someone unhides them, and you can group them, which adds a small + and - control so the reader can collapse and expand a block with one click. Groups are the better choice for anything you share, because the control shows that something is tucked away.

In this lesson you will group rows and columns, nest groups inside each other, learn the difference between hiding, grouping and filtering, total only the rows that are visible with SUBTOTAL, and fix the most common reasons a group will not work. If you have not used selections and headers yet, start with A Tour of the Google Sheets Interface.

Group rows

Say rows 3 to 6 hold the orders for January and row 7 is the January total. To let readers collapse the orders:

  1. Select the rows to tuck away (click the row number 3, then shift-click row number 6).
  2. Open Data > Group > Group rows 3 - 6. The shortcut is Alt + Shift + Right arrow on Windows and Chrome OS, or Option + Shift + Right arrow on a Mac.
  3. A thin bracket and a - control appear on the left of the row numbers. Click the - to collapse the rows. The control turns into a +. Click it to expand again.

The same menu has Ungroup rows. The shortcut is Alt + Shift + Left arrow. Ungrouping a block does not delete any data. It removes the control and the rows stay as they were.

Select the detail rows only, not the total row beneath them. Then the total stays visible when the group is collapsed, which is exactly what a reader wants to see.

Group columns

Columns work the same way. Select the columns (click the letter, shift-click another), then choose Data > Group > Group columns C - E. The control appears above the column letters. Grouping columns is useful for a sheet that has a quarter total at the end of three monthly columns, or a data block with helper columns that you want out of the way.

To group columns that are not next to each other, group them one block at a time. A single group must cover a continuous range, so a selection of C, D and F cannot become one group. Create the group for C:D, then a second group for F.

Nested groups

A group can sit inside a bigger group. Group rows 3 to 6 (the January orders), then group rows 3 to 12 (January and February), and Sheets draws a second level of brackets. Collapsing the outer group hides everything inside it, and collapsing only the inner one hides just the January detail. This gives you an outline: totals for the year, then months, then orders. Sheets allows several levels (up to eight), which is more than a readable report needs.

By default the control sits below a row group and to the right of a column group, next to the total. Build your sheet to match: details first, total underneath, and use that layout for the whole sheet so readers always know where to click.

Hide, group or filter?

These three look similar on screen but behave differently.

Tool How Who can undo it Best for
Hide Right-click the row or column header and choose Hide row or Hide column Anyone who selects the neighbors and clicks the small arrows Rows you want gone for good, such as scratch work
Group Data > Group Anyone, with one click on the + Detail a reader may want to see
Filter Data > Create a filter Anyone who changes the filter Showing only rows that match a rule (Sorting and Filtering)

Hidden and collapsed rows are still part of the sheet. A SUM includes them, so the number on screen can differ from the sum of what you see. That brings us to SUBTOTAL.

Totals that respect hidden rows

SUBTOTAL(function_code, range) calculates a total over a range and can ignore rows that are hidden. The function code picks the calculation, and there are two sets of codes:

Calculation Code (includes manually hidden rows) Code (ignores hidden rows)
Average 1 101
Count 2 102
CountA 3 103
Max 4 104
Min 5 105
Product 6 106
Sum 9 109

Both sets always ignore rows removed by a filter. Only the 100-series also ignore rows that you hid by hand or collapsed in a group. So =SUBTOTAL(109, D3:D20) is "the sum of what I can see", while =SUM(D3:D20) and =SUBTOTAL(9, D3:D20) still count collapsed rows. Another benefit of SUBTOTAL is that it does not double-count other SUBTOTAL cells in its range, so a grand total can safely cover the monthly totals.

JS Stand-in: SUM versus SUBTOTAL(109) when rows are collapsed

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Collapse the group in your own sheet and the SUBTOTAL(109, ...) cell updates by itself, which makes a collapsible report that always adds up to what is on screen.

Working with groups

  • Expand or collapse everything. Right-click a group control and choose Expand all row groups or Collapse all row groups (the same for columns). The shortcuts are in Data > Group too.
  • Copy a grouped range. Copying and pasting rows keeps the cell contents. Groups themselves belong to the sheet's layout, so check the pasted result and regroup if needed.
  • Printing and sharing. A collapsed group prints collapsed. Collapse to the level you want before you print or export to PDF.
  • Protecting layout. A viewer can expand any group. If the detail must not be seen, do not only collapse it; use sheet protection and share a copy without the sensitive rows (Sharing and Collaboration).

Why can't I group?

If Group rows is greyed out or the shortcut does nothing, check these in order:

  1. The selection is a single cell or a partial header. Select whole rows or whole columns.
  2. The selection crosses a frozen row or column. Frozen cells and the rest of the sheet cannot be one group. Unfreeze (View > Freeze > No rows), group, then freeze again.
  3. The selection overlaps an existing group in a way Sheets cannot nest. A new group must either sit completely inside an existing group or completely contain it, not cross its edge. Ungroup first or adjust the selection.
  4. The selection is not continuous. Group non-adjacent blocks separately, as above.
  5. You are in a view-only file. You need edit access. Viewers can expand a group but not create one.
  6. You are on the mobile app. Grouping is a desktop-browser feature. The mobile app shows and expands existing groups; create them in a desktop browser.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which menu path groups the selected rows?

    Grouping is under the Data menu. After grouping, a +/- control appears next to the row numbers.
  2. 2. You want to group columns C, D and F but not E. What should you do?

    A group must cover a continuous range, so create one group per block.
  3. 3. Rows 3 to 6 are collapsed in a group. Which formula returns the sum of only the visible rows in D3:D20?

    Function code 109 ignores rows hidden by hand or collapsed in a group. Code 9 and SUM still include them.
  4. 4. What happens to your data when you ungroup rows?

    Grouping is just an outline layer. Ungrouping removes the layer and leaves the cells untouched.
  5. 5. Why should you select only the detail rows, not the total row, when grouping?

    Whatever is inside the group disappears on collapse. Keep the summary row outside so readers still see it.

Summary

  • Group rows or columns with Data > Group (or Alt + Shift + Right arrow); the +/- control collapses and expands them. Ungroup removes only the control.
  • Groups can be nested into levels; each group must cover a continuous range, and non-adjacent blocks need separate groups.
  • Hiding removes rows quietly, grouping gives readers a visible control, and filtering shows only rows that match a rule.
  • SUM counts hidden rows; SUBTOTAL(109, range) totals only what you can see.
  • If grouping fails, check the selection, frozen rows or columns, overlapping groups and your edit rights.

Next: Conditional Formatting.