Checkbox Project
The above image is our finished project. The project will provide the following features and information:
- Checkboxes to indicate the completion status of various learning objectives.
- An indication of the number of objectives completed versus the total number of objectives to complete.
- A list of objectives that have yet to be completed.
- A strikethrough and green font color applied to completed objectives.
- Date/time stamps that track when an objective was marked as complete.
Creating Checkboxes in Excel
We start with a list of tasks that need to be completed. (Note: The inclusion of the chicken is optional. Feel free to use any fowl of your choice.)
Adding Checkboxes to Cells
Adding a checkbox to a cell couldn’t be easier. Simply select a cell, then click Insert (tab) – Cell Controls (group) – Checkbox.
The result is a checkbox located in the middle of the selected cell.
Checkboxes will store either a “True” or “False” in the cell depending on the checked or unchecked state respectively.
The great thing about this is that we are free to use any Excel feature or function that utilizes “True/False” responses as part of their workings. You are limited only by your innate creativity when it comes to implementing and using checkboxes.
Featured Course
Excel Essentials for the Real World
Adding Checkboxes in Bulk
If you wish to place checkboxes in several cells, you can do this in bulk by pre-selecting the cells, and then clicking the Insert – Checkbox button.
Formatting Checkboxes
Checkboxes share many of the same cosmetic properties as text: size, color, alignment, etc.
Changing the font color can produce some very pleasing effects.
You can copy/paste formatting checkboxes to other cells to simplify formatting.
Deleting Checkboxes
Removing a checkbox from a cell is no different than deleting text from a cell; select the cell holding the checkbox and press the DELETE key on the keyboard.
However, when you hover your mouse over a cell that held a checkbox, a faded version appears in the cell. This faded version will remain visible if the cell is selected.
This behavior is because checkboxes are just fancy formatting tricks for “True/False” selections.
The same happens if you delete the contents of a cell, yet the font, size, and color are not deleted.
If you don’t want this “ghostly” behavior, clear the cell formatting by selecting Home (tab) – Editing (group) – Clear – Clear All.
Counting Checkbox Selections
At the top of the report, a summary of accomplished items versus total items is to be displayed.
Creating the Summary Formulas
For this, two calculations will be needed: one to count the number of completed (i.e., checked) items, and one to count the total number of items on the list. These two calculations will be placed off to the side of the report and eventually hidden from the viewer.
The formula results will be displayed via a link in a shape object.
For the number of items completed, the following formula will be created that utilizes the COUNTIF function.
K4 = COUNTIF(C5:C13, TRUE)The COUNTIF will count the number of cells containing “True”.
For the number of task items, a COUNTA function will be used. Since the COUNTA function will count the presence of anything in a cell, it only needs to point to the range of checkboxes.
L4 = COUNTA(C5:C13)These formula results (cells K4 and L4) will be concatenated with a title (cell K3) to create the message displayed in the upcoming shape object.
=K3 & K4 & "/" & L4
For an extra bit of visual flair, the text and numbers will be placed on separate rows. To do this, a Line Feed character will be inserted between the K3 and K4 references. The Line Feed character is created using the CHAR function and the ASCII code 10.
=K3 & CHAR(10) & K4 & "/" & L4
DO NOT FRET: You won’t see the effect of the Line Feed until you enable the Wrap Text option.
Adding the Formula Results to a Shape Object
In the upper-left of the report, add a shape object of your choosing by selecting Insert (tab) – Illustrations (group) – Shapes.
Set the shape’s fill color, border color, and border thickness as desired.
Select the shape object and enter the following into the Formula Bar:
=K5
Test the summary by selecting and deselecting various checkboxes.
The columns holding the calculations (columns K and L) can be hidden from the user.
Combining Checkboxes with Formulas
As mentioned earlier, any feature or function in Excel that uses “True” or “False” as part of the process can take advantage of checkbox output.
Creating a Filtered List
The task at hand is to create a list of objectives that have yet to be completed.
To do this, Excel’s FILTER function can be used to take the list of objectives (cells B5 through B13) and reduce the list based on the “False” state of the neighboring cells (C5 through C13).
G5 = FILTER(B5:B13, C5:C13=FALSE, "")
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Creating a “Note”
To give the list of incomplete tasks the appearance of a handwritten note, change the font to a font that is like handwriting.
To make the illusion convincing, Add a “Rectangle: Folded Corner” shape to the sheet and place it in front of the filtered list of objectives. This shape is located in the “Basic Shapes” group of shapes.
Set the fill color’s transparency to something almost transparent (between 80% and 90% transparent).
An advantage of having this note shape in front of the FILTER formula is that it makes selecting the underlying cells more challenging, thus harder for the user to corrupt the project.
Make sure to have the height of the note shape tall enough to accommodate all objectives when no checkboxes are selected. The shape is NOT dynamic and does not resize based on the user’s checkbox selections.
Conditional Formatting and Checkboxes
When a user checks a box, thus completing a task, it would be a nice look to have the entire completed task’s entry change color and have a strikethrough line crossing the entry’s text.
This will be accomplished using Excel’s Conditional Formatting feature.
For the color change rule, select the list of objectives, checkboxes, and (upcoming) timestamps (B5 through D13), then click Home (tab) – Styles (group) – Conditional Formatting – New Rule.
In the New Formatting Rule dialog box, select “Use a formula to determine which cells to format” and enter the following formula.
=$C5This will have all cells on a row examine their respective Column C cell for a “True/False” state. If “True” then formatting is applied; otherwise, no formatting is applied.
Click the “Format…” button to open the Format Cells dialog box. On the Font tab, set the font color to Green.
For the strikethrough rule, select the list of objectives (B5 through B13), then click Home (tab) – Styles (group) – Conditional Formatting – New Rule.
In the New Formatting Rule dialog box, select “Use a formula to determine which cells to format” and enter the following formula.
=$C5Click the “Format…” button to open the Format Cells dialog box. On the Font tab, enable the Strikethrough option.
After committing the rules, the effect for checked/unchecked objectives appears as follows.
When examining the rules in the Manage Rules area of Conditional Formatting, it should look like the following.
For a primer on using Conditional Formatting with formulas, check out this post.
Featured Course
Visually Effective Excel Dashboards
Adding Dynamic Timestamps that Don’t Update
The next objective is to have a date/timestamp displayed to the right of a checkbox when the checkbox is set to a “True” state.
The “go-to” function for many users to place a date/time stamp into a cell is the NOW() function.
=NOW()NOTE: If you only need the date, but not the date and time, you can use the TODAY() function instead of the NOW() function.
The problem with either of these functions is that they automatically update to the latest date/time whenever the worksheet refreshes. We need these dates/times to remain as-is when the checkbox was selected.
To create a locked-down version of the date/time, we’re going to use a feature in Excel you may never have used or even heard of: Iterative Calculations.
Don’t let that scary-sounding name frighten you away. This will be quite easy.
Iterative Calculations – What are they and how do they work?
An iterative calculation uses its result as an input. These are used when you need to perform the same calculation a certain number of times where the results are built upon one another. An example of this might be to calculate the future value of an investment.
Our example is MUCH simpler. We want Excel to insert a date/time stamp, but this formula will be updated the next time the sheet recalculates, causing the previous date/time to be replaced with the current date/time.
Instead of updating the date/time formula, we’ll have the cell read its previous output as input and re-display it as a new output.
Because this would go on forever, Excel steps in and says, “NO! I refuse to perform such insanity” and shuts the formula down.
We will tell Excel to perform the requested iterations but stop after a set number of iterations, like 100.
Because 100 iterations happen in an instant, we get the effect of a (mostly) accurate date/time stamp that will remain locked into its final answer.
The formula will constitute 3 if statements:
- If the checkbox is “unchecked” (i.e., “False”), display nothing in the cell.
- If the current date/time cell is empty, display a date/timestamp using the NOW() function.
- If the world has not burned to the ground (all is going well), display the current cell’s output.
To make things simple, we won’t write a 3-level nested IF, we’ll use the much simpler IFS function.
=IFS(C5=FALSE, "", D5="", NOW(), TRUE, D5)
- IF C5 is “False” (unchecked), do nothing (“” or empty text)
- IF D5 is empty, add the current date/time using the NOW() function
- If you have made it this far (TRUE), re-read cell D5 and redisplay
As you can see, it doesn’t seem to be working correctly. Excel, by nature, does not like or allow for iterative calculation because it could send the system into an infinite loop of recalculations.
We need to allow Excel to do this but restrict it to a small number of iterations. Technically, the formula only requires a single iteration to attain its needed result.
To enable iterative calculations, click File (tab) – Options then select the Formulas category (on the left). Next, activate the “Enable Iterative Calculation” option and set the Maximum Iterations to 1. NOTE: This will be enabled for the entire workbook but only for that workbook.
When the checkbox is set to a “True” state (checked), we see the date/time when the checkbox was selected. Plus, it remains locked because the iterative calculations were limited to a single iteration. Future sheet recalculations will not alter this result.
Fill the formula down to the adjacent rows of objectives and test for accuracy.
Availability and Compatibility with Older Versions of Excel
As of November 2023, the feature has been released to the Beta channel for Microsoft 365 Insiders.
If a file using the new Excel Checkboxes is sent to a user running an older version of Excel, where the new checkboxes are not available, the cells will display simple “True” or “False” text in the checkbox cells.
Because the new checkbox feature is just clever formatting trickery, the underlying “True/False” data remains. All dependent features created by the user that solicit the “True/False” selection will continue to operate normally.
The only downside is the inability to simply click a cell to change its state, rather the user will have to manually type the text “True” or “False”.
It’s not an ideal situation, but at least things still operate normally. This may be just the excuse someone needs to upgrade their version of Excel.
To see the “old way” of inserting checkboxes (available in all Excel versions but rather less functional), check out this post.
Conclusion
Now that Excel has provided us with a more useful version of checkboxes, we can combine this new feature with things like Conditional Formatting, formulas, and other Excel mechanisms to produce highly interactive and visually impressive spreadsheets.
Practice Workbook
Feel free to Download the Workbook HERE. Please note, Excel checkboxes don’t work yet on Excel for the web.
Featured Course
Black Belt Excel Package
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
