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.
Table of contents
YouTube Switch off/ pause conditional formatting
Watch, like, subscribe
Setup the On/ Off Conditional Format functionality
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.
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
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.
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’.
The effect of this is that as soon as Excel sees that this condition is active, all other conditional formatting rules are ignored.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.