Hide Rows Using Conditional Formatting

Question

Hiya,

Microsoft Excel 2016 - Hide Rows Using Conditional Formatting

I am looking to find a way to hide certain rows based on drop-down answer e.g. if the answer is selected as "Yes", how can I hide the following 2/3 rows without using VB or Macros. Is there a way that this can be done via Conditional Formatting?

Any helpful would be grateful.

Thanks,

Donna

Answer 1

Conditional formatting cannot hide rows or change the rows' height.

If you want to hide rows fully automatically, you need VBA.

As an alternative, you could "hide" the contents of rows by setting the number format to ;;; (three consecutive semicolons) in conditional formatting. The rows would still be visible, but they would be blank.

Answer 2

Hi HansV MVP,

Thank you for your reply.

I am not trying to change the row height. I have attached a picture of the example I was talking about earlier. For example, if the user selects "No" in column B, how do I get rows A3, A4, A5 and A6 to automatically hide? 

Hope that clarifies my question a bit.

Thanks,

Donna

Answer 3

As I mentioned, to get rows to hide automatically, you would need VBA code. But you mentioned that you wanted to avoid that.

You can, however, hide the text in cells A3 to A6:

• Select A3:A6.
• On the Home tab of the ribbon, click Conditional Formatting, New Rule...
• Select 'Use a formula to determine which cells to format'.
• Enter the formula   =$B$2="No"
• Click Format...
• Activate the Number tab.
• Select Custom in the Category list.
• Enter   ;;;   (three consecutive semicolons) in the Type box.
• Click OK, then click OK again.

Answer 4

Hi HansV MVP,

Thank you for your patience and help with my query, appreciate it.

Thanks,

Donna