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, 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.

Dataset-Hide Rows Based on Cell Value in Excel


Download Excel Workbook


5 Easy Ways to Hide Rows Based on Cell Value in Excel

Method 1: Hide Rows Based On Cell Value Using Filter Feature

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.

Filter-Hide Rows Based on Cell Value in Excel

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

Clicking on filter icon

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.

Filter command box

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.

Filter result

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: Excel Macro: Hide Rows Based on Cell Text in Excel (3 Simple Methods)


Method 2: Using Formula and Then Filtering to Hide Rows 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.

=IF(E5<50,"Hide",E5)

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.

Formula -Hide Rows Based on Cell Value in Excel

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

Fill handle

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.

Unselecting hide text

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

Formula result

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

Read More: Formula to Hide Rows in Excel (7 Methods)


Similar Readings


Method 3: Applying Conditional Formatting to Hide Rows Based On Cell Value in Excel

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).

conditional formatting-Hide Rows Based on Cell Value in Excel

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.

 =$C5="West"

Click on Format.

New formatting Rule window

 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.

Format cells window

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

New formatting Rule window

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

Final result-Hide Rows Based on Cell Value in Excel

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.

Read More: Hide Rows Based on Cell Value with Conditional Formatting in Excel


Method 4: Hide Rows Based on Cell Value Using VBA Macro in Excel

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).

VBA Macro method

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

vba -Hide Rows Based on Cell Value in Excel

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

macro code

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.

vba macro code outcomes

Read More: VBA to Hide Rows in Excel (14 Methods)


Method 5: Hiding Rows Based On Cell Value in Real-Time Using VBA Macro

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.

real time vba -Hide Rows Based on Cell Value in Excel

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.

Module insertion

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

Selecting worksheet

Step 3: The Private Sub appears.

Private Sub

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

macro code-Hide Rows Based on Cell Value in Excel

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.

VBA outcomes

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.


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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

4 Comments
  1. Hi! I’m trying to apply some conditional formatting with a macro

    First: hide all rows (in the entire active sheet) if the value of the cell in Column G =”BID”, AND if the value of the cell in Column H <10000.00, AND if the value of the cell in Column I ="Orders, Web" OR ="cXML Orders"
    Second: hide all rows (in the entire active sheet) if the value of the cell in Column G ="MO", AND if the value of the cell in Column H <10000.00

    Then, as another separate macro to run after the above two are run:
    Apply RBG(253, 233, 217) color to any/all cells in Column G if the value of the cell in Column G is <10000.00

    If you could provide some direction on this, I would be so grateful!

    • ‘Thank you Dan for your comment. Let’s just your first row is 2 and last row is 7.

      ‘First condition: G = “BID”, H <= 10000.00, I = "Orders, Web" OR ="cXML Orders"

      Sub Hide_Condition1()
      Dim i As Integer
          For i = 2 To 5
      ' Cells (i,7) here 7 means G column and i will be 2, so it will be G2 cell and so on
              If Cells(i, 7).Value = "BID" And Cells(i, 8).Value <= 10000 And _
              (Cells(i, 9).Value = "Orders, Web" Or Cells(i, 9).Value = "cXML Orders") Then
                  Rows(i).EntireRow.Hidden = True
              Else
                  Rows(i).EntireRow.Hidden = False
              End If
          Next i
      End Sub

      ‘Second condition: G =”MO”, H <10000.00

      Sub Hide_Condition_2()
      Dim i As Integer
          For i = 2 To 5
              If Cells(i, 7).Value = "MO" And Cells(i, 8).Value <= 10000 Then
      
                  Rows(i).EntireRow.Hidden = True
              Else
                  Rows(i).EntireRow.Hidden = False
              End If
          Next i
      End Sub

      ‘Last Condition: G <10000.00, Apply Color

      Sub Hide_Condition_3()
      Dim i As Integer
          For i = 2 To 5
              If Cells(i, 7).Value < 10000 Then
              Cells(i, 7).Interior.Color = RGB(253, 233, 217)
              End If
          Next i
      End Sub

      ‘Hope this answer your query.

      • It was working perfectly.
        But my case.
        I need to apply this formula in sheet1 according to change the value in sheet2.
        It is functioning unless I protect sheet 1.

        How it possible to functioning even if the worksheet protected.

        • Greetings Ashif,

          You can manipulate (Hide or Unhide) the Rows of a Protected Worksheet by ticking the Use AutoFilter option under Allow all users of this worksheet to:

          [Go to the Review tab > Protect / Unprotect Sheet > Tick Use AutoFilter > Enter Password > Click OK]

          Hiding Rows in Protected Worksheet

          I hope this may work in your case. Let me know your thoughts in the comment section.

          Regards
          Maruf Islam (Exceldemy Team)

Leave a reply

ExcelDemy
Logo