Excel Checkboxes and How to Use ThemDec 23, 2021
Inserting Checkboxes in Excel
For the more intermediate Microsoft Excel user, getting up and running with Excel is an amazing feeling. You honestly feel like Iron Man when the first time you realize how much computing power and automation that’s 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 be talking about how you can create a checkbox in Excel and use them to automate processes in your spreadsheets. You can follow along in our blogpost video!
Launch Excel and create a datasheet on which you want to associate checkboxes with different actions. For example, we created an attendance sheet for students containing the names and attendance fields. We also have to include another table in our datasheet that contains 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. In case you can’t find the Developer tab, go to the File menu, click Options, in the excel options dialog box look for the customize ribbon tab. 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 also find other options about Microsoft Excel in the Excel options dialog box.
Inserting Checkboxes in Excel
Now that we have the Developer tab up and running, we will now insert checkboxes into the datasheet so we can populate the table entries with a single click. First, go to the Developer tab, from Insert Options, 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 on the excel checkbox, you can edit text to anything that you'd like. 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.
In the format control dialog box, you will see the control tab at the very 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.
Now we'll move to the cell attendance checkbox. For this one, we will have to insert multiple checkboxes to accommodate all the people. In the Excel ribbon head to the developer tab, and insert a check box. Double click the check box so we can 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, 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 in order 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. Be sure to 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 you can see in the screen shown 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, by hovering your mouse pointer over the column letter and click on it when it becomes a downward arrow. Right click to bring up the context menu the select hide.
Now we will start publishing the following table, here we entered 10 in the whole 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.
Now to check the number of absent students, we will subtract the number of students present from the total students. The formula goes like this.
The value in B15 (students present) will simply 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 check boxes in your spreadsheets! You can use them for aesthetic purposes or add a dash of useful automation to them. 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. Be sure to check Simple Sheets and see all our automated dashboards go to work just for you!
81% of companies across the use Microsoft Excel.
Mastering Excel can increase your earnings up to 4x over the course of your career!
Bottom Line: It pays to master Excel. Take our world-class Excel University and take your spreadsheet skills to the next level.
Get 5 FREE Excel Templates and Trainings
What email do you want us to send your access to?
We hate SPAM. We will never sell your information, for any reason.