How to Hide Rows in Excel Based on Cell Value

To hide rows in Excel based on a cell value, the fastest method is AutoFilter: select your data, click Data → Filter, then use the column’s drop-down arrow to show only the values you want and hide the rest. For automatic, permanent hiding, use a short VBA macro that loops through your rows and sets EntireRow.Hidden = True whenever a cell matches your criteria.

If you’re looking for a quick and simple way to tidy up your Excel spreadsheet by hiding rows based on the value of a cell, you’ve come to the right place. Hiding rows in Excel can make a big difference in how your data is presented, especially if you want to eliminate clutter and make your spreadsheet more readable. With Microsoft Excel, you can easily set up a formula that can hide rows based on specific criteria, such as cell values, allowing you to focus on the data that matters most to your needs. In this blog post, we’ll guide you through a step-by-step process to learn how to hide rows in Excel based on cell value.

Step 1: Open Your Excel Spreadsheet

The first step in hiding rows in Excel based on cell value is to open your spreadsheet. Once your spreadsheet is open, locate the cells you want to use as your criteria for hiding rows. For example, you might want to hide all rows where the cell in the A column contains a certain value.

Step 2: Select the Rows You Want to Hide

After locating the cells you want to use as your criteria for hiding rows, you need to select the rows you want to hide. Click and drag your mouse over the row numbers to select the rows, or left-click on the row number for each row while holding CTRL to individually select multiple rows.

Step 3: Hide the Selected Rows

With the rows still selected, right-click on one of the highlighted row numbers and choose ‘Hide’ from the context menu (or press ‘Ctrl’ + ‘9’). Excel will immediately hide every selected row from view. This is the most direct way to hide specific rows once you’ve identified which ones match your criteria. For a faster workflow, see our guide to the hide rows shortcut.

A quick clarification: Excel does not have a ‘Format Cells’ option that hides an entire row based on its value. A custom number format such as ;;; only hides the contents of a cell, not the row itself. Likewise, conditional formatting can change a cell’s color, font, or borders, but it cannot hide or delete rows. To hide rows based on a value automatically, use the AutoFilter or VBA methods described below.

Step 4: Choose How You Want to Match the Value

Decide which rows should disappear based on your cell criteria — for example, every row where column A contains the value ‘X’, or every row where a status column reads ‘Closed’. If you only need to do this once, hiding the matching rows manually (Step 3) is enough. If you want Excel to find and hide the matching rows for you, the AutoFilter feature or a VBA macro will do the work automatically.

Step 5: Apply Your Method and Hide Rows

Apply your chosen method — manual hiding, AutoFilter, or VBA — and the rows that meet your criteria will be hidden from view. To bring them back later, you can unhide the rows at any time, as explained in the unhiding section below.

Ready to Give it a Try?

Hiding rows in Excel based on cell value is a quick and simple way to improve the readability of your spreadsheet and focus on the data that matters most. Play around with different criteria and see how Excel can help you create a more organized and professional-looking spreadsheet.

Using the AutoFilter Feature to Hide Rows in Excel

Another quick and easy way to hide rows in Excel based on cell value is to use the AutoFilter feature, which is the fastest non-VBA option for hiding rows by value. This feature works well if you only want to temporarily hide rows to focus on specific data. To use this feature, select the column you want to filter, click the ‘Data’ tab, and then click the ‘Filter’ button. (Our guide on how to use filters in Excel covers this in more depth.) You can then set your criteria for hiding rows by clicking the drop-down arrow next to the column header and selecting the value you want to filter on. Rows that do not meet your criteria will be hidden from view. Repeat the process to remove the filter and show all rows again.

Hiding Rows in Excel with VBA

If you’re comfortable using VBA macros, you can also create a macro to automatically hide rows based on specific criteria. This can be useful if you have a large dataset and want to automate the process of hiding rows. To create a macro, press ‘Alt’ + ‘F11’ to open the Visual Basic Editor, click ‘Insert’, and then click ‘Module’. Enter the macro code that loops through each row and hides any row whose cell matches your criteria, then save the macro and run it.

A simple example that hides every row where column A equals “X” looks like this:

Sub HideRowsByValue()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRow
        If ws.Cells(i, "A").Value = "X" Then
            ws.Rows(i).EntireRow.Hidden = True
        End If
    Next i
