How to Hide Rows Based on Cell Value in Excel (5 Methods)

In day-to-day uses, we need to hide rows based on cell values in Excel to deal with a huge dataset. Typically, an Excel dataset contains numerous rows. And most of the time it’s tiresome to go through the entire dataset to sort the entries against call values. However, using Excel’s Filter, and Conditional Formatting features as well as formula and VBA macros, we can hide rows based on cell value in Excel. In this article, we demonstrate those previously mentioned ways to hide rows based on a cell value in Excel.

Let’s say we have a Sales dataset consisting of columns Order Date, Region, Product, and Quantity. We want to use any of the cell values in the column to hide rows.

1. Using Excel Filter Feature to Hide Rows Based on Cell Value

In the Home, tab Excel offers the Filter feature in the Editing section. We can use any desired cell value to filter the entries resulting in hiding the rows.

Step 1: In the worksheet, Go to the Home tab > Select Sort & Filter (from the Editing section) > Select Filter (from the Sort & Filter options).

Selecting Filter displays the Filter icon in each column header.

Step 2: Click on any filter icon in the column headers (i.e., Region).

Step 3: After clicking on the Filter icon, the Filter command box appears. Unticked any items (i.e., West) to hide their respective rows from the dataset.

Click on OK.

In a moment, Excel hides the unticked entries (i.e., West) from the dataset and leaves all other entries to display as shown in the picture below.

You can untick any of the entries to hide the rows from any column using the header filter icon. For better representation, we demonstrate the method with a handful of rows and columns. You can use this method to as many columns as you need and hide as many rows as possible according to your data type.

Read More: Hide Rows and Columns in Excel

2. Using Formula and Filtering to Hide Rows in Excel Based on Cell Value

In the previous method, we used the Filter feature to hide rows based on cell values. What if we want a formula to indicate our entries whether fulfill our criteria or not? Then filter the rows against the indication.

In this method, we use the IF function to insert a custom string (i.e., Hide) to indicate the incapability of fulfilling our demand. Then using the Filter feature, we hide the rows that contain the Hide string in them.

Step 1: Type the following formula in any cells (i.e., F5) adjacent to the raw dataset.

E5<50, is the logical_test where it declares the E5 values that less than 50 will display Hide text in the cells. Otherwise, the formula displays E5 itself.

Step 2: Press ENTER and Drag the Fill Handle to the custom string in the cells as depicted below picture.

Step 3: Follow Steps 1 to 3 of Method 1 to bring out the Filter command box. In the command box, unselect the Hide value then Click OK.

Filtering the entries without selecting the Hide value, hides all the rows that contain the Hide value in their entries.

You can create any custom string to apply the Filter to. This method is useful for conducting criteria (that can be value or text) searches in a dataset.

Read More: How to Automatically Hide Rows with Zero Values in Excel

3. Applying Excel Conditional Formatting to Hide Rows

Excel’s Conditional Formatting feature is also capable of hiding row contents depending on cell value. However, earlier methods hide the entire row in such a way that it seems they didn’t exist, but this method only hides the row contents.

Step 1: Go to the Home tab > Select Conditional Formatting > Select New Rule (from the options).

Step 2: The New Formatting Rule window appears. In the New Formatting Rule window,

Select Use a formula to determine which cell to format as Select a Rule Type.

Write the following formula under Edit the Rule Description.

Click on Format.

 Step 3: The Format Cells window appears. Inside the Format Cells window,

Select the Number section > Choose Custom (under the Category option) > Type 3 Semicolons (i.e., ;;;) under the Type section.

Click on OK.

Step 4: By clicking OK, Excel takes you back to the New Formatting Rule dialog box. Again, Click OK.

Executing Steps 1 to 4 results in hiding all the contents that match West in the C column.

You can assign any text or value in the formula that we insert to apply the Conditional Formatting. If you want only to hide the contents, not the rows, this method is very convenient to use. Also, you can clear the Conditional Formatting rules from the options anytime you want the raw dataset without altering a single thing.

4. Hiding Rows Based on Cell Value Using VBA Macro

VBA is a strong tool to achieve any conditional-driven outcomes. In this method, we use VBA macro code to hide rows from a specific column imposing a certain condition.

For this method, we transform our dataset as practical ones as depicted in the below screenshot. And want to hide the rows depending on a column’s (i.e., Region) value equal to a cell value (i.e., East).

Step 1: Hit ALT+F11 altogether to open the Microsoft Visual Basic window. In the window, Hover to the Toolbar > Select Insert > Select Module.

Step 2: Paste the following macro code in the Module and Press F5 to run the macro.

Sub Hide_Rows_Based_On_Cell_Value()
StartRow = 2
EndRow = 15
ColNum = 2
For i = StartRow To EndRow
If Cells(i, ColNum).Value <> "East" Then
Cells(i, ColNum).EntireRow.Hidden = True
Else
Cells(i, ColNum).EntireRow.Hidden = False
End If
Next i
End Sub

The macro code assigns start (i.e., 2), end (i.e., 15) row and column (i.e., 2, Region Column) numbers. The column number declares in which column the macro matches the given value (i.e., East). Then the VBA IF function hides any rows except the East value existing in the rows of the given column (i.e., Region column).

Step 3: Executing the macro code hides all the rows except rows containing East in their cells similar to the image below.

Read More: VBA to Hide Rows in Excel

5. Using VBA Macro to Hide Rows Based on Cell Value in Real-Time

Using VBA Macro Code in Method 4, we hide rows of a final or finished dataset. But there can be a scenario where we have to hide rows in real-time maintaining a certain condition. In that case, we can use a private macro to deal with this kind of scenario.

For this below dataset, we want to execute an operation where we just write the criteria and Excel automatically hides the rows in real-time.

Step 1: After opening the Microsoft Visual Basic (by pressing ALT+F11 altogether), Double Click on the respective sheet (i.e., Sheet3) under the VBAProject section.

Step 2: Choose Worksheet from the sheet’s code window as shown in the below screenshot.

Step 3: The Private Sub appears.

Step 4: Similar to the previous macro code, Paste the following macro code in the sheet’s code window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
StartRow = 2
EndRow = 15
ColNum = 2
For i = StartRow To EndRow
If Cells(i, ColNum).Value = Range("A18").Value Then
Cells(i, ColNum).EntireRow.Hidden = True
Else
Cells(i, ColNum).EntireRow.Hidden = False
End If
Next i
End Sub

The written macro code assigns start (i.e., 2), end (i.e., 15) row, and column (i.e., 2) numbers. Then it imposes a condition that it hides values equal to cell A18 in column 2. The VBA IF function creates a private macro code to hide rows in real-time after entering any value in cell A18.

Step 5: Hit F5 to run the macro then back to the worksheet. Try Typing anything that exists in Column 2 and Press ENTER.

Hitting enter after typing any texts (i.e., East or West), hides the respective text containing rows from the dataset. You can use any text or value of the assigned column to hide rows within a dataset.

Read More: VBA to Hide Rows Based on Cell Value in Excel

Download Excel Workbook

Conclusion

In this article, we demonstrate to hide rows based on cell value in Excel. Excel’s Filter and Conditional Formatting features are handy in hiding rows. However, the Conditional Formatting feature just hides the contents in the rows. VBA macros offer effective ways to hide rows according to your data type. Hope these described methods do the job in the way you desire. Comment, if you have further inquiries or have anything to add.

Related Articles

• How to Hide Blank Rows in Excel VBA
• VBA to Hide Rows Based on Criteria in Excel
• How to Hide the Same Rows Across Multiple Excel Worksheets

<< Go Back to Hide Rows | Rows in Excel | Learn Excel