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.

1. Set up a form control check box to toggle the conditional formats
2. Link it to a cell, let’s say $H$2
3. Set up a new conditional formatting rule that checks for =NOT($H$2)
4. No need to set any formatting options for this.
5. Move this rule to the top in conditional formatting manage rules screen
6. Check stop if true option for this new rule.
7. 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.