Categories
Excel

Turn Data Validation On and Off with a Checkbox

One way to prevent users from entering wrong data is to use data validation. Data validation limits the type of data that can be entered in the cells. Sometimes, you might want to allow users to enter data that is not allowed by the data validation rules. Instead of removing the data validation, you can use a simple checkbox to override it.

In this article, you’ll limit the data that can be entered in a range of cells. For example, you can prevent users from entering duplicate values in a table of data that spans from cell $A$1:$H$11. You can combine a simple checkbox from the Developer tab with “data validation” to turn validation on and off. To do this, you need to follow these steps:

1. 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 Data Validation On or 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 Data Validation

  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 Data » Data Validation from Data Tools group.
  3. Select Custom from Allow drop-down.
  4. Type =IF($A$13=TRUE,COUNTIF($A$2:$H$11,A2)<2,TRUE) in the Formula box.
  5. On the Error Alert tab, you can customize the error message that will appear when users enter duplicate data.
  6. Click OK.

The data validation only works when the checkbox is checked. You can apply the same principle to conditional formatting when using the formula option.

The formula =IF($A$13=TRUE,COUNTIF($A$2:$H$11,A2)<2,TRUE) is used to check if the range A2:E11 contains numbers only when cell A11 is set to TRUE (true means the checkbox is checked). The formula has three arguments: 1. a logical test, 2. a value if true, and 3. a value if false.

  1. The logical test is $A$13=TRUE, which means the formula will only evaluate the second argument if the value in cell A13 is TRUE (A13 is linked to the checkbox). Otherwise, it will return the third argument, which is TRUE.
  2. The second argument is COUNTIF($A$2:$H$11,A2)<2, which uses the COUNTIF function to count how many times a value appears in the range A2:H11. The <2 part means the formula will return TRUE if the count is less than 2, and FALSE otherwise.
  3. The third argument is TRUE, which means the formula will return TRUE if the logical test is FALSE. It means that if the checkbox is not checked return TRUE instead of FALSE to make users bypass the Data Validation rules.

Data Validation