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.

CondFormatOff01

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

CondFormatOff02

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

Create an On/Off Switch

Instead of removing the conditional formatting, you could add an On/Off switch to your worksheet, and 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.

CondFormatOff03

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

CondFormatOff04

Then, when the CondF_Show cell is changed to No, the conditional formatting is temporarily hidden.

CondFormatOff05

____________

You may also like...

11 Responses

  1. David says:

    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. Jerry Betz says:

    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. Guido Lenssens says:

    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. Guido, you can use the MOD function, For example, to check if cell B3 is a multiple of A3, select B3, and use this formula for the conditional formatting:
    =MOD(B3,A3)<>0

  5. Guido Lenssens says:

    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!

  6. Guido, if you want to check if cell A3 is a multiple of B3, select A3, and use this formula for the conditional formatting, with red fill colour formatting:
    =MOD(A3,B3)<>0

  7. Guido Lenssens says:

    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

  8. Guido, if cell B3 has a value of 12, which cell are you trying to colour red if it’s not a multiple of 12?

  9. Guido Lenssens says:

    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

  10. 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

  11. Guido Lenssens says:

    Hey Debra: you’re right: it works with the semi-colon!

    Thank you very much for your help!
    Guido

Leave a Reply to Guido Lenssens Cancel reply

Your email address will not be published. Required fields are marked *