Summary

Excel for Microsoft 365 has a checkbox that lives inside the cell: select a range, click Insert > Checkbox, and every cell gets one. The tick is not a picture — the cell holds TRUE or FALSE, which is what lets you count completed tasks with a formula and strike them out automatically. This article covers the native control, the older Developer-tab check box that perpetual versions of Excel still use, and the linked cell that trips everyone up.

In this guide

• 1. Insert Checkboxes Across a Whole Range
• 2. Check What the Cell Actually Holds
• 3. Count the Ticked Boxes with COUNTIF
• 4. Strike Through Completed Tasks with Conditional Formatting
• 5. Untick or Remove Checkboxes
• 6. Add a Check Box in Excel 2016 to 2024 with the Developer Tab

Before You Start

• Excel for Microsoft 365 on Windows or Mac for the in-cell checkbox in Steps 1 to 5. Excel 2016, 2019, 2021 and 2024 use the Developer-tab control in Step 6 instead.
• Edit permission on the workbook.
• No admin rights required.

Instructions

1. Insert Checkboxes Across a Whole Range

Excel's in-cell checkbox fills an entire selection in one action, so a fifty-row checklist takes the same three clicks as a single cell.

1. Open your worksheet and type your task names down column A, starting in A2. Put the heading Task in A1 and Done in B1.
2. Select the cells that need checkboxes. Click B2 and drag down to B9; the selected block highlights.
3. Open the Insert tab on the ribbon.
4. Click Checkbox in the Cell Controls group. An empty square appears centred in every cell you selected.
5. Click one of the squares. It fills with a tick. Click it again and the tick clears.

Verification: Click a ticked cell once to select it, then read the formula bar above the column headers. It reads TRUE.

Note: No Checkbox button on the Insert tab means your Excel does not have this control. It ships in Excel for Microsoft 365 on Windows and Mac; the perpetual releases — Excel 2016, 2019, 2021 and 2024 — use the Developer-tab check box in Step 6. To confirm which you are running, click File > Account and read the version under About Excel.

Tip: Select several checkbox cells at once and press Space to toggle all of them together. Handy for clearing last week's list before you reuse it.

2. Check What the Cell Actually Holds

The tick is not an object sitting on the sheet — it is how Excel draws the values TRUE and FALSE, and that single fact is what makes every step below possible.

1. Click a ticked checkbox cell and read the formula bar. It shows TRUE.
2. Click an unticked one. The formula bar shows FALSE.
3. Click an empty checkbox cell, type TRUE, and press Enter. The box ticks itself — you set the value and the checkbox is the display.
4. To see the value in words, click cell C2 and enter:

=IF(B2,"Done","Outstanding")

5. Press Enter. C2 reads Done when the box beside it is ticked and Outstanding when it is not.

Note: Because the value is a real TRUE or FALSE, the column sorts and filters like any other. Click Data > Filter and the dropdown on B1 offers TRUE and FALSE as filter values — see How to Sort and Filter Data in Excel.

3. Count the Ticked Boxes with COUNTIF

One formula turns a column of boxes into a live progress count that updates the moment someone ticks something.

1. Click an empty cell below your list — B11 in this example.
2. Enter the formula that counts the ticks:

=COUNTIF(B2:B9,TRUE)

3. Press Enter. The cell shows how many boxes are ticked, and the number changes as you tick and untick.
4. For the outstanding count, click B12 and enter:

=COUNTIF(B2:B9,FALSE)

5. For a percentage complete, click B13 and enter:

=COUNTIF(B2:B9,TRUE)/COUNTA(A2:A9)

6. The result appears as a decimal such as 0.375. Select the cell, open the Home tab, and click the % button in the Number group. It now reads 38%.

Note: =SUM(B2:B9) over a checkbox column returns 0. SUM ignores logical values inside a range reference, so counting is the job of COUNTIF. If the percentage cell shows a date instead of a number after step 6, the cell carried a date format from something else — Understanding Number Formats in Excel explains why the value and its display are separate things.

Tip: Wrap the two counts into one readable line: =COUNTIF(B2:B9,TRUE)&" of "&COUNTA(A2:A9)&" done" shows 3 of 8 done.

4. Strike Through Completed Tasks with Conditional Formatting

A ticked box can drive the look of the row beside it, so a finished task crosses itself out the moment it is ticked.

1. Select A2:A9 — the task names, not the checkboxes.
2. On the Home tab, click Conditional Formatting in the Styles group, then click New Rule near the bottom of the menu. The New Formatting Rule dialog opens.
3. Under Select a Rule Type, click Use a formula to determine which cells to format. A Format values where this formula is true box appears below.
4. Click into that box and enter:

=$B2=TRUE

5. Click Format at the bottom right. The Format Cells dialog opens.
6. Open the Font tab, tick Strikethrough under Effects, and set Color to a grey if you want the finished rows to recede. Click OK.
7. Click OK again to close New Formatting Rule. Every task whose box is ticked strikes through immediately, and unticking a box restores it.

Note: The dollar sign in $B2 is doing real work. It locks the rule to column B while leaving the row free, so row 5 checks the box in B5 rather than all rows checking B2. If the wrong rows strike out, that reference is the reason — see Understanding Absolute and Relative Cell References in Excel.

