Zahid Hasan
In this article, we will discuss five simple steps to create Data Validation with Checkbox control in Excel.
Download Practice Workbook
5 Easy Steps to Create Data Validation with Checkbox Control in Excel
In this section of the article, we will discuss five simple steps to create Data Validation with Checkbox control in Excel. Let’s say, we have a blank dataset of Sales Data of ABC Company. Our goal is to fill up this dataset using Data Validation with Checkbox Control.
Let’s give you a quick overview of how we will fill this blank dataset. In the Name column, we will only accept text input, whereas, in the Sales Amount column, we will allow only numerical inputs. In the Region column, we will use a drop-down list to fill up the cells. For all the columns, we will use Checkboxes that need to be activated. Otherwise, we will not be able to insert data into these cells. Now, let’s follow the steps mentioned below to do this.
Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
Step 01: Insert Checkboxes from Developer Tab
In the first step, we will learn how we can insert Checkboxes in our Excel worksheet. Let’s follow the instructions outlined below.
- Firstly, create the following table as shown in the following image. This will come in handy later on.
- After that, Go to the Developer tab from Ribbon.
- Now, click on the Insert option from the Controls group.
- Then, choose the Check Box (Form Control) option from the drop-down.
- Following that, draw a Checkbox in the Data Validation Control column, as shown in the image below.
- Afterward, right-click on anywhere on the Checkbox.
- Now, select the Edit Text option.
- Subsequently, rename the Checkbox according to your convenience. In this case, we have renamed it Enable Data Entry.
Read More: How to Add Checkbox in Excel without Using Developer Tab (3 Methods)
Step 02: Link Cells with Checkboxes
In this step, we will link specific cells to the Checkboxes. Linking Checkbox to a cell returns a TRUE or FALSE value in the linked cell, depending on whether the Checkbox is ticked or unticked. We will use these TRUE and FALSE values in our Data Validation formula in the upcoming steps.
- Firstly, right-click on any portion of the Checkbox.
- After that, select the Format Control option.
As a result, the Format Control dialogue box will appear on your worksheet.
- Now, in the Format Control dialogue box, go to the Control tab.
- Following that, select cell C16 in the Cell link field.
- Subsequently, click OK.
After doing this, if you check the Enable Data Entry Checkbox, TRUE will be returned in cell C16, as shown in the image below.
- Now, follow the same procedure to create two more Checkboxes and link them with cells C17, and C18 respectively.
Read More: What Can You Do with Cell Value If Checkbox Is Checked in Excel?
Step 03: Formulate Data Validation for First Column
In this section of the article, we will formulate the Data Validation for the Name column. In the Name column, we will only accept text input as mentioned earlier. Now, let’s use the instructions given in the following section to do this.
- Firstly, select all the cells of the Name column.
- Then, go to the Data tab from Ribbon.
- After that, choose the Data Validation option from the Data Tools group.
- Now, in the Data Validation dialogue box, select the Custom option in the Allow field.
- Following that, use the formula given below in the Formula field.
=IF($C$16,ISTEXT(B5),FALSE)Here, cell C16 indicates the first cell of the Linked Cell column, and cell B5 represents the first cell of the Name column.
Formula Breakdown
- Here, the ISTEXT function returns a TRUE or FALSE value based on whether the value in cell B5 is text or a number.
- Here, cell B5 is the value argument.
- Output → TRUE.
- Then, in the IF function,
- $C$15 → This is the logical_test argument.
- ISTEXT(B6) → This refers to the value_if_true argument.
- FALSE → It indicates the value_if_false argument.
- Output → TRUE.
- Then, click OK.
- Now, insert a numerical input in cell B5.
You can see that Excel is giving an error. Because this cell is not allowed to have numerical data in it.
- Now, let’s insert a text value in cell B5. But this time, we have unchecked the Checkbox of Enable Data Entry.
Just like the previous one, Excel is returning an error again. That means the Enable Data Entry Checkbox should be checked and at the same time the entered data should be a text value. This indicates that our inserted Checkbox with Data Validation is working perfectly.
- Now, let’s enter the Name of the salespersons in the Name column while keeping the Enable Data Entry Checkbox checked.
Read More: If Checkbox Is Checked Then Apply Formula in Excel (4 Methods)
Step 04: Develop Data Validation for Second Column
Now, we will develop Data Validation for the Sales Amount column to create Checkbox with Data Validation in Excel. In the Sales Amount column, we will allow only the numerical values. Let’s use the instructions outlined below to do this.
- Firstly, select all the cells of the Sales Amount column.
- Afterward, go to the Data tab from Ribbon.
- Following that, select the Data Validation option from the Data Tools group.
- Now, in the Data Validation dialogue box, choose the Custom option in the Allow field.
- Following that, enter the following formula in the Formula field.
=IF(AND($C$16,$C$17),ISNUMBER(C5),FALSE)Here, cell C17 refers to the second cell of the Linked Cell column, and cell C5 indicates the first cell of the Sales Amount column.
Formula Breakdown
- The ISNUMBER function returns a TRUE or FALSE value based on whether a value is a number or a text.
- Here, cell C5 is the value argument.
- Output → TRUE.
- Now, in the AND function,
- $C$16 → It is the logical1 argument.
- $C$17 → This refers to the [logical2] argument.
- Output → TRUE.
- Now, the IF function becomes → IF(TRUE,TRUE,FALSE).
- Output → TRUE.
- Then, click OK.
- Subsequently, make sure that both the Enable Data Entry and the Validate Sales Checkboxes are checked.
- Then, enter the Sales Amount for each salesperson as demonstrated in the following picture.
Read More: How to Group Checkboxes in Excel (3 Easy Ways)
Step 05: Construct Drop-Down List for Third Column
In the final steps, we will create a drop-down list for the Region column. Now, let’s follow the procedure discussed below to do this.
- Firstly, in the Region List column, insert the available Regions as shown in the image given below.
- After that, select all the cells of the Region column.
- Then, go to the Data tab from Ribbon.
- Now, select the Data Validation option from the Data Tools group.
- Following that, in the Data Validation dialogue box, select the List option in the Allow field.
- Then, use the following formula in the Source field.
=IF(AND($C$16,$C$18),$D$16:$D$18,$D$19)Here, cell C18 indicates the third cell of the Linked Cell column, the range of cells D16:D18 represents the cells of the Region List column, and cell D19 represents a blank cell.
Formula Breakdown
- Here, in the AND function,
- $C$16 → It is the logical1 argument.
- $C$18 → This refers to the [logical2] argument.
- Output → TRUE.
- Now, the IF function becomes → IF(TRUE,$D$16:$D$18,$D$19).
- Here, TRUE → This is the logical_test argument.
- $D$16:$D$18 → It refers to the value_if_true argument.
- $D$19 → This indicates the value_if_false argument.
- Output → {“East”;”West”;”South”}.
- After that, click OK.
As a result, drop-down icons will be available in each cell of the Region column.
- Now, make sure that both the Enable Data Entry and the Activate Region List Checkboxes are checked.
- Following that, click on the drop-down icon beside cell D5 and the drop-down list will be available as shown in the image below.
If you uncheck any of the previously mentioned Checkboxes, the drop-down list will disappear.
- Now, insert the Regions for respective salespersons in the Region column, as demonstrated in the image below.
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
Conclusion
So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to create Data Validation with Checkbox control in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.
Related Articles
- How to Resize Checkbox in Excel (3 Easy Ways)
- Excel Checkbox: If Checked then Change Cell Color (2 Methods)
- VBA to Check If CheckBox Is Checked in Excel (3 Ways)
- How to Apply Conditional Formatting Using Checkbox in Excel
- Excel VBA: Form Control Checkbox Value (3 Examples)
- How to Filter Checkboxes in Excel (with Easy Steps)
