How to Hide Rows Based on Cell Value with Conditional Formatting in Excel: 2 Methods

Method 1 – Changing Text Color to Hide Rows Based on Cell Value with Conditional Formatting

Steps:

Select the cell range B5:D10.
From the Data tab >>> Conditional Formatting > select “New Rule…”.

The “New Formatting Rule” dialog box will appear.

Select “Use a formula to determine which cells to format” from the Rule Type section.
Type the following formula in the Rule Description section.

=$D5=1988

This will apply our conditional formatting to rows with the year 1988.

Select “Format…”

excel hide rows based on cell value conditional formatting

The “Format Cells” dialog box will appear.

From the Font tab >>> Color >>> select “White”.
Press OK.

We can see there is nothing inside the Preview box.

Press OK.

excel hide rows based on cell value conditional formatting

We’ll hide rows based on cell value conditional formatting. Two rows contained the year 1988, and both vanished from view.

Note: This method has a drawback. If we change the background color, the text will appear. Hence, it is not always useful. Our next method will solve this problem.

excel hide rows based on cell value conditional formatting

Method 2 – Hide Rows Using Conditional Formatting & Custom Format Feature

Steps:

Following the first method, bring up the “New Formatting Rule” dialog box and click on “Format…”.
From the Number tab >>> select Custom in the Category section.
Type triple Semicolons (“;;;”).
Press OK.

We can again notice there is nothing on the Preview box.

Press OK.

excel hide rows based on cell value conditional formatting

In conclusion, this custom format will hide rows based on cell value conditional formatting.

Note: In our previous method, we saw that changing either the text color from White to any Visible Color or the background color will make the hidden rows visible. Using this technique, the rows will not appear, even when we change the background color.

excel hide rows based on cell value conditional formatting