Categories
Excel

Enable/Disable Conditional Formatting with a Checkbox

Conditional formatting is a powerful feature of Excel that lets you change the appearance of cells based on their values. However, using the menus and dialog boxes of the GUI to apply and remove conditional formatting can be tedious and time-consuming. A better way to control conditional formatting is to use checkboxes on your worksheet that let you switch it on and off easily and quickly. This way, you can view your data in different ways whenever you want, without going through multiple steps.

For this example, you’ll apply conditional formatting to a range of cells so that any data appearing more than once is highlighted for easy identification. We’ll assume your table of data extends from cell $A$1:$H$11. To conditionally format this range of data so that you can identify duplicates requires a few steps.

1. Enable Developer Tab

Enable Developer Tab

If the Developer tab is not already showing, right-click any tab and select Customize the Ribbon, then check the Developer from the Main Tabs box and click OK.

2. Insert a Checkbox to Turn Conditional Formatting On and Off

  1. Go to the Developer tab and click on Insert in the Controls group.
  2. Select the Checkbox option from the Form Controls section.
  3. Right-click the checkbox and select Format Control.
  4. Select the Control tab and in the Cell Link box, type $A$13 and click OK.

For more information, visit Setting Up Checkboxes for Conditional Formatting.

3. Apply Conditional Formatting

Conditional Formatting dialog showing formula to format a range to highlight duplicates
  1. Select cell A2, then drag and select a range down to cell H11. It is important that cell A2 is the active cell in your selection.
  2. Select Home » Conditional Formatting » New Rule.
  3. Select Use a formula to determine which cells to format.
  4. Type =AND(COUNTIF($A$2:$H$11,A2)>1,$A$13) in the “Format values where this formula is true” box.
  5. Click on Format and select a color you want to be applied to duplicated data. Click OK, then OK again.

Although the checkbox you added to the worksheet is checked, the cell link in $A$13 will read TRUE and all duplicates within the range $A$2:$A$11 will be highlighted. As soon as you deselect the checkbox, its cell link ($A$13) will return FALSE, and duplicates will not be highlighted.

The checkbox gives you a switch so that you can turn conditional formatting on and off from the spreadsheet, with no need to return to the Conditional Formatting dialog box. You can apply the same principle to data validation when using the formula option.

This works because you used the AND function. AND means two things must occur: COUNTIF($A$2:$H$11,A2)>1 must return TRUE, and the cell link for the checkbox ($A$13) also must be TRUE. In other words, both conditions must be TRUE for the AND function to return TRUE.

Conditional Formatting

  1. Setting Up Checkboxes for Conditional Formatting
  2. Highlighting Formula Cells with Conditional Formatting
  3. Sum Cells That Meet Conditional Formatting Criteria
  4. Highlight Every Other Row or Column with MOD Function and Conditional Formatting
  5. Enable and Disable Conditional Formatting with a Checkbox
  6. Sort Data By Conditional Formatting Criteria