MS-Excel / Functions and Formula

---

Turn Conditional Formatting and Data Validation On and Off with a Checkbox

Data validation can make it far less likely that a user will accidentally enter incorrect data. Sometimes, however, you might need to make it easier to enter data that otherwise would be flagged as incorrect by conditional formatting or blocked completely by the validator.

Usually, you would enable users to enter data that otherwise would be flagged as incorrect by removing conditional formatting and/or data validation from the cells. There is an easier way, however: you can combine a simple checkbox from the Forms toolbar with data validation.

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

Select cell K1 and name this cell CheckBoxLink by typing the name into the Name box to the left of the Formula bar and pressing Enter. Nowclick in cell I3 and select Developer → Insert → Form Controls → Check Box (pre-2007, if the Forms toolbar is not already showing, right-click any toolbar and select Forms, then click the checkbox icon).

Right-click the checkbox and select Format Control → Control. In the Cell Link box, type the name CheckBoxLink and click OK. Select cell A1, then drag and select a range down to cell H100. It is important that cell A1 is the active cell in your selection. Select Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format," and type the following in the "Format values where this formula is true" box, (pre-2007, Format → Conditional Formatting... → change "Cell Value Is" to "Formula Is"):

=AND(COUNTIF($A$1:$H$100,A1)>1,CheckboxLink)

Click the Format option and select the format 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 K1 (CheckBoxLink) will read TRUE and all cells with more than two duplicates within the range $A$1:$H$100 will be highlighted. As soon as you deselect the checkbox, its cell link (CheckBoxLink) will return FALSE, and these cells 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$1:$H$100,A1)>2 must return TRUE, and the cell link for the checkbox (CheckBoxLink) also must be TRUE. In other words, both conditions must be TRUE for the AND function to return TRUE.