Create an Excel Data Entry Form That Includes Checkboxes

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn how to create an Excel data entry form that includes checkboxes. Sometimes, users need to make a To-Do list or checklist in Excel. For those purposes, we can create a data entry form with checkboxes. Here, we will show the step-by-step procedures to create a data entry form that includes checkboxes. Also, we will explain 4 examples of Excel data forms. So, without further delay, let’s start the discussion.


How to Create an Excel Data Entry Form That Includes Checkboxes: Step-by-Step Procedures

Suppose, you have a dataset where you want to enter some data. However, you want to make sure that the person entering the data has selected a checkbox before that. You can add checkboxes in your data entry form by following some simple steps.

Here, we have a data entry form where we want to enter the Name and Age of an employee if he agrees to a statement. Now, we will show you how you can create an Excel data entry form that includes checkboxes.

Create an Excel Data Entry Form that Includes Checkboxes


Step 1: Insert Check Box from Form Controls

In the first step, you have to insert a checkbox from the Form Controls feature. Make sure you have the Developer tab in your excel workbook to do that.

Follow the steps given below to insert a checkbox into your Excel data entry form.

  • Firstly, go to the Developer tab >> click on Insert >> select Check Box from Form Controls.

Insert Check Box from Form Controls to Create an Excel Data Entry Form that Includes Checkboxes

  • Then, insert a Check Box into your form.

  • Now, if you want you can change the text in the Check Box. Here, we will “I agree to this statement”.


Step 2: Create Link to Cell

Next, we will show you how you can create a link to the cell where you see the status of the checkbox if it is checked or unchecked.

  • To start with, right-click on the checkbox.
  • After that, click on Format Control.

Create Link to Cell to Create an Excel Data Entry Form that Includes Checkboxes

  • Now, the Format Object box will open.
  • Then, insert Cell B4 in the Cell link box.
  • Next, click on OK.

Opening Format Object box to Create an Excel Data Entry Form that Includes Checkboxes

  • Finally, if you check the checkbox Cell B4 will be TRUE.

Read More: How to Create Data Entry Form in Excel


Step 3: Use Data Validation Feature

In the final step, we will use the Data Validation feature to enter data after selecting the checkbox. To do that in your data entry form, go through the steps given below.

  • Firstly, select Cell C7.
  • After that, go to the Data tab >> click on Data Tools >> click on Data Validation >> select Data Validation.

Use Data Validation Feature to Create an Excel Data Entry Form that Includes Checkboxes

  • Now, the Data Validation box will appear.
  • Then, select Custom as Validation Criteria.
  • Next, insert the following formula in the formula box.
=B4
  • Lastly, click on OK.

Opening Data Validation Box to Create an Excel Data Entry Form that Includes Checkboxes

  • Afterward, you can try to insert a name in Cell C7 without selecting the checkbox.

  • Then, you will find a warning box like the image shown below.

  • On the other hand, if you select the checkbox, you can enter any text in that cell.

  • Similarly, we can add another checkbox in the Excel form for age.

  • Now, to hide the linked cells, select cell range B4:B5.
  • After that, go to the Home tab >> click on Font Color >> select White, Background 1.

  • Thus, you can add checkboxes in your Excel data entry form.

Create an Excel Data Entry Form that Includes Checkboxes


4 Examples of Excel Data Entry Forms That Include Checkboxes

You can create data entry forms including checkboxes using Conditional Formatting, different functions, and formulas in Excel. Here, we will show you 4 examples of such forms.


Example 1: Make To Do List with Conditional Formatting That Includes Checkboxes

In the first example, we have a dataset of a work to do list. Now, we will show you how you can include checkboxes with Conditional Formatting in this list.

Make To Do List with Conditional Formatting to Create an Excel Data Entry Form that Includes Checkboxes

Steps:

  • Firstly, insert checkboxes following the steps shown above.

  • Then, create links with cells to check the status of the checkboxes going through the same steps shown above.

  • Next, insert the following formula in Cell D5.
=IF(E5=TRUE,"Done","To Be Done")
  • Then, press Enter and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

  • Thus, you will get the status of the work if it is Done or To Be Done.

In the IF function, we inserted if Cell E5 is TRUE, it will return Done otherwise it will return To Be Done.
  • After that, select cell range D5:D8.
  • Next, go to the Home tab >> click on Conditional Formatting.

  • Afterward, select New Rules.

  • Now, the New Formatting Rule box will open.
  • Then, select Use a formula to determine which cells to format option.
  • Next, insert the following formula in the box.
=$E5=TRUE
  • Further, click on Format.

Opening New Formatting Rule Box to Create an Excel Data Entry Form that Includes Checkboxes

  • Now, the Format Cells box will appear.
  • Then, go to the Font option >> turn on Strikethrough option.

