On / Off conditional formatting with this simple trick

Posted on July 13th, 2016 in Learn Excel - 16 comments

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:

toggle-cf-tip

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.

on-off-conditional-formats-stop-if-true-option

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.

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

16 Responses to “On / Off conditional formatting with this simple trick”

  1. Shair says:

    Beautiful! Something so simple makes such a difference. Your tips are invaluable!

  2. John Lythe says:

    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!

    • Leon-K says:

      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.

  3. Amit says:

    It was amazing... Superb idea

    Your fan
    Amit

  4. Gabor says:

    Great, thank you!

  5. LALIT KUMAR says:

    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".

    • NARAYAN says:

      Hi ,

      Try either of the following , in cell B2 :

      =IF(OR(A1 = "Cash",A1 = "Finance"), "Lalit", "Kumar")

      =IF(OR(A1 = {"Cash","Finance"}), "Lalit", "Kumar")

      • Karen says:

        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

  6. ANSHUMMAN TIWARI says:

    as always it's fun to see your tips and tricks.

  7. daveycroc says:

    Lovely, I was so close to the same.
    I was using VBA to move the stop if true conditional format down the priority.

  8. achyutanam says:

    Awesome Imagination........:)

  9. badraddin says:

    Thank you

  10. KiwiSteve says:

    Wonderful! Love it

  11. Mariana says:

    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.

  12. Mariana says:

    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. =)

  13. Franciele says:

    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.

Leave a Reply