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
____________

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

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

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