Pause/ disable conditional formatting in MS Excel (On/ Off switch)
на канале: AuditExcel Advanced Excel and Financial Model Training and Consulting
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
Remove Conditional Formatting (3 ways)- • Remove conditional formatting from Ex...
From https://www.auditexcel.co.za/blog/pau...
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.
Смотрите видео Pause/ disable conditional formatting in MS Excel (On/ Off switch) онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь AuditExcel Advanced Excel and Financial Model Training and Consulting 30 Июль 2020, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 4,193 раз и оно понравилось 24 людям.