End Sub

The key line is ws.Rows(i).EntireRow.Hidden = True, which hides the whole row whenever the cell value matches. To unhide them again, set EntireRow.Hidden = False in the same loop. If you’re new to writing or running macros, start with our guides on adding macros in Excel and the Excel VBA beginner’s guide.

Unhiding Rows in Excel

If you need to unhide rows in Excel, there are a few different methods depending on how you hid the rows in the first place. If you hid rows manually (Step 3), select the rows on either side of the hidden ones, right-click, and choose ‘Unhide’. If you used the AutoFilter feature to hide rows, click the drop-down arrow next to the column header and select ‘Clear Filter From Column Name‘ — this will remove the filter and show all rows again. For a full walkthrough of every option, see our dedicated guide on how to unhide rows in Excel.

Final Thoughts

Hiding rows in Excel can be a powerful tool to help you focus on the data that matters most. Whether you use the formula method, the AutoFilter feature, or a VBA macro, Excel makes it easy to hide and unhide rows based on specific criteria. With a little practice, you’ll be able to create organized and professional-looking spreadsheets that provide the insights you need.

FAQs about Hiding Rows in Excel Based on Cell Value

Can I hide multiple rows in Excel at once?

Yes, you can hide multiple rows in Excel at once by selecting the rows you want to hide and then using any of the methods described in this blog post to hide them.

Is it possible to hide rows in Excel based on multiple criteria?

Yes, it’s possible to hide rows in Excel based on multiple criteria by setting up a more complex formula in the ‘Format Cells’ dialog box. For example, you could hide all rows where the cell in the A column contains the value ‘X’ and the cell in the B column contains the value ‘Y’.

How do I know if rows are hidden in Excel?

In Excel, if rows are hidden, you will see a small triangle in the row number on the left-hand side of the screen. You can also tell if a row is hidden by trying to select it. If the row is hidden, you won’t be able to select it and Excel will skip over it when selecting multiple rows.

Can I still use formulas in hidden rows?

Yes, you can still use formulas in hidden rows. However, the results of the formulas will not be visible until you unhide the rows.

How do I unhide all rows in Excel?

To unhide all rows in Excel, select the entire sheet by clicking the box above the row numbers and to the left of the column letters (or by pressing ‘Ctrl’ + ‘A’). Then, right-click on any row number and select ‘Unhide’ from the context menu.

Frequently Asked Questions

What is the fastest way to hide rows based on a cell value?

AutoFilter is the fastest non-VBA method. Select your data, go to Data → Filter, click the drop-down arrow on the column you want to filter, and uncheck the values you want to hide. Excel instantly hides every row that doesn’t match. Excel even has a built-in feature for hiding rows this way, and it works on multiple criteria at once.

Can conditional formatting hide rows based on a value?

No. Conditional formatting can change the appearance of a cell or row — its fill color, font, or borders — but it cannot hide or delete a row. To actually hide rows by value, use AutoFilter or a VBA macro. See how to use conditional formatting in Excel for what it can and can’t do.

How do I hide rows automatically when a cell value changes?

Use a VBA macro. A macro that loops through your rows and sets EntireRow.Hidden = True whenever a cell matches your criteria can run on demand, or you can place the code in a Worksheet_Change event so it runs automatically as values change. The Excel VBA beginner’s guide explains how to set this up.

What’s the difference between hiding rows and deleting rows by value?

Hiding keeps the data in the file — the rows are just not displayed, and formulas referencing them still calculate. Deleting removes the data permanently. If your goal is to remove rows that match a condition rather than hide them, see how to delete multiple rows based on a condition.

Will hidden rows still be included in my formulas?

Yes. Hidden rows are still part of the worksheet, so functions like SUM and COUNT continue to include them. The exception is SUBTOTAL and AGGREGATE, which can be set to ignore rows hidden by a filter. The results of any formulas inside the hidden rows simply aren’t visible until you unhide them.

How do I hide rows without using AutoFilter or VBA?

Select the rows you want to hide (hold ‘Ctrl’ to pick non-adjacent rows), right-click a selected row number, and choose ‘Hide’ — or press ‘Ctrl’ + ‘9’. This is a manual method that works well for a small number of rows. The keyboard shortcut for hiding rows makes it even faster.