Excel Tutorials
Add real interactive checkboxes to a sheet, react to whether they're checked with IF, and count how many are checked. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
A checkbox in Excel is an actual clickable control sitting in a cell, checked or unchecked, that ties to a TRUE or FALSE value you can reference in a formula. That makes it possible to build interactive checklists, task trackers or approval sheets where a formula reacts automatically to whether something's been checked off.
Combined with IF and COUNTIF, checkboxes turn a static list into something that actually calculates, showing a live count of what's done, or flagging a row once its box is checked.
Step-by-step, matching the video above.
Select a cell, go to the Insert tab, and click Checkbox (in newer Excel) or use Developer > Insert > Checkbox in the Form Controls section in older versions.
By default a checkbox already writes TRUE or FALSE directly into the cell it's placed in when checked or unchecked.
Select the cell with the checkbox and drag the fill handle down to add the same interactive checkbox to every row.
In another cell, type =IF(A2=TRUE,"Done","Pending") to display different text depending on whether the checkbox in A2 is checked.
Use =COUNTIF(A2:A20,TRUE) to count how many checkboxes in that range are currently checked.
=COUNTIF(A2:A20,FALSE) gives you the opposite count, everything not yet checked off.
="Completed: "&COUNTIF(A2:A20,TRUE)&" of "&COUNTA(A2:A20) builds a live-updating summary sentence showing progress.
| Task | A: Checked? | B: =IF(A=TRUE,"Done","Pending") |
|---|---|---|
| Book venue | ☑ TRUE | Done |
| Send invites | ☑ TRUE | Done |
| Order catering | ☐ FALSE | Pending |
| Confirm AV setup | ☐ FALSE | Pending |
| Print name tags | ☑ TRUE | Done |
=COUNTIF(A2:A6,TRUE) on this list returns 3. A summary formula like ="Completed: "&COUNTIF(A2:A6,TRUE)&" of "&COUNTA(A2:A6) would display "Completed: 3 of 5" and update live as boxes get checked.
A single checkbox is useful on its own, but real checklists usually need to react to more than one condition at once.
=IF(AND(A2=TRUE,B2=TRUE),"Ready","Not Ready") only returns "Ready" if both A2 and B2 are checked.
=IF(OR(A2=TRUE,B2=TRUE),"In Progress","Not Started") returns "In Progress" if either box is checked.
AND and OR inside an IF work the same way here as with any other condition, see nested IF, OR and AND statements for the full breakdown.
Some checkbox insert methods create a separate linked cell rather than writing directly into the cell it sits on, double-check which cell your formulas should actually reference.
=IF(A2="TRUE",...) won't match a real checkbox value. Use =IF(A2=TRUE,...) without quotes.
COUNTA(A2:A20) counts every cell with any value, including FALSE. Use COUNTIF with TRUE specifically if you only want the checked count.
A checklist shouldn't need a formula to know what's done.
Free 14-day trial. No credit card required.
Go to the Insert tab and click Checkbox in newer versions, or use Developer > Insert > Checkbox under Form Controls in older versions.
TRUE when checked, FALSE when unchecked, a real boolean value you can reference directly in formulas.
Use COUNTIF, like =COUNTIF(A2:A20,TRUE), to count checked boxes in a range.
Use IF, like =IF(A2=TRUE,"Done","Pending"), to display different text depending on the checkbox's state.
Yes, combine AND inside IF, like =IF(AND(A2=TRUE,B2=TRUE),"Ready","Not Ready").
Make sure you're comparing to TRUE without quotes, =IF(A2="TRUE",...) won't match a real checkbox value.
Yes, select the cell with the checkbox and drag the fill handle down like any other cell.
Every business starts with a spreadsheet. Updoot is where you scale past it, status updates itself automatically.
Start Your Free Trial →