How to Hide Rows Based on Cell Value in Excel

The simplest way to hide rows based on a cell value in Excel is a filter: click in your data, press Ctrl + Shift + L, click the arrow on the column you’re checking, and untick the values whose rows you want to hide, such as “Closed”. Excel hides those rows until you clear the filter. Excel has no built-in rule that hides rows automatically, so if you want rows to disappear as soon as a value changes, you need a short VBA macro. Both approaches, plus a formula option, are below.

Method 1: AutoFilter (no code)

1. Click any cell in your data. Make sure it has a header row.
2. Press Ctrl + Shift + L, or go to Data > Filter.
3. Click the arrow in the header of the column you want to check.
4. Untick the values to hide and click OK.

For conditions instead of exact values, use Number Filters (for example, Greater Than 1000), Text Filters (Contains “test”) or Date Filters (Before a date). Filtered row numbers turn blue, and the status bar shows how many records are visible.

Filters don’t update on their own. After editing data, click Data > Reapply (or press Ctrl + Alt + L) to hide newly matching rows.

Hide rows based on a condition across columns

Add a helper column with a formula that returns TRUE or FALSE, such as =AND(C2="Closed",D2<TODAY()-30), then filter that column to show only FALSE. This lets you hide rows using any logic a formula can express. See how to do multiple IF statements for building conditions.

Method 2: Show the rows you want with FILTER

In Excel for Microsoft 365, Excel 2021 and later, the FILTER function returns only matching rows in another place, leaving the source untouched:

=FILTER(A2:D500,C2:C500<>"Closed","Nothing to show")

Put it on a separate sheet or to the side of your data. It updates instantly when values change, which makes it a good no-code alternative to hiding rows in reports and dashboards.

Method 3: Hide rows automatically with VBA

This macro hides every row where column C says “Closed” and runs whenever you edit a cell in C2:C500. Change the range and the value to suit your sheet.

1. Right-click the sheet tab and choose View Code.
2. Paste the code below into the window that opens.
3. Close the editor and save the file as Excel Macro-Enabled Workbook (.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    If Intersect(Target, Me.Range("C2:C500")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    For Each c In Me.Range("C2:C500")
        c.EntireRow.Hidden = (c.Value = "Closed")
    Next c
    Application.ScreenUpdating = True
End Sub

To hide rows where a number is below a value instead, change the test to (c.Value < 100). To check for a blank cell, use (c.Value = ""). Macros must be enabled when the file opens. See how to run macros in Excel.

Why conditional formatting can’t hide rows

Conditional formatting changes how cells look, not whether rows are shown. Some guides suggest setting the font to white to “hide” values, but the rows still take up space, still print, and still count in formulas. Use a filter or VBA instead.

Show hidden rows again

• Filtered rows: click Data > Clear, or press Ctrl + Shift + L to remove the filter.
• Rows hidden by VBA: select all with Ctrl + A, right-click a row number and choose Unhide. The macro hides them again on the next edit unless you remove it.

More in how to unhide all rows in Excel.

Totals that ignore hidden rows

SUM includes hidden rows. Use =SUBTOTAL(109,D2:D500) to sum only visible rows, or =AGGREGATE(9,5,D2:D500), which also ignores rows hidden manually or by VBA.

Troubleshooting

The filter arrow doesn’t show all values

The data has a blank row, so Excel only filtered part of it. Delete the blank row, or select the full range before pressing Ctrl + Shift + L.

The macro doesn’t run

The code is in a regular module instead of the sheet’s own code window, or macros are disabled. Right-click the sheet tab, choose View Code, and make sure the code is there. Then reopen the file and click Enable Content.

Excel is slow after adding the macro

The loop checks every row on each edit. Keep the range as small as your data, and leave the ScreenUpdating lines in place.

Frequently asked questions

Can I hide columns based on a value the same way?

Filters only work on rows. For columns, change c.EntireRow.Hidden to c.EntireColumn.Hidden in a macro that loops across a header row.

Does this work in Excel for the web?

Filters and the FILTER function do. VBA macros don’t run in the web version.

Summary

• Quick and code-free: filter the column and untick the values.
• Complex rules: filter on a TRUE/FALSE helper column.
• Reports: use FILTER to show matching rows elsewhere.
• Automatic: a Worksheet_Change macro hides rows as values change.

Related: select only visible cells in Excel.