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.
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.
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.
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.
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.
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
____________
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?
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)
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
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
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!
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
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
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?
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
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
Hey Debra: you’re right: it works with the semi-colon!
Thank you very much for your help!
Guido