How to use checkboxes in Excel. In this Excel tutorial, we will explore how to insert checkboxes
into this table over here, and then use the data of whether the box is checked or unchecked.
Here’s a preview of what is possible in Excel using checkboxes: you can have the formatting
of table rows change automatically when a box is checked, and you can build checklist
completion summaries and charts that use the checkbox data.
The easiest way to add checkboxes in Excel is through the Developer tab. If you don’t
already have it, the Developer tab can be enabled by going to the top of your Excel
screen, right-clicking, and selecting “customize the ribbon”. Check the box next to “Developer”
at the bottom right, and click OK. Congratulations, you are now an Excel Developer!
On the Developer tab, we click Insert – Form Controls – Check Box. This brings you into
some sort of drawing mode. Let’s draw an area that is roughly the same as cell C2.
We can now edit the standard text, and change it to “Done”. Or delete the text altogether,
as the purpose of a checkbox is pretty clear to most people.
Let’s do some more formatting. This is a bit tricky at first, as the checkbox is superimposed
on top of the cell itself. If you click on cell C2 with your left mouse button, you change
the checkbox from unchecked to checked, which is not what we want to do yet. If you click
on cell C2 with your right mouse button, you can go to format control, and then in the
control tab switch from a standard checkbox to a 3-D shaded checkbox. This will become
visible once you click OK. Now let’s narrow the checkbox object and center it in the middle
of the cell. This solves our left-click issue as well, we can now either select the cell
or select the checkbox. Let’s copy over the checkbox from cell C2
into cells C3 through C6. Next, we right-click on the checkbox in cell C2, go back to format
control, and provide a cell link. We link this particular checkbox to cell D2. Now this
seems to be a bit counterintuitive. Normally, with regular Excel functions, you would go
to cell D2 and insert a formula to pull data from cell C2. In the world of form controls,
you are telling the checkbox in C2 to push data to D2. Click OK, and nothing seems to
be happening. However, once you check the checkbox in C2, the value in D2 becomes TRUE.
And once you uncheck C2, the value in D2 becomes FALSE. As you can see by clicking on D2, there
is no formula here, TRUE and FALSE are pushed from C2 to D2, rather than pulled from there.
We repeat the cell link procedure for each of the checkboxes and its related row in column
D. But wait, can’t we just copy over cell C2 down the rows after inserting the cell
link, and have this update automatically? No, sadly you can’t. You have to manually
assign a cell link to each of the checkboxes in Excel. I have tried every possible way
I could think of to make the cell link update automatically. I have read every possible
source on checkboxes and form controls in Excel. There is no other way, as far as I
know. If you do find a way, please let me know in the comments. For now, it’s not
a big deal to assign the cell links to each checkbox manually, but if you have an Excel
table with one hundred or more checkboxes it becomes very labor intensive.
Let’s check each box on and off to activate the form control, and have FALSE show up in
column D for each row. Here comes the fun part of using checkboxes
in Excel! Let’s have the formatting of table rows change automatically when a box is checked.
Select cells A2 through C6, go to the Home tab, conditional formatting, new rule, use
a formula to determine which cells to format. Below “format values where this formula
is true”, type =$D2, then click Format. What you could do here is to select the Strikethrough
effect. Click OK, and then OK again. For any row where the checkbox is checked, the text
gets a strikethrough. Personally, I don’t really like strikethroughs on a task list,
I am actually pretty proud if I get a task done, and would like to display it prominently
when I have completed it. Let’s go back to conditional formatting, manage rules, edit
rule, format, disable strikethrough, go to Fill, and select bright green. Click OK three
times. Now each row that is checked turns green. The formatting of table rows changes
automatically when a box is checked. Time to add reporting functionality that uses
the Excel checkbox data: summaries and charts. Let’s add a table in cells A9 through C12,
with “Complete” in row 10 and “Not started” in row 11. The column header is Minutes in
B9 and Number in C9. In B10, we want to add up how many minutes of video material we have
made. On the Home tab, go to Functions – More Functions, and type SUMIF. The range of cells
we want to evaluate is D2 through D6, let’s lock columns and rows with a $ sign. In criteria
we type TRUE. Sum range is the actual cells to sum: B2 through B6, with the row numbers
locked with a $ sign. Click enter, and we see the current number of minutes completed
show up. We can copy the formula from B10 to B11, and just change the criteria part
from TRUE to FALSE. Then sum the total number of minutes in B12, to make sure it is equal
to B7. In the number column, we do something similar.
However, here we use the Excel COUNTIF function instead of a SUMIF. Functions – More Functions,
type COUNTIF. The range of cells we want to evaluate is D2 through D6, let’s lock columns
and rows with a $ sign. In criteria we type TRUE. Let’s copy over the formula from C10
to C11, and change criteria from TRUE to FALSE. Then sum the number of videos in C12. Let’s
check and uncheck the checkboxes to verify that the table updates correctly, and that
the totals stay the same. That takes care of the absolute numbers, now
let’s add a table in our Excel spreadsheet that reports on the relative progress. We
copy over the existing summary table, and delete the formulas. In cell B15, we type
=B10/B$12. We format this as a percentage. Then we copy over the formula to the others
cells, and update the formatting of the totals to show percentages as well. If we complete
the two videos with the longest video length, then that represents 40% of the total number
of videos (2 out of 5), but 59% of the total video length (34 out of 58).
We can now hide the TRUE/FALSE column, as these values are only used for calculations
in the background, which the user does not need to see.
Let’s insert two pie charts to visualize the checkbox data in Excel. Select cells A15
through B16, go to the Insert tab, pie charts, doughnut. Move and resize the chart a bit
so it covers 5 columns: F through J. Let’s make the chart title “Progress in minutes
of material”. Let’s make the color of the complete section green, and the color
of the not started section red. The easiest way to do that is to click each element of
the legend separately, then right-mouse click, fill. If you select the entire chart, and
click on the big plus sign on the top right, you can activate Data Labels. We want a Data
Callout, but just for the percentages, not for the category names. If we change the status
of any checkbox, both the tables as well as the graph update.
We copy and paste the chart, and align the two charts through the Format menu - Align.
We change the link for the source data that feeds the chart, from column B to column C.
Let’s use the same colors as we did on the other chart: complete in green, not started
in red. Add the data labels: click on the big plus sign, activate Data Labels, Data
Callout, percentages, but not category names. Update the title to “progress in number
of videos”. Now check, uncheck, check, uncheck, and see everything update automatically. That’s
some of the beauty and magic of using checkboxes in Excel!