Opening New Format Cells Box to Create an Excel Data Entry Form that Includes Checkboxes

  • Next, go to the Fill tab >> select any color of your choice.
  • Lastly, click on OK.

  • Again, click on OK.

  • Thus, you can include checkboxes in your to list with Conditional Formatting.

  • Additionally, to hide the linked cells click on Column E.
  • Then, right-click on it.
  • After that, click on Hide.

  • Finally, a ToDo list with checkboxes will be created.


Example 2: Create Checklist with Data Summary Applying COUNTIF Function

You can also create checklists with different functions. Suppose, we have a list of different tasks of a project. Now, we will show you how to add checkboxes and use the COUNTA & COUNTIF functions to create a checklist with data summary.

Create Checklist with Data Summary Applying COUNTIF Function in Excel

Steps:

  • In the beginning, insert checkboxes in cell range C5:C10 following the steps shown above.

  • After that, create links with cell range D5:D10 to check the status of the checkboxes going through the same steps shown above.

  • Next, you can hide Column D following the steps given in Example 1.

  • Then, insert the following formula in Cell C12 and press Enter.
=COUNTA(B5:B10)

Using COUNTA Function to Create an Excel Data Entry Form that Includes Checkboxes

Here, in the COUNTA function, we counted the number of tasks in cell range B5:B10.
  • Finally, to count the number of completed tasks, insert the following formula in Cell C13.
=COUNTIF(D5:D10,TRUE)

Using COUNTIF Function to Create an Excel Data Entry Form that Includes Checkboxes

Here, in the COUNTIF function, we counted the number of tasks in cell range D5:D10 if the value is TRUE.

Read More: How to Create Data Entry Form with Drop Down List in Excel


Example 3: Determine Monthly Payment Using Checkbox in Excel

In the third example, we have a dataset containing the value of Borrowed Amount, Annual Interest Rate and No of Payments. The value of Monthly Payments can vary whether the payments are made at the beginning or not. In such cases, you can use a checkbox for this condition and get the result according to that.

Determine Monthly Payment Using Checkbox in Excel

Follow the steps given below to determine monthly payments using checkboxes in Excel.

Steps:

  • Firstly, insert a checkbox following the steps shown above and change the text.

  • Next, create a link to this checkbox with Cell C8 like above.

  • Then, select Cell C10 and insert the following formula.
=PMT(C6,C7,C5,0,C8)
  • After that, press Enter.

Using PMT Function to Create an Excel Data Entry Form that Includes Checkboxes

Here, in the PMT function, we inserted Cell C6 as rate, Cell C7 as nper, Cell C5 as pv, 0 as fv and Cell C8 as type.
  • Now, you will get a value of Monthly Payments if payments are made at the beginning.

  • However, if you unselect the checkbox, the value of Monthly Payments will change.
  • Thus, you can determine monthly payments using checkboxes in Excel.

Read More: How to Create Data Entry Form in Excel VBA


Example 4: Use Checkboxes to Create Interactive Report

In the last example, you will find a way to create an interactive report using checkboxes in Excel. Here, we have the Sales value of 2 months of some products. Now, we will show you how using checkboxes you can find the Total Sales of the selected products.

Use Checkboxes to Make Interactive Report Create an Excel Data Entry Form that Includes Checkboxes

Steps:

  • To start with, insert the following formula in Cell E7.
=SUM(C7:D7)
  • After that, press Enter and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Using SUM Function to Create an Excel Data Entry Form that Includes Checkboxes

In the SUM function, we added the values of cell range C7:D7.
  • Now, you will get the Total Sales values for all the products.

  • Then, insert checkboxes for all the products following the steps shown above.

  • After that, create links with cell range H7:H10 with the corresponding product name going through the steps shown above.

  • Next, select Cell I7 and insert the following formula.
=IF(H7=TRUE,G7,"-")
  • Afterward, press Enter and drag down the Fill Handle tool to AutoFill the formula.

Here, in the IF function, we inserted if Cell H7 is TRUE, it will return the value of Cell G7 otherwise it will return “-”.
  • Now, you will get only the products selected in the list.

  • Lastly, insert the following formula in Cell H4 and press Enter.
=DSUM(A6:E10,"Total Sales",I6:I10)

Using DSUM Function to Create an Excel Data Entry Form that Includes Checkboxes

In the DSUM function, cell range A6:E10 as database, “Total Sales” as field and cell range I6:I10 as criteria.
  • Now, if you change the selected product list the Total Sales value will change.

Read More: How to Create an Excel Data Entry Form Without a UserForm


Download Practice Workbook

You can download the workbook to practice yourself.


Conclusion

So, in this article, we have shown you 4 examples of creating an Excel data entry form that includes checkboxes in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. Thank you!


Related Articles


<< Go Back to Data Entry Form in Excel | Data Entry in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo