fbpx
Search
Close this search box.

On / Off conditional formatting with this simple trick

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

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

  14. edh says:

    Incredibly simple, so obvious after I see it, but pure genius for discovering this tidbit!

  15. Srilil says:

    This tip is awesome. Thank you so much.

Leave a Reply