Adding checkboxes in Excel can streamline how you handle interactive checklists, to-do lists, and simplify data entry tasks. There are basically two ways to create checkboxes in Excel:

  1. Using Form Controls: This classic method is compatible with most versions of Excel. For step-by-step instructions on how to use this method, click here to read our detailed guide.
  2. Using Excel’s Latest Cell Controls: This innovative feature is available only in the latest Excel updates and allows for quick adjustments directly from the Insert tab

In this blog post, we will cover this new feature, providing all the information you need to efficiently use checkboxes in your Excel projects.

Checkbox Project

Take a look at the image below to see our finished, interactive Checkbox Project we will create using simple steps.

Excel Checkbox Finished Project

Here’s what it offers:

  1. Checkboxes: Mark your progress by ticking off objectives as you complete them.
  2. Progress Overview: Easily see how many objectives you’ve completed and how many you still need to tackle.
  3. Pending Objectives: Get a quick list of what’s still ahead, so you can plan your focus areas.
  4. Completion Indicators: Completed objectives are crossed out and turned green, so you can instantly see your achievements.
  5. Completion Dates: Every time you mark an objective as complete, the date and time are recorded, helping you track your progress over time.
Excel Checkbox Objective Order

How to Add Checkbox in Excel

Let’s start with a list of tasks that you need to track. Adding check boxes in Excel is a great way to visually mark off completed items, keeping you organized and focused.

Excel Checkbox List of Objectives

Inserting Checkboxes to Cells

Adding a checkbox to an Excel cell is straightforward. Here’s how to do it:

  1. Select the Cell: Click on the cell where you want the checkbox to appear.
  2. Insert the Checkbox: Navigate to the ‘Insert’ tab at the top of Excel, find the section labeled ‘Cell Controls’, and select ‘Checkbox’.
Excel Insert Checkbox Button on Ribbon

Once placed, the checkbox will appear in the middle of the selected cell.

Excel Checkbox Added to Cell

In Excel, each checkbox holds a value: “True” for checked, “False” for unchecked. This feature lets you connect checkboxes with functions.

This flexibility opens up many possibilities for automating tasks and organizing data . You might be managing project milestones or tracking inventory. Checkboxes can be a simple but powerful tool in your Excel toolkit.

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

How to Add Multiple Checkboxes

If you need to add checkboxes to several cells at once, you can easily do this in bulk with just a few clicks:

  1. Select the Cells: Click and drag to select all the cells where you want checkboxes.
  2. Insert Checkboxes: Go to the ‘Insert’ tab at the top of Excel. Look for the Insert tab and click on ‘Checkbox’. This will place a checkbox in each of the selected cells.
Excel Bulk Adding Checkboxes

This method is a quick way to handle multiple entries at once, saving you time and ensuring consistency across your data entries.

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.

How to Delete Checkboxes in Excel

Removing a checkbox from your Excel worksheet is as simple as deleting any regular content:

  1. Select the Checkbox: Click directly on the checkbox you want to remove, not just the cell.
  2. Delete: Press the ‘DELETE’ key on your keyboard to remove the checkbox.

You might notice a faint outline of the checkbox remains visible when you select the cell again. This occurs because the checkbox is an object in the cell, rather than typical cell content like text or numbers.

Excel Checkbox Visual Artifact After Deleting

The same happens if you delete the contents of a cell, yet the font, size, and color are not deleted.

If this residual outline—sometimes called a “ghost” image—bothers you, you can remove it completely by clearing the cell’s formatting:

  • Clear Formatting: Go to the ‘Home’ tab, find the ‘Editing’ group, and click on ‘Clear’. Choose ‘Clear All’ to remove any residual formatting from the cell.

This will ensure the cell returns to its default state. It will be free from any visual remnants of the checkbox or other prior formatting.

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.

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
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:

=K5
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.

=$C5

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.

=$C5

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.

=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 in Excel – What are they and how do they work?

Iterative calculations are a method in Excel where the output of a formula is used as the input for the same formula during the next calculation cycle. This process is useful for scenarios where you need the results to build on each other, such as calculating the compounded future value of an investment.

Let’s look at a simple application: creating a date/time stamp that updates itself every time you recalculate your Excel sheet. Here is how it works:

1. Initial Setup

Normally, updating a cell with the current date and time using the NOW function causes the cell to change every time you recalculate the spreadsheet. This isn’t helpful if you want to keep a record of when a particular action was taken.

2. Creating a Static Timestamp

  • To get around this, we set up Excel to use a formula that checks the cell’s previous value and only updates it under certain conditions:
  • If a checkbox linked to the cell is unchecked (False), the cell remains empty.
  • If the cell is currently empty, it displays the current date and time using NOW.
  • Otherwise, it keeps displaying the same date and time it originally displayed, effectively locking in the timestamp when the checkbox was first checked.

3. Limiting Iterations

To prevent Excel from continuously recalculating itself into a loop, we instruct it to stop after a certain number of iterations, like 100. This happens quickly, so it appears almost instantaneous to the user, but it ensures the timestamp stays fixed.

4. Simplifying the Formula

We’ll use the Excel IFS function instead of a complex nested IF formula. IFS is simpler and more efficient for this.

=IFS(C5=FALSE, "", D5="", NOW(), TRUE, D5)

If you want to learn more about using the IFS function in Excel to simplify your formulas, click here for a detailed article. We’ll show you step-by-step how to make the most of this powerful feature!

Excel Timestamp Failure Due to Circular References

Set Conditions:

  1. If cell C5 is not checked (shows “False”), the target cell should stay empty.
  2. If cell D5 is empty, it should fill with the current date and time.
  3. Otherwise, cell D5 should just show the date and time it already has.

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.

💡This change applies to the whole workbook, but only affects this particular 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

As of April 2024, the latest checkbox feature in Excel is available to users who are part of the Microsoft 365 Insider Program and are using the Beta channel. This means it’s still in testing and not yet released to everyone.

What Happens When Sharing with Users on Older Versions?

If you use these new Excel check boxes and send your file to someone using an older version of Excel, the checkboxes won’t appear as you see them. Instead, the cells will show “True” or “False,” which indicates whether the checkbox was checked or not.

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

The good news is that any formulas or setups you’ve made based on these checkboxes will still work just fine, since they depend on this “True/False” data.

However, users with older versions won’t be able to click the cell to change its state from “True” to “False” or vice versa. They’ll need to type it manually, which isn’t as convenient.

While this isn’t ideal, your spreadsheet will still function correctly. This might even encourage some users to update their Excel to the latest version to take full advantage of new features.

To see the “traditional way” of inserting checkboxes (available in all Excel versions but rather less functional), check out this post.

Download the Workbook

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.

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.