Excel Hide Rows Based on Cell Value with Conditional Formatting

Get FREE Advanced Excel Exercises with Solutions!

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


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

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 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: How to Hide Rows Based on Cell Value in Excel


2. Hide Rows Using Conditional Formatting & Custom Format 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: How to Automatically Hide Rows with Zero Values in Excel


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


Download Practice Workbook


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo