Pause/ disable conditional formatting in MS Excel (On/ Off switch)

00:00 Situations where you want to pause conditional formatting
00:10 On/ Off Switch
00:14 Setup the new on/ off conditional formatting OVER the other conditional formats
00:51 After setup you need to prioritize this rule ABOVE the others i.e. stop the others running

Images on website

As useful as the feature is, it is sometimes nice to be able to pause/ switch off conditional formatting in MS Excel. So ideally you want a switch that says something like Switch On/ Off Conditional Formatting. When it is ‘On’ all the conditional formatting in the sheet is active. When the switch is set as ‘Off’, the conditional formatting is made inactive, so you see none of it.

This can be easily achieved.

First set up the dropdown option using data validation. Below we have a simple switch that has the option of On or Off. In Column E you can see the Conditional Formatting icons we have set up to show the variance between Actual results in 2016 and what is Budgeted for the next year.

Pause Switch off conditional formatting in MS Excel

In order to Pause /Switch off conditional formatting you can do the following:

Highlight the cells
Activate the Conditional Formatting New Rule (HOME ribbon, Conditional Formatting dropdown, New Rule)
Choose to ‘Use a formula to determine which cells to format’
In the resultant formula bar, click on the switch and type =”Off”
Note that we leave the format as is i.e. No format is set
Pause Switch off conditional formatting in MS Excel

After you click OK, that is not the end. Now you need to tell Excel that if this ‘Conditional Format’ is active (switch set to Off) then all other conditional formats must be paused.

To do this click on Manage Rules in the Conditional Format dropdown as shown below.

Pause Switch off conditional formatting in MS Excel

You should see your new rule in the list. If it is not at the top, click on it and move it to the top with the arrows. Then click the ‘Stop if true’ tick box’.

Pause Switch off conditional formatting in MS Excel

The effect of this is that as soon as Excel sees that this condition is active, all other conditional formatting rules are ignored.

[chinikarlugh]

Thank you for sharing this. I'm using this function so that I can check if the answers I typed in a cell is correct or not, but I don't want to always know it, that's why having this function where I can turn the conditional formatting on and off whenever I want is really helpful in my studies. It speeds things up. Thanks again. I hope you're having a great day :)

[TOOMtheRaccoon]

I really like this simple solution. Thank you for sharing this, very useful.

[safuanlatif]

Very brief yet a very clear explanation! Bravo!!

[cjphaedrus]

Thanks for posting this. Would it be correct to assume that by ordering the rules shown in the last part of the video that it would be possible to toggle on/off only some or one of the conditional formatting rules? eg If you had some basic formatting you want to see all the time those would always be on but an additional conditional format to call out some special conditions could be toggled? I may answer this myself when I get the chance to get back into Excel. I ask here as I have better access to videos than Excel.