On / Off conditional formatting with this simple trick
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
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.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« We are moving to New Zealand [personal]||Find out how many times a value is present in a cell [formulas] »|