Enable and disable conditional formatting

Question

Is it possible to enable and disable, or overwrite automatic conditional formatting in Excel with a checkbox?

I figured out how to do that for custom conditional formatting, but for bars that are generated automatically, I'm not sure how to do that.

For instance something like that:

How can I make the formatting disappear when toggling the box? Specifically, that it looks like so after disabling:

Unfortunately, I cannot use vba or c# to achieve that, because sending Excel files with active macros is a security concern for a lot of people.

Is there still a way how to achieve that, eventually?

user_8162520 · Answer

Enable & Disable Conditional Formatting Using CheckBox

Well, yes certainly its achievable, please follow the steps,

• First select the range from B2:D6

• From Home Tab --> Under Styles Group --> Click Conditional Formatting,

• Click New,

• From New Formatting Rule Dialog Box --> Select Rule Type --> Format all cells based on their values,

• Under Edit the Rule Description --> Select Format Style as Data Bar

• Choose formatting accordingly and Press Ok -> Ok

• Next from Developer Tab --> Controls Menu --> Click Insert --> Click Check Box From Form Controls

• Place the Check Box in Cell F2 as shown in image,

• Now select the Check Box, right click on it, click Format Control --> Cell Link --> F2 --> Press Ok

• Next select the cell F2 --> Press CTRL 1 --> Format Cells Dialog Box opens --> Under Number Tab --> Category --> Custom --> Type --> ;;;

;;;

This hides the TRUE's & FALSE's which is occurs while checking and unchecking the Check Box,

• Select again the range B2:D6

• Press ALT H + L + N --> New Formatting Rule Dialog Opens --> Select the Rule Type --> Use A Formula To Determine Which Cells To Format, & enter the below formula, in the edit the rule description

=$F$2=FALSE

• Make sure the formatting will be No Fill i.e. No Color should be chosen from the Fill Tab,

• Press Ok --> Ok

• Finally the above rule needs to be set as STOP IF TRUE, as shown below & apply

• Press Ok,

• Images below shown after Checking & Unchecking the Check Box,

Unchecked Check Box

Checked Check Box

Please refer the .gif link as well, to ensure it works!

Enable and disable conditional formatting

user_18555986 · Answer

I already got an awesome answer, just want to add one thing:

It is possible to have a label set on the linked cell for the checkbox. Just hit Ctrl + 1 and type ;;;"Show Conditional Formatting" in custom settings - this way you do not need an extra cell.

This is what it looks like:

Enable and disable conditional formatting

2 Answers

Enable & Disable Conditional Formatting Using CheckBox