Share via

Hide Rows Using Conditional Formatting

Anonymous
2019-09-29T14:50:09+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2019-09-29T16:03:51+00:00

    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.

    20+ people found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2019-09-29T15:18:52+00:00

    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.

    6 people found this answer helpful.
    0 comments
  3. Anonymous
    2019-09-30T12:05:57+00:00

    Hi HansV MVP,

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

    Thanks,

    Donna

    3 people found this answer helpful.
    0 comments
  4. Anonymous
    2019-09-29T15:43:07+00:00

    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

    3 people found this answer helpful.
    0 comments