Excel Checkboxes and How to Use Them

Getting up and running with Excel is fantastic for the more intermediate Microsoft Excel user. You feel like Iron Man when you first realize how much computing power and automation are at your fingertips. Excel lets you add conditional formatting, dynamic charts, buttons, checkboxes, labels, combo boxes, scroll lists, etc. Proper use of these form controls can be achieved when dealing with a massive datasheet, and you need to call many functions and procedures in the desired order. These controls come in handy, too, when data cells overlap each other. In this post, we’ll discuss how to create a checkbox in Excel and use it to automate your spreadsheet processes. You can follow along in our blog post video!

Launch Excel and create a datasheet on which you want to associate checkboxes with different actions. For example, in Checkbox Excel cell link box or delete multiple checkboxes. We created an attendance sheet for students containing the names and attendance fields. We must also include another table in our datasheet containing the Total, Present, and Absent fields.

Setting Up The Excel Developer Tab

We need to make the Developer tab visible on the Excel ribbon to get started. If you can’t find the Developer tab, go to the File menu, click Options, and look for the Customize ribbon tab in the Excel options dialog box. In the right pane, you can click on the developer box. This will allow you to see the developer tab in your Excel ribbon. You can find other Microsoft Excel options in the Excel options dialog box.

Inserting Checkboxes in Excel

Now that we have the Developer tab up and running, we will insert checkboxes into the datasheet to populate the table entries with a single click. First, go to the Developer tab from Insert Options, and click on the check box located under Form controls, as shown in the image below. When you click, you will see a plus sign indicator; click where you want to add a checkbox. If you double-click the Excel checkbox, you can edit the text to anything. This is a neat way if you want to get started on creating an interactive to-do list without having to mess around with VBA Code.

Using the Checkbox with Formulas

Select and remove the text, right-click on it, and in the context menu, click Format Control. You will see the control tab in the format control dialog box at the end. In this window, you're given a cell link box. With the cell link box, specify the location in the datasheet where you want to show the status of the check/uncheck. The linked cell will return TRUE/FALSE depending on the value of the Excel checkbox. Click OK to continue.

Using Excel Checkboxes in an Attendance Sheet

Now we'll move to the cell attendance checkbox. For this one, we must insert multiple checkboxes to accommodate everyone. Head to the developer tab in the Excel ribbon and insert a check box. Double-click the check box to edit text and remove the default name. With just the check box remaining, let's move the check box to the side. We want to add the cell link in the same worksheet for simplicity's sake. So right-click on the check box in the context menu and select format control. In the control tab, let's set the cell link to cell H2. When the check box is enabled, the cell link at the H2 location will change automatically. We can use this to spruce up our attendance sheet a bit. We will write a formula in the same cell at location C2, and this will check the value in your linked cell H2, The first parameter of the formula H2 = TRUE, the value in H2 checks that if TRUE, a keyword present will appear in the cell, and if false, then absent, it will appear in the cell. Now follow the same procedure and insert multiple checkboxes to include checkboxes with all cells in the Attendance field. You can copy down the formula as well so you can apply the formula to all the checkboxes. Note that you can copy and paste the check box, but you'll need to change the linked cells in the form controls for each one. As shown in the screen below, we have created checkboxes with cells. Where the checkbox is enabled, the corresponding value in column H2 will change to TRUE, eventually by formula evaluation. The present will appear in the corresponding cell in the presence field. We can clean this up by hiding the column that contains the TRUE/FALSE values. We can do that by selecting the entire column hovering your mouse pointer over the column letter, and clicking on it when it becomes a downward arrow. Right-click to bring up the context menu, then select hide. We will start publishing the following table; we entered 10 in the class (we have ten students). We will calculate the occurrence of keywords present in the table attendance column. So we will write a formula as; The result will be nine since there is only one absent student. To check the number of absent students, we will subtract the number of students present from the total number. The formula goes like this. The value in B15 (students present) will be subtracted from B14 (total) value, giving the desired result. You also note that all relevant information will automatically be updated when the checkbox is enabled/disabled. There are countless ways that you can use checkboxes in your spreadsheets! You can use them for aesthetic purposes or add a dash of helpful automation. Do you know what’s even better? Having the checkboxes already there in your dashboard! A great example of this is our Invoice template, wherein you can create checkboxes for your invoice sheet so that all you have to do is to print the sheet out. Check Simple Sheets and see all our automated dashboards go to work just for you!