How to hide rows based on cell value in Excel?

When working with large datasets in Excel, you may often need to hide rows based on specific cell values to streamline your data view or focus on relevant information. This process can be tedious if done manually. Fortunately, in this tutorial, we'll explore three effective methods for hiding rows based on cell values in another column.

Hide rows based on cell value with Filter

The Filter function in Excel is a straightforward way to hide rows based on specific cell values. This built-in feature allows you to filter out data that meets certain criteria, effectively hiding rows that do not match your specified conditions. This method is ideal for quick, manual adjustments and can be easily reversed if needed.

1. Select the data you want to filter out, and click Data > Filter. See screenshot:

2. Then click on the down arrow to display the filter drop down list, and click Number Filters (or Text Filters) > Greater Than (you can choose other criterion you need from the submenu). See screenshot:

3. In the popping dialog, type the criterion into the textbox next to is greater than. See screenshot:

4. Click OK. Now the only the data greater than 3000 is shown, and the rows whose data is smaller than 3000 are hidden.

Quickly select rows based on cell value with Kutools for Excel

Kutools for Excel offers a powerful feature – Select Specific Cells to streamline the process of selecting rows based on cell values. While it doesn’t directly hide rows, it allows you to quickly select all rows that meet specific criteria, which you can then manually hide or perform further actions on. This method is highly efficient for handling large datasets and performing batch operations.

After downloading and installing Kutools for Excel, click Kutools > Select > Select Specific Cells to open the Select Specific Cells dialog box. Then configure the dialog as follows.

1. Select the column range containing numbers as the basis for row selection.
2. Select the Entire row option in the Selection type section.
3. In the first Specific type drop-down list, select Less than, and then enter 3000 into the text box to fiter rows where the value is less than 3000.
4. Click the OK button.

   

Result

All rows where the numbers in column B are less than 3000 will be automatically selected.

Hide rows based on cell value with VBA code

Moreover, if you are interested in VBA code, here I can introduce a VBA code to hide rows based on cell value.

1. Press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.

2. Then click Insert > Module to open a new Module window, and paste below VBA code into it.

VBA: Hide rows based on cell value.

Sub HideRow()
'Updateby20150618
Dim Rng As Range
Dim WorkRng As Range
Dim xNumber As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xNumber = Application.InputBox("Number", xTitleId, "", Type:=1)
For Each Rng In WorkRng
    Rng.EntireRow.Hidden = Rng.Value < xNumber
Next
End Sub

3. Then press F5 key to run the VBA, then select the data range you want to hide rows into the popping dialog (excluding headers). See screenshot:

4. Click OK, and type the criterion number into the second dialog. See screenshot:

Tip: If you want to hide rows which are greater than 3000, just change Rng.EntireRow.Hidden = Rng.Value < xNumber to Rng.EntireRow.Hidden = Rng.Value > xNumber, or if you want to hide rows whose data is equal to 3000, change to Rng.EntireRow.Hidden = Rng.Value = xNumber.

Hide Rows Based on Cell Value