Temporarily Hide Excel Conditional Formatting

To highlight specific cells on an Excel worksheet, you can use conditional formatting.

In the example shown below, orders with a quantity greater than 50 are highlighted with green fill colour.

cells highlighted with green fill colour
cells highlighted with green fill colour

Conditional Formatting Rule

This was the result of simple conditional formatting, based on the cell value.

Sometimes though, the conditional formatting can be distracting, and there’s no built in way to temporarily remove it.

Edit Formatting Rule dialog box
Edit Formatting Rule dialog box

Create an On/Off Switch

Instead of removing the conditional formatting, you could add an On/Off switch to your worksheet. Then, adjust the rules, so you only show the conditional formatting when the switch is on.

In the screenshot below, there’s a cell named CondF_Show, and it has a data validation drop down for Yes and No.

cell named CondF_Show
cell named CondF_Show

Change Conditional Formatting Rule

I changed the conditional formatting to a formula that checks both the Quantity cell value, and the value in the CondF_Show cell.

Change Conditional Formatting Rule
Change Conditional Formatting Rule

On/Off Switch Set to No

After you revise the rule, when the CondF_Show cell is changed to No, the conditional formatting is temporarily hidden.

On/Off Switch Set to No
On/Off Switch Set to No

More Conditional Formatting Info

For more Conditional Formatting rules and advanced examples that use a formula, go to the Conditional Formatting Examples page on my Contextures site.

And here are a few more pages on my site, where you can learn more about Excel Conditional Formatting:

Fix Conditional Formatting Extra Rules

Conditional Formatting – Currency

Conditional Formatting – Documentation

Conditional Formatting – Data Bars

____________

0 thoughts on “Temporarily Hide Excel Conditional Formatting”

  1. I like the switch idea. In your solution, the switch is combined as part of the conditional formatting condition. Another method is to have the switch as its own condition (as condition 1); when “Stop if True” is checked, it will prevent any other formatting from being applied.

    When the switch is its own condition, it allows for the other formatting conditions to be simpler and easier to write. On the other hand, if you need more than 3 conditions, adding the switch will make your formatting incompatible with Excel 97-2003. What are other pros and cons of each method?

  2. Great tip! I’ve done this by using True/False as the value in the cell that serves as the switch, so that it shortens the conditional formatting statement:

    =AND(C2>50,CondF_Show=”Yes”)

    becomes

    =AND(C2>50,CondF_Show)

  3. need conditional formating based on the multiple of the number oin another cell. How to do that?
    For ex: in the formatted cell can only be the mutiple of the number in the other cell, otherwise it colours for ex red

  4. Thanks for the hint Debra, but it doesn’t bring what I expected.
    In fact I need that in CEL is ONLY a multiple of the value in cell B
    For ex: if B=100 in cell A should only be 100 or 200 or 300 etc. and NOT for ex. 150 or 175 etc. So a multiple of 100 ONLY
    Any other hint?

    Thanks in advance!

  5. Sorry Debra: I just copied the frormula but is does not work…
    Maybe I did not explain well enough: to put it very simple: cell B is a fixed value (let’s say 12)
    The cell to format can ONLY be: or 12 or 24 or 36 or 48 etc.
    If not the cell should color red

    Thansk in advance for a hint!

    Guido

  6. Hi and thanks again, Debra
    The cell to format in this case is cell A3 when she isn’t eual to 12 or 24 or 36 etc

  7. Guido, that second formula should work: =MOD(A3,B3)<>0
    You might have to use a semi-colon though, if that’s what you use in other formulas:
    =MOD(A3;B3)<>0

    If that doesn’t work, maybe you could send me a small version of your workbook:
    ddalgleish @ contextures.com

Leave a Reply to Guido Lenssens Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.