Checkbox Project

Excel Checkbox Finished Project

The above image is our finished project.  The project will provide the following features and information:

  1. Checkboxes to indicate the completion status of various learning objectives.
  2. An indication of the number of objectives completed versus the total number of objectives to complete.
  3. A list of objectives that have yet to be completed.
  4. A strikethrough and green font color applied to completed objectives.
  5. Date/time stamps that track when an objective was marked as complete.
Excel Checkbox Objective Order

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.)

Excel Checkbox List of Objectives

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.

Excel Insert Checkbox Button on Ribbon

The result is a checkbox located in the middle of the selected cell.

Excel Checkbox Added to 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.

Excel Checkbox True and False States

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

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.

Excel Bulk Adding Checkboxes

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.

Excel Checkbox Color Examples Checked and Unchecked

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.

Excel Checkbox Visual Artifact After Deleting

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.

Excel Checkbox Completed Task Summary

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.


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
Excel Summary Calculation Area

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
Excel Formula Using a Line Feed

DO NOT FRET: You won’t see the effect of the Line Feed until you enable the Wrap Text option.

Excel 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.

Excel Shape Object Insert

Set the shape’s fill color, border color, and border thickness as desired.

Excel Shape Inserted Into a Sheet

Select the shape object and enter the following into the Formula Bar:

Excel Shape using a Formula to Reference a Cell

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, "")
Excel Filter Function to Display Incomplete Tasks

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

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.

Task List Using Handwriting Font

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.

Excel Shape Basic List Selecting Note Shape

Set the fill color’s transparency to something almost transparent (between 80% and 90% transparent).

Note Shape in Front of Filter Function

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.


This 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.

Excel Changing Font Color in Format Cells Dialog Box
Excel Conditional Formatting Rule Dialog Box

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.


Click the “Format…” button to open the Format Cells dialog box.  On the Font tab, enable the Strikethrough option.

Excel Setting Font Strikethrough in Format Cells Dialog Box

After committing the rules, the effect for checked/unchecked objectives appears as follows.

Conditionally Formatted Text

When examining the rules in the Manage Rules area of Conditional Formatting, it should look like the following.

Conditional Formatting Rules List

For a primer on using Conditional Formatting with formulas, check out this post.

Featured Course

Visually Effective Excel Dashboards

Create eye-catching Excel Dashboards with actionable tips you can use right away.
Learn More
Excel dashboards course cover

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.

Excel Timestamp Example

The “go-to” function for many users to place a date/time stamp into a cell is the NOW() function.


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:

  1. If the checkbox is “unchecked” (i.e., “False”), display nothing in the cell.
  2. If the current date/time cell is empty, display a date/timestamp using the NOW() function.
  3. 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)
Excel Timestamp Failure Due to Circular References
  1. IF C5 is “False” (unchecked), do nothing (“” or empty text)
  2. IF D5 is empty, add the current date/time using the NOW() function
  3. 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.

Excel Enabling Iterative Calculations in Excel Options

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.

Excel formula to Insert a Timestamp

Fill the formula down to the adjacent rows of objectives and test for accuracy.

Excel Timestamp Examples

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.

The pitfalls of using the new checkboxes in older versions of Excel

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.


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.

Excel Download Practice file

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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.