Tip: To strike the checkbox cell along with the task, select A2:B9 before you start at step 1. The rule formula stays exactly the same.

5. Untick or Remove Checkboxes

Clearing the ticks and deleting the boxes are two separate actions, and Delete performs them in that order.

1. Select the checkbox range B2:B9.
2. Press Delete. Every tick clears and the empty boxes stay in place. Nothing but the ticks is affected — press Ctrl + Z to bring them straight back.
3. Press Delete a second time. The checkboxes themselves are removed and the cells go blank. Any COUNTIF pointing at the range now returns 0 because there is nothing left to count; Ctrl + Z restores the boxes and their values.
4. To drop the boxes but keep the answers, select the range, open the Home tab, click Clear in the Editing group at the right end of the ribbon, and choose Clear Formats. The cells now show the words TRUE and FALSE, and every formula built on them keeps working.

Tip: Reusing a checklist next week? Stop at step 2. One Delete resets the whole column to unticked and leaves the boxes ready to go.

6. Add a Check Box in Excel 2016 to 2024 with the Developer Tab

Perpetual versions of Excel have a different control — a small object that floats above the grid and writes its answer into a cell you nominate.

First, switch on the tab that holds it:

1. Click File in the top-left corner, then Options at the bottom of the left sidebar. The Excel Options dialog opens.
2. Select Customize Ribbon in the list down the left side.
3. In the Main Tabs list on the right, tick Developer.
4. Click OK. A Developer tab appears on the ribbon. Turning it on adds a tab and changes nothing about your workbook — untick the same box to hide it again.

Now place the control:

5. On the Developer tab, click Insert in the Controls group. A gallery drops down with Form Controls across the top.
6. Click the check box icon under Form Controls. The pointer changes to a crosshair.
7. Click inside cell B2. A check box appears with the label Check Box 1 beside it.
8. Right-click the check box, choose Edit Text, delete the label text, then click any empty cell on the sheet. Only the box remains.
9. Right-click the check box again and choose Format Control. The Format Control dialog opens.
10. Open the Control tab, click into the Cell link box, then click cell C2 on the sheet. The box fills with $C$2.
11. Click OK, then tick the check box. Cell C2 shows TRUE. Untick it and C2 shows FALSE.

The linked cell is the part that confuses everyone, so here is what is actually happening. The check box is a picture-like object drawn on top of the sheet. It is not in cell B2; it is merely sitting over it, and it holds no value of its own. The only place its state is recorded is the cell you named in Cell link. Every formula you write points at that cell — never at the box.

Two consequences follow, and both catch people out:

• Copy a check box down the column and its Cell link comes with it. Ten copies all write to $C$2, so ticking any one of them flips the answer for all ten. Open Format Control on each copy and set Cell link to its own row — $C$3, $C$4, and so on.
• The linked column is ugly, and hiding it is fine. Right-click the column C header and choose Hide. The values keep working; hidden columns still calculate. Full detail in How to Hide and Unhide Rows and Columns in Excel.

12. Count the results against the linked column rather than the boxes:

=COUNTIF(C2:C9,TRUE)

Note: Conditional formatting works the same way here, pointed at the linked column: select A2:A9 and use =$C2=TRUE as the rule formula in Step 4.

Troubleshooting

WARNING

Developer-tab check boxes do not survive Excel for the web. Open a workbook containing them in a browser and Excel warns you; edit it there and the controls are removed from the file, taking their Cell link settings with them. Keep those workbooks in the desktop app, or rebuild them with the in-cell checkbox from Step 1, which is stored in the cell and has nothing to strip out.

Symptom / Error	Potential Cause	Solution
No Checkbox button on the Insert tab	Perpetual version of Excel	The in-cell control ships in Excel for Microsoft 365 only. Check File > Account > About Excel, then use the Developer-tab route in Step 6.
No Developer tab on the ribbon	Hidden by default	Turn it on under File > Options > Customize Ribbon — Step 6. It is a display setting; nothing in the workbook changes.
=SUM() over the checkbox column returns 0	SUM ignores logical values	Count them instead with =COUNTIF(B2:B9,TRUE) — Step 3.
Every check box ticks at once	Copied Form Controls share one Cell link	Right-click each copy, choose Format Control > Control, and point Cell link at its own row — Step 6.
Ticking a Form Control changes no formula	Cell link was never set	Right-click the box, choose Format Control > Control, and click a cell into the Cell link box — Step 6.
Conditional formatting strikes the wrong rows	Rule reference not column-locked	Use =$B2=TRUE, with the dollar sign before the column letter only — Step 4.
Delete clears the tick but leaves the box	Delete clears the value first	Press Delete a second time to remove the control — Step 5.
Checkbox cells are greyed out and will not tick	The sheet is protected	Unprotect it if you own it. If the password is not yours, ask IT to unprotect the sheet, or to allow Edit objects for your account, and include the file path and sheet name. See How to Protect a Sheet or Workbook in Excel.

Related Articles

• How to Use Data Validation and Input Tips in Excel
• How to Create a Drop-Down List in Excel
• How to Use Conditional Formatting in Excel
• How to Insert a Signature Line or Checkbox in Word

Did this solve your problem?