How to Hide Rows in Google Sheets: Manual, Filters, Groups, and Automatic Rules

How to Hide Rows in Google Sheets

Right-click the row number in the left margin and choose Hide row. The keyboard shortcut is Ctrl+Alt+9 on Windows and Command+Option+9 on Mac. To hide several rows at once, drag across their row numbers first, then use the same command.

Two small arrows appear in the margin where the rows were. Click either arrow to bring them back, or select the rows on both sides of the gap and press Ctrl+Shift+9. Columns work the same way with Ctrl+Alt+0 to hide and Ctrl+Shift+0 to unhide.

That is the manual answer. Which section you actually need depends on the job:

• Empty rows and columns cluttering the edges of the sheet, see hiding unused cells below.
• Rows that match a value such as Complete, see hiding rows based on a cell value.
• Hiding rows for yourself only, without changing the view for collaborators, see filter views.
• A block you want to fold and unfold repeatedly, see grouping and collapsing rows.
• Rows that should disappear on their own when a value changes, see the flag column method.
• Totals that did not change after the rows vanished, see the SUBTOTAL section.

How to Hide Unused Cells in Google Sheets

Click the row number of the first empty row under your data, press Ctrl+Shift+Down to select everything to the bottom of the sheet, then right-click and choose Hide rows. Repeat with the first empty column and Ctrl+Shift+Right.

That gives you the clean dashboard look where the grid stops at the data. It is purely cosmetic, though, and worth knowing before you use it on a slow file.

A Google Sheets file holds a maximum of 10,000,000 cells across all tabs, and 18,278 columns in a single sheet. Hidden cells still count toward both. If the file has become slow to open, delete the unused rows and columns instead of hiding them, because deletion is what actually reduces the cell count.

Deleting is safe here. Right-click the selected empty rows and choose Delete rows, and Sheets keeps the grid working normally. You can add rows back later from the same menu.

How to Hide Rows Based on a Cell Value

Select your header row, choose Data and then Create a filter, then click the filter icon on the column you want to test. Filter by condition handles a single column, and Custom formula handles anything more involved.

To hide every row whose status column reads Complete, use a custom formula that returns the rows you want to keep:

The dollar sign locks the column so the rule tests column E for every row. Start the reference on the first data row, not the header.

One caveat matters more than the syntax. A filter created this way applies to the whole file, so every collaborator sees the rows disappear at the same moment you do. The next section is the private version.

How to Hide Rows Without Changing What Other People See

Use a filter view. Choose Data, then Filter views, then Create new filter view. Set the same conditions you would set on a normal filter and give the view a name.

A filter view is stored against its own URL, which is why the address bar gains an fvid parameter while the view is open. Sending that link opens the sheet with your view applied, and it never rearranges anyone else's screen.

Two properties make filter views the safer default on a shared file. They are per-person rather than global, and someone with view-only access can create a temporary one without edit rights.

The tradeoff is that filter views do not update themselves in the way people expect. A row whose status changes by formula stays where it is until the view is reopened, which is the exact problem the flag column method below solves.

How to Group and Collapse Rows Instead of Hiding Them

Select the rows, right-click, and choose Group rows. The shortcut is Alt+Shift+Right Arrow, with Option in place of Alt on Mac. Ungroup with Alt+Shift+Left Arrow.

A bar with a minus button appears beside the row numbers. Click it to fold the block away and click the plus to bring it back. Groups can be nested, so a quarter can collapse into a year.

Grouping is the right choice for anything you fold and unfold repeatedly, such as monthly detail under a summary. Hiding is better for rows nobody needs to see again this quarter. A collapsed group also looks exactly like a hidden row to the next person, which is worth remembering when they cannot find the unhide arrows.

Why onEdit Scripts Miss Formula-Driven Rows

Everything above hides rows when you ask it to. The harder version of the question is hiding rows automatically, the moment a value changes, with nobody clicking anything.

Filters and filter views are the first suggestion, and they work until a value changes by formula rather than by typing. Neither re-evaluates on recalculation, so a row that just became Complete stays visible until someone reopens the filter.

The second suggestion is Apps Script.

This has a harder limit. The onEdit trigger fires on human edits only. A cell that changes because a formula recalculated never fires it, so script-based hiding silently misses exactly the rows you built it for.

Both approaches also need the status column to already be clean. If the column contains Done, done, complete, and Finished as free text, neither the filter nor the script catches all four.

How to Hide Rows Automatically With a Flag Column

Split the job in two. Use FITS to decide whether a row should be visible, then use FILTER to build the view.

That formula reads Done, done, complete, and Finished the same way a person does. No cleanup pass on the status column first.

Point FILTER at the flag column on a second tab and you have a live view that recalculates like any other formula.

Because both parts are formulas, a value changing by recalculation updates the view immediately. There is no trigger to fire and no quota to hit.

For a rule that depends on judgment rather than status, describe the judgment.

If you genuinely need the rows collapsed rather than filtered, sort on the flag column and collapse the block at the bottom. That keeps everything in one tab without a script.

Why SUM Still Counts Hidden Rows

SUM reads the values in a range, not the pixels on your screen. Hiding a row changes nothing about the total, which is correct behaviour and almost never what people want.

SUBTOTAL is the function that respects visibility, and its first argument decides how much it respects:

That difference is the single most common surprise in this whole topic. Codes 1 to 11 only skip rows removed by a filter. The 101 to 111 versions of the same codes also skip rows you hid by hand.

The pattern repeats across the family. Use 101 for AVERAGE, 102 for COUNT, and 103 for COUNTA when you want visible rows only.

Why FILTER and QUERY Ignore Which Rows Are Hidden

FILTER and QUERY read the underlying data, so a hidden row is still an input to both. There is no argument that changes this.

The workaround is a helper column that turns visibility into a value. SUBTOTAL with code 103 counts non-empty visible cells, so pointed at a single cell it returns 1 when that row is visible and 0 when it is hidden:

Fill that down column H, then filter on it like any other column:

Now a report tab shows only the rows visible on the source tab. The helper column is also the honest way to export what someone sees rather than what the sheet stores.

Why You Cannot Unhide Rows in Google Sheets

Four causes cover almost every case, and they look identical from the outside.

1. An active filter, not a hidden row. A filter removes rows without leaving the unhide arrows in the margin. Look for the green funnel icon in the column headers and clear the filter.
2. A collapsed group. Check the left margin for a plus button. Groups fold rows away and never show unhide arrows.
3. A protected range. If you do not have edit rights on the range, the unhide option is unavailable. Check Data and then Protected sheets and ranges, or ask the owner.
4. Row 1 is hidden. There is no row above it to select, so the usual drag does not work. Type A1 in the Name Box at the top left, press Enter, then use Format, Row, and Unhide rows.

One quick diagnostic settles it. If the row numbers jump from 14 to 22 and there are no arrows in the margin, it is a filter or a group, not a hidden row.

When to Use Each

Hide rows by hand for a one-off tidy-up, and accept that everyone else sees it too. Hide unused cells for a dashboard, or delete them if the file is slow. Group rows for anything you fold and unfold on a schedule.

For one clean status column and manual edits only, a filter view is free and takes a minute. Reach for =FITS() when the status text is inconsistent, when values change by formula, or when the hide rule needs judgment.

Related jobs use the same pattern: writing IF THEN logic in plain English, auto-populating a cell based on another cell, classifying rows into categories before you filter them, running automations without a Zapier subscription, and building a workflow hub for the team. The full tour is in automating Google Sheets tasks you used to need regex for.

Frequently Asked Questions