Here is a quick & awesome way to make your dashboards sexy. Add an on / off switch to your conditional formats.
Take a look at this demo to understand what I mean:
How to add on/off switch to conditional formatting
The trick is to use stop if true feature in your conditional formatting rules screen.
Let’s say you have a few rules in your CF.
- Set up a form control check box to toggle the conditional formats
- Link it to a cell, let’s say $H$2
- Set up a new conditional formatting rule that checks for =NOT($H$2)
- No need to set any formatting options for this.
- Move this rule to the top in conditional formatting manage rules screen
- Check stop if true option for this new rule.
- You are done. If your user unchecks the form control, the conditional formats won’t be applied.
Related: Introduction to conditional formatting.
Download example workbook
Click here to download the example workbook. Examine the conditional formatting rules to learn how to do this.
Switch on your Excel awesomeness
Conditional formatting is one of the many ways to impress your boss. Learn about other advanced Excel skills and important formulas to make you a rock star at work.
Do you set up on/off switches for CF?
In most of my dashboards, I have something similar. These will help declutter the report.
What about you? How do you add such switches to your reports? Share your tips in the comments section.
18 Responses to “On / Off conditional formatting with this simple trick”
Beautiful! Something so simple makes such a difference. Your tips are invaluable!
Great Trick! I use this regularly with dashboards and reports - it allows users with different learning styles to consume your information. I have created a situation where the user can choose font formats or colour formats (or to turn all conditional formats off)
https://youtu.be/ZUgXjlxCmdU
I showed this to Bill Jelen at the Excel Summit in Auckland in January and even he was impressed!
John, thank you so much for sharing your method. I will be incorporating this functionality into my dashboards as a fundamental feature. As Shair has said, "Something so simple...".
Chandoo, thank you for again showing that innovative Excel solutions don't always have to be technically complex.
It was amazing... Superb idea
Your fan
Amit
Great, thank you!
i have a question please give me answer for this,,,, If A1 = Cash or Finance
so print in b2 cell "Lalit" and if A1= anything expect Cash or Finance So print "Kumar".
Hi ,
Try either of the following , in cell B2 :
=IF(OR(A1 = "Cash",A1 = "Finance"), "Lalit", "Kumar")
=IF(OR(A1 = {"Cash","Finance"}), "Lalit", "Kumar")
Thank you for answering this question. This is something I have been trying to work out and was going an entirely different way. This is much simpler and more elegant. Can't wait to try it out! Tx
as always it's fun to see your tips and tricks.
Lovely, I was so close to the same.
I was using VBA to move the stop if true conditional format down the priority.
Awesome Imagination........:)
Thank you
Wonderful! Love it
I understand how this should work, but I cannot get to work correctly for me. I have several columns, each individually formatted with a graded color scale. I select the entire range and create the true/false rule, and make sure it's at the top, and that the "stop if true" box is checked. When I toggle my switch on/off, though; only the very first cell of the range goes on/off. Any thoughts on what is wrong? Thanks.
Never mind; I figured it out. I originally did not use an absolute reference for my on/off cell. It works now! So pleased. Thanks again. =)
Thank you very much, for help us! I'm a brazilian, I'm learning English and I love Excel, so in your site I can train both.
Incredibly simple, so obvious after I see it, but pure genius for discovering this tidbit!
This tip is awesome. Thank you so much.