Download the practice workbook 👉 HERE and follow along.
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:
- 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.
- 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 list using Excel Checkboxes.
Here’s what it offers:
- Checkboxes: Mark your progress by ticking off objectives as you complete them.
- Progress Overview: Easily see how many objectives you’ve completed and how many you still need to tackle.
- Pending Objectives: Get a quick list of what’s still ahead, so you can plan your focus areas.
- Completion Indicators: Completed objectives are crossed out and turned green, so you can instantly see your achievements.
- Completion Dates: Every time you mark an objective as complete, the date and time are recorded, helping you track your progress over time.
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.
Inserting Checkboxes to Cells
Adding a checkbox to an Excel cell is straightforward. Here’s how to do it:
- Select the Cell: Click on the cell where you want the checkbox to appear.
- Insert the Checkbox: Navigate to the ‘Insert’ tab at the top of Excel, find the section labeled ‘Cell Controls’, and select ‘Checkbox’.
Once placed, the checkbox will appear in the middle of the selected cell.
How Checkboxes Work in Excel
Each checkbox in Excel has a value:
- TRUE when it’s checked.
- FALSE when it’s unchecked.
This makes checkboxes a flexible tool. You can link them to formulas, organize data, and even automate tasks.
Featured Course
Excel Essentials for the Real World
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:
- Select the Cells: Click and drag to select all the cells where you want checkboxes.
- 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.
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 in Excel aren’t just functional—they can also look great! You can customize their appearance to match your spreadsheet’s style.
You can copy/paste formatting checkboxes to other cells to simplify formatting.
How to Delete Checkboxes in Excel
Removing a checkbox in Excel is straightforward. Whether you need to delete one or several, follow these simple steps.
- Select the Checkbox: Click directly on the checkbox you want to remove, not just the cell.
- Delete: Press the ‘DELETE’ key on your keyboard to remove the checkbox.
- If the checkbox is checked, press ‘DELETE’ again (the first ‘DELETE’ unchecks it).
💡 Microsoft has improved how checkboxes work. Now, pressing DELETE fully removes a checkbox without leaving behind a “ghost” outline..
Optional: Keep the TRUE/FALSE Values
If you want to remove the checkbox but keep the cell’s TRUE/FALSE values:
- Select the checkbox or the cell it’s in.
- Go to the Home tab on the ribbon.
- Click Clear > Clear Formats.
This removes the checkbox while retaining the linked cell’s data.
Counting Checkbox Selections
Want to track progress in a report? You can create a summary at the top of your Excel sheet showing how many tasks are completed versus the total number of tasks.
Follow these steps to set it up.
Step 1: Create the Summary Formulas
To count checkbox selections, you’ll need two formulas:
- 1. Count Completed Items:
- Use the COUNTIF function to count how many checkboxes are checked. Checked boxes return the value TRUE. Here’s the formula:
=COUNTIF(C5:C13, TRUE)This counts the number of TRUE values in the range C5:C13 (the cells linked to your checkboxes).
- 2. Count Total Items:
- Use the
COUNTAfunction to count all the checkboxes. This formula works even if some boxes are unchecked:
- Use the
=COUNTA(C5:C13)Step 2: Display the Formula Results
To display the summary as a message, combine the results into a single text string:
- Place the formulas in cells
K4andL4. - Add a label in
K3, like"Tasks Completed:". - Use the following formula to create a summary message:
=K3 & K4 & "/" & L4
Optional: Add a Line Break for Better Formatting
To place the text and numbers on separate lines, insert a Line Feed character using the CHAR function with ASCII code 10:
=K3 & CHAR(10) & K4 & "/" & L4
💡 Tip: Turn on the Wrap Text option in the Format Cells menu to see the line break.
Step 3: Add the Summary to a Shape Object
To make the summary stand out, add it to a shape:
- Go to Insert > Shapes and choose a shape.
- Customize the shape’s fill color, border color, and thickness.
- Select the shape, then type your formula (e.g.,
=K5) into the Formula Bar.
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
Checkboxes in Excel don’t just look great—they can also power dynamic features like summaries and filtered lists.
Let’s explore how to link checkboxes with formulas and create a filtered list of incomplete tasks.
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 Excel’s Essential Modern Functions
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 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!
Set Conditions:
- If cell C5 is not checked (shows “False”), the target cell should stay empty.
- If cell D5 is empty, it should fill with the current date and time.
- 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.
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
At the end of June 2024, Microsoft began the rollout of this feature to the Current Channel (more on release channels in Microsoft 365). With time, it will be available to all Excel for Microsoft 365 users.
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 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
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master Checkboxes in Excel with hands-on examples.
Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
