How to Add Checkboxes in Excel and Count Them

A checkbox in Excel turns a cell into a tick box you can click on and off. Behind the box, the cell holds TRUE or FALSE, so you can count ticked boxes with COUNTIF and show how much of a list is done. This guide covers both ways to add checkboxes, for Microsoft 365 and for older versions, and how to count them, total them and color finished rows.

How to add a checkbox in Excel (Microsoft 365)#

In Excel for Microsoft 365 and Excel for the web, checkboxes live inside cells. They behave like normal values: you can copy them, fill them down and sort by them.

1. Select the cells that should get a box, for example C2:C7.
2. Choose Insert > Checkbox.
3. Click a box to tick it, or select the cell and press Space.

Each ticked box stores TRUE and each unticked box stores FALSE. Click a cell and look at the formula bar to see the value. If the cells already held TRUE or FALSE, the boxes pick those values up. Empty cells start as FALSE.

To add more boxes later, select a cell with a checkbox and fill down with Ctrl+D (Mac: ⌘D), or copy and paste it. Excel 2021, Excel 2019 and earlier don't have this button. Use the Developer tab method below instead.

How to add a checkbox in older versions of Excel#

Older desktop versions use Form Control check boxes. These float on top of the sheet rather than sitting in a cell, and they need a linked cell before formulas can read them.

Turn on the Developer tab#

• Windows: File > Options > Customize Ribbon, tick Developer, click OK.
• Mac: Excel > Preferences (or Settings) > Ribbon & Toolbar, tick Developer, click Save.

Draw and link a check box#

1. Choose Developer > Insert > Check Box (Form Control).
2. Click and drag on the sheet to draw it. Edit or delete the label text.
3. Right-click the box and choose Format Control.
4. On the Control tab, click in Cell link, click the cell next to it (for example C2), and click OK.

The linked cell now shows TRUE or FALSE as you tick and untick. Without a cell link, the box is only a picture: formulas can't see it. For many rows, set up one box, copy it down, and update each cell link. That is slow, which is the main reason the newer in-cell checkboxes are easier.

How to count checked boxes with COUNTIF#

Use COUNTIF with TRUE as the criteria to count ticked boxes. Here is an event task list:

To count finished tasks:

=COUNTIF(C2:C7,TRUE)

The result is 4. Write TRUE without quotes. It is a logical value, not the text "TRUE". To count the tasks still open, use FALSE:

=COUNTIF(C2:C7,FALSE)

That returns 2. The COUNTIF function guide lists the full syntax, and how to use COUNTIF shows more types of criteria.

How to show percent complete#

Divide the ticked boxes by the number of tasks. COUNTA counts the filled task names:

=COUNTIF(C2:C7,TRUE)/COUNTA(A2:A7)

The result is 0.666.... Format the cell as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%) and it shows 67%. Using COUNTA on the task column means the total updates when you add a task.

To count one person's finished tasks, add a second condition with COUNTIFS:

=COUNTIFS(B2:B7,"Priya",C2:C7,TRUE)

Priya has 2 ticked tasks. The comparison of COUNTIF vs COUNTIFS explains when you need the second function.

Use checkbox values in IF and other formulas#

Because a checkbox is TRUE or FALSE, you can use it anywhere Excel expects a test. A test is any expression that gives TRUE or FALSE, like B2>100.

To show a status label:

=IF(C2,"Done","Open")

IF(C2, ...) is the same as IF(C2=TRUE, ...). If C2 is ticked, the result is "Done".

To add up only the costs of ticked items, use SUMIF with TRUE as the condition. If D2:D7 holds costs:

=SUMIF(C2:C7,TRUE,D2:D7)

This adds the costs on ticked rows only. It's useful for a packing list, a shopping list or an expense claim where you tick what's approved. You can learn how TRUE and FALSE work in the lesson on TRUE, FALSE and comparisons.

How to color or strike through finished rows#

Conditional formatting can grey out or strike through a row when its box is ticked.

1. Select the rows, for example A2:C7.
2. Choose Home > Conditional Formatting > New Rule.
3. Pick Use a formula to determine which cells to format.
4. Type =$C2=TRUE.
5. Click Format, tick Strikethrough and choose a grey font. Click OK twice.

The $ before C keeps every cell in the row looking at the checkbox in column C. Tick a box and the whole row is crossed out.

Common checkbox problems#

Most checkbox problems come from the value behind the box, not the box itself.

• COUNTIF returns 0. You typed "TRUE" in quotes and the cells hold logical values, or a Form Control box has no cell link. Remove the quotes, or set the link.
• Delete doesn't remove the box. For in-cell checkboxes, the first press of Delete unticks a ticked box. Press it again to remove the checkbox.
• Boxes don't move with sorting. Form Control boxes float above the cells and can end up on the wrong row after a sort. In-cell checkboxes move with their rows.
• Typing over a box. If you type text into a checkbox cell, the box shows nothing useful. Keep checkbox columns for TRUE and FALSE only.

Checkboxes also work well for daily logs. The attendance tracker in Excel uses codes, but you could use one checkbox per day instead and count TRUE values the same way.

Practise it#

Try the exercise on this page: count the ticked tasks with COUNTIF and TRUE. Then practise COUNTIF in a live spreadsheet, where you count text, numbers and conditions, and practise TRUE, FALSE and comparisons to see how logical values work inside formulas.