Checkboxes and a self-counting packing list
6 minutes · 4 steps · free, no sign-up
A checkbox is a cell that is either ticked or not. Behind the scenes a ticked box holds <code>TRUE</code> and an unticked box holds <code>FALSE</code>, which means formulas can count them. You will build a packing list that tells you how far through you are.
- Turn cells into checkboxes
- Tick and untick them
- Count ticked boxes with COUNTIF
- Show progress as a percentage
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceWhat you will use
TRUE / FALSE what a checkbox holds
=COUNTIF(B2:B6,TRUE) how many are ticked
=COUNTIF(B2:B6,FALSE) how many are left
=D2/COUNTA(A2:A6) share completedThe key ideasSummary: Checkboxes and a self-counting packing list
A checkbox is just a cell with a special display. Tick it and the cell holds TRUE, untick it and it holds FALSE. Because they are real values, COUNTIF(range, TRUE) counts the ticks, and an IF can react to one.
Dividing the ticks by the number of items gives progress, which you can show as a percentage. This pattern, a list with a tick column and a count, is behind task trackers, shopping lists and project checklists.
Watch out forCommon mistakes
- Typing TRUE by hand next to a checkbox. The display and the value can then disagree. Click the box instead.
- Counting with COUNTA.
COUNTAcounts every box, ticked or not, because both hold a value. UseCOUNTIF(range,TRUE)for the ticked ones. - Forgetting to format the result. Progress as a fraction such as 0.4 is correct but unfriendly. Format it as a percentage.
Next stepTry it in the real Google Sheets
In Google Sheets use Insert → Checkbox. You can pick your own values (for example "Yes" and "No") under Data → Data validation. Combined with conditional formatting, a ticked row can turn grey automatically.