Hide Rows Based on Cell Value with Conditional Formatting in Excel

In this article, we’re going to show you 2 methods in Excel to hide rows based on cell value conditional formatting. To demonstrate our methods, we’ve picked a dataset with 3 columns: “Name”, “Email”, and “Born”.

excel hide rows based on cell value conditional formatting


Download Practice Workbook


2 Ways to Hide Rows Based on Cell Value with Conditional Formatting in Excel

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

For the first method, we’re going to set the text color to “White” to hide rows based on cell value Conditional Formatting. We aim to hide all the rows with the year 1988. To do this –

Steps:

  • Firstly, select the cell range B5:D10.
  • Secondly, from the Data tab >>> Conditional Formatting > select “New Rule…”.

The “New Formatting Ruledialog box will appear.

  • Thirdly, select “Use a formula to determine which cells to format” from the Rule Type section.
  • Then, type the following formula in the Rule Description section.
=$D5=1988

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

  • After that, select “Format…

excel hide rows based on cell value conditional formatting

The “Format Cellsdialog box will appear.

  • Then, from the Font tab >>> Color >>> select “White”.
  • After that, press OK.

We can see there is nothing inside the Preview box.

  • Finally, press OK.

excel hide rows based on cell value conditional formatting

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

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

excel hide rows based on cell value conditional formatting

Read More: Excel Macro: Hide Rows Based on Cell Text in Excel (3 Simple Methods)


Similar Readings


2. Hide Rows Using Conditional Formatting & Custom Formate Feature

For this section, we’re gonna use another conditional formatting to hide rows based on cell values in Excel. Instead of changing the font color, we’re going to implement a custom formatting, which contains triple Semicolons. Let’s jump into the action.

Steps:

  • Firstly, following the first method, bring up the “New Formatting Ruledialog box and click on “Format…”.
  • Secondly, from the Number tab >>> select Custom in the Category section.
  • Thirdly, type triple Semicolons (“;;;”).
  • Press OK.

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

  • Finally, 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’ve seen that if we change either the text color from White to any Visible Color or the background color it will make the hidden rows visible. However, using this technique, the rows will not appear, even when we change the background color.

excel hide rows based on cell value conditional formatting

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


Practice Section

We’ve provided practice datasets for each method in the Excel file. Hence, you can follow along with our methods easily.

excel hide rows based on cell value conditional formatting


Conclusion

We’ve shown you 2 methods in Excel to hide rows based on cell value conditional formatting. If you face any problems, feel free to comment below for further assistance. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo