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

Data validation can make it far less likely that a user accidentally will 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 once 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 duplicates requires a few steps.