To add checkboxes in Google Sheets, select the cells you want to use and choose Insert, then Checkbox on a computer. The default values are TRUE for checked and FALSE for unchecked. To count checked boxes in C2:C20, use =COUNTIF(C2:C20,TRUE).
For a tracker that includes unused rows, count only rows with an actual task. A checkbox shows whether a specific condition is true. Use a dropdown instead when you need to distinguish several states such as Not started, In progress and Done.
- Apply checkboxes to the entry cells below the header.
- Use TRUE and FALSE for default checkbox formulas.
- Count real tasks separately from unused rows.
- Update formulas if you choose custom checked or unchecked values.
- Test a checked task, an unchecked task and a blank row.
Add a checkbox to a cell or a range
Open a spreadsheet on a computer. Put the task names in column A and a heading such as Complete in C1. Select C2:C20 and choose Insert, then Checkbox. Click one box and watch it change between unchecked and checked. Test another row to confirm you applied the rule to the intended range.
The visible box represents a cell value. In the default setup, the checked state corresponds to TRUE and unchecked corresponds to FALSE. You can reference that value from another formula. A status icon pasted as text may look like a checkbox and behave differently in calculations. Use the built-in checkbox control when the cell should hold a value you can test.
Keep the header and notes outside the range
Apply the rule only where a person should be able to tick an item. Leave the heading intact. If the sheet has another table below the tracker, avoid applying the rule to that unrelated area. Check a row near the bottom before sharing. Decide whether future task rows should already have checkboxes.
Ask one clear question per checkbox
A checkbox labelled Complete should answer whether the task has been completed. If you also need to record whether a reviewer approved it, use a separate field. If the reader needs three or more mutually exclusive states, a dropdown can express them more clearly. A binary field is most useful when everyone agrees what checking it means.
Count checked boxes with COUNTIF
With default checkboxes in C2:C20, use the following formula in a separate summary cell.
=COUNTIF(C2:C20,TRUE)If you check two boxes, the expected count is two. Uncheck one and verify the count drops by one. Use a range that covers the rows the report should track. The formula does not need to look at the visible symbol or colour.
Count only checked rows with a task
If you put checkboxes into future blank rows, a stray check in an unused row can inflate the result. Use the task name or another required field as a condition. In this example column A contains one nonblank task name per actual task.
=COUNTIFS(A2:A20,"<>",C2:C20,TRUE)Test three rows: a real checked task, a real unchecked task and an empty row with a checked box. Only the first should count. Choose a more reliable required key if a task can legitimately have an empty name.
Find incomplete items without counting empty rows
An unchecked box is meaningful when there is a real task to complete. To count real tasks explicitly marked FALSE, use the following formula.
=COUNTIFS(A2:A20,"<>",C2:C20,FALSE)Check how your sheet should handle missing validation or imported values. If every real task must be either checked or unchecked, verify that each row actually has the intended checkbox rule. If an imported row has a blank or invalid state, flag it for review instead of assuming it means complete.
Calculate a completion percentage
Divide the number of checked real tasks by the number of real tasks. If column A holds a plain task name for every real row, the following example uses a nonblank count as its denominator.
=IF(COUNTIF(A2:A20,"<>")=0,"",COUNTIFS(A2:A20,"<>",C2:C20,TRUE)/COUNTIF(A2:A20,"<>"))Format the result as a percentage. If four real tasks exist and three are checked, the expected result is 75%. A blank result is deliberate here when there are no tasks. You can use a different display if the report requires it. If column A contains formulas returning empty text or identifiers with other conventions, test the denominator against actual rows.
Decide what belongs in the denominator
A project may include optional tasks, cancelled tasks or tasks planned for next month. If the percentage should cover required current tasks only, apply the same scope to the numerator and denominator. Check one optional checked task and one required unchecked task. If the two counts use different scopes, the percentage can be misleading.
Keep the count visible beside the percentage
A display of 100% can mean one of one or twenty of twenty. Showing completed and total counts makes the result easier to interpret. Check that both refer to the same task range and criteria. If a new row is added beyond row 20, extend the formulas consistently.
Use a checkbox value to filter or total records
A checkbox can feed a report without changing the original table. For a task sheet with names in column A and default checkboxes in C, use FILTER to show checked real tasks.
=IFNA(FILTER(A2:C20,A2:A20<>"",C2:C20=TRUE),"No checked tasks")Check a known included row and an unchecked row. Keep enough room for the result to expand. This simple example displays a message for #N/A. If source formulas can contain #N/A, inspect unexpected results before treating the message as proof that no tasks matched.
Add only amounts from approved records
Suppose column D holds hours and the checkbox in C means Verified. Sum only verified records with a nonblank task name.
=SUMIFS(D2:D20,C2:C20,TRUE,A2:A20,"<>")Check a verified task with a known value and an unverified one. Make sure hours are numeric and use consistent units. A verified flag answers whether the row belongs in the total. It does not check whether someone entered the correct number.
Use the checkbox for formatting without making colour carry the meaning
A conditional-formatting rule can format completed rows. For a range beginning in row two, a rule such as =$C2=TRUE uses column C to decide what to format on each row. Keep the status readable through the checkbox and clear column label. Verify the row above and below a checked task to catch an offset formula.
Set custom checked and unchecked values
Open Data and Data validation for the intended range. Choose the checkbox rule and use the custom cell values controls where appropriate. For example, a checked value can be Yes and an unchecked value can be No. Save the rule and verify the cell’s underlying value.
With custom values, update formulas to match the stored text. The following example counts Yes values only where a task exists.
=COUNTIFS(A2:A20,"<>",C2:C20,"Yes")Test a checked and unchecked row. Inspect whether older values still match the rule after a change. If this tracker supplies another report, verify its formulas as well. A report that checks TRUE can miss rows using Yes.
Choose default or custom values deliberately
Default Boolean values are useful for straightforward formulas and can be easy to maintain. Custom labels can fit a reporting or export convention. Use the one required by the sheet and tell collaborators what the value means. If different rows use different rules, check whether the differences are intentional before merging them.
Test the delivered output
If the data is copied to a separate report or exported, inspect what the recipient sees and how the values are represented. They may need a fixed label or the source workbook rather than a screenshot of the boxes. Use the requested format and verify that the status remains clear without relying on a colour or a symbol.
Fix checkboxes that do not behave as expected
A row has TRUE or FALSE but no visible box
Check the cell’s data validation. A pasted value or imported column can contain Boolean values without checkbox formatting. Apply the intended checkbox rule if authorised and test the result. Review neighbouring rows to see whether they have the same rule.
A pasted row has lost its checkbox
Inspect the destination cell and the applied range. A paste can change values or rules depending on the workflow. Use the approved paste option or restore the intended validation. Check one existing row and one new row after the change.
A checked box does not increase the total
Confirm that the row is inside the formula’s range and that the checkbox uses the expected Boolean or custom value. Check any other criteria such as the required task name or date. A correct checkbox can be excluded by a separate condition. Test the formula on the actual row rather than clicking the box repeatedly.
The task name and checked state appear out of alignment
Check whether a sort or paste affected only part of the table. Keep the whole record together when sorting. Find a known task and verify its status against the source. Repair from the approved working copy before trusting a completion total.
Build a small checklist and test its edge cases
Create four task rows and leave two future rows empty. Check three tasks and leave one unchecked. Verify the result of three completed out of four. Check a box in an empty future row and confirm the task-based count stays at three. Add a real task to that row and verify that the count and denominator change as expected.
Then make an approved test copy with custom values. Change the summary formula to match those values and compare the result. This exercise shows why the display alone cannot tell you what a formula should check. Keep the column label, rule and calculations consistent.
Review the tracker before handing it to a team
Check a new row, an imported row and the last row inside the reporting range. Ask an authorised editor to mark a harmless test task and verify the summary. Document what Complete or Verified means where needed. If the report will be shared separately, confirm the intended output rather than assuming a filtered view restricts access to the underlying workbook.
Build a tracker for a class project with separate stages
Make columns for Task, Owner, Complete and Reviewed. Put checkboxes in Complete and Reviewed only if each column answers an independent question. A task can be complete but still need review. Do not treat those two values as interchangeable. Agree on who should mark each field and how the sheet records a task returned for correction.
Count completed tasks awaiting review
In this example A contains task names, C contains Complete and D contains Reviewed. A task awaiting review has a nonblank name, is complete and is explicitly marked unchecked in Reviewed.
=COUNTIFS(A2:A20,"<>",C2:C20,TRUE,D2:D20,FALSE)Test a complete reviewed task, a complete unreviewed task and an incomplete task. Only the second should count. Check an imported row where the Reviewed cell has no checkbox. If the team needs to treat missing review states differently from unchecked values, give that case its own check.
Decide whether a review resets after an edit
A task may be reviewed, then changed. The checkbox cannot tell whether an edit affects the earlier approval unless the team records it. Agree when the review box should be cleared or when a separate revision field is required. Check the workflow with a harmless test row. A record of who approved what may need more than two Boolean fields.
Keep the summary aligned with its wording
A tile called “Ready for submission” might require both Complete and Reviewed. If so, count rows where both are true and use the same record scope everywhere. Compare the total with a list of the actual tasks. If one checkbox is optional, represent that exception explicitly instead of changing the meaning of unchecked across different rows.
Use checkboxes in a register with optional items
A reading plan can contain required and optional tasks. A completion percentage for required reading should exclude optional tasks from both numerator and denominator. Put the type in a separate column. In this example column B holds Required or Optional, and column C contains the default checkbox.
=IF(COUNTIFS(A2:A20,"<>",B2:B20,"Required")=0,"",COUNTIFS(A2:A20,"<>",B2:B20,"Required",C2:C20,TRUE)/COUNTIFS(A2:A20,"<>",B2:B20,"Required"))Test an optional checked row and a required unchecked row. The optional item should not increase the percentage. The required unchecked item should be included in the denominator. If there are no required tasks, the example leaves the result empty. Use the display the task requires.
Check who owns the denominator
A team might want a progress percentage for one person, one class or one period. Apply the same owner or date criteria in both counts. Test a task from another group. If a percentage rises when the wrong group completes a task, inspect the range and criteria. A correct count with the wrong denominator can still produce a misleading report.
Keep missing and excluded items visible when needed
A task can be cancelled, postponed or missing a checkbox rule. Decide which should be reported separately. A checkbox alone cannot explain why a row is out of scope. Use a separate status or note where the task needs a reason. Review those records before sharing a progress figure.
Apply formatting that helps people scan a checklist
To format whole completed rows, select the desired table range beginning at row two. Create a conditional-formatting rule and use a formula that checks the status column on the same row. For column C, a locked column and relative row can do that. Add a real-task condition if blank rows could contain checked values.
=AND($A2<>"",$C2=TRUE)Check the rule’s applied range and a known row. If the wrong row is coloured, compare the formula’s first row with the range’s first row. If the formula applies to the right record but covers too few columns, inspect the selected formatting area. Keep the actual label readable.
Show a verified row without hiding necessary detail
A colour or strikethrough can help distinguish completed work. Ensure the reader can still read the task name and relevant notes. If a report will be printed or viewed without colour, keep a visible status or count that tells the same story. Conditional formatting should support the worksheet’s meaning.
Use different rules only when each one is needed
A completed row and a reviewed row may need different display cues. If several rules affect the same cells, test a record that matches both. Check which format the sheet displays and whether the result makes sense. If the rules become difficult to interpret, use separate status columns with clear labels.
Check how the rule behaves after a new row or import
Insert or add a test row where users normally add work. Confirm it has a checkbox and that the formula covers it. These are two separate checks. A row can have a checkbox and fall outside C2:C20, or be inside the formula’s range without the intended rule. Verify both in the actual workbook.
Test a values-only import
Some workflows bring in TRUE, FALSE, Yes or No values without checkbox rules. Decide what the import should produce. Apply or preserve the appropriate validation in the approved destination. Check a known checked value and an unchecked value. Do not change custom values until dependent formulas have been reviewed.
Check the result when sorting
Sort the complete table or use the intended view. Verify that the task, owner and checkbox stayed together. If only the checkbox column moved, the summary can retain the same total while the wrong tasks appear completed. A count cannot detect that misalignment. Check a known task’s actual status after changing the order.
Preserve a fixed record where the task requires one
A live checklist can change after a report is sent. If the recipient needs a fixed record, use the approved copy or export process and verify the statuses and totals. The snapshot should identify its scope and date where required. Keep the working sheet separate so later updates are not mistaken for the earlier result.
Compare three ways of representing progress
Use one checkbox when there is one yes-or-no decision. Use separate checkboxes when two independent questions matter, such as completed and verified. Use one dropdown when a task occupies one state at a time, such as Not started, In progress, Needs review or Done. Choose the representation based on how people enter data and how the report will use it.
Test the representation with an exception
Suppose work is complete but has been returned for correction. A single Complete checkbox might not communicate that situation. A separate review field or a status option may be clearer. Agree on which state the tracker should show and verify the formulas that depend on it. Keep the worksheet structure simple enough that a new editor can use it correctly.
Make one final check with a real workflow
Ask an authorised user to create a harmless task, mark it complete, change it back and then test the report. Check that the summary changes by exactly one at the right moments. Test a new row and an empty row. If there are separate review states, verify them as well. The tracker is useful when users and calculations agree on what each box means.
Count checked tasks by owner or project
Suppose column A contains the task, B contains Owner and C is Complete. Put the owner you want to review in G2. To count completed tasks for that owner, include the owner and the task key in the same formula.
=COUNTIFS(A2:A20,"<>",B2:B20,G2,C2:C20,TRUE)Check one completed task for the selected owner, one completed task for another owner and one unchecked task. Only the first should contribute. If G2 is blank, decide whether the report should prompt the reader or intentionally count unassigned work. Add a separate test for the selector when a blank has a special meaning.
Compare two owners without changing the source
Put the names in G2:G5 and copy the formula down with fixed source references where appropriate. Inspect the last formula and make sure it points to the right owner. Test a person with no completed tasks and one with a completed item. The difference should be due to the actual records, rather than a range that moved when copied.
Check what happens when a task is reassigned
Change an owner in an approved test copy and verify how the counts move. A complete task reassigned from one person to another can lower the first person’s total and raise the second person’s total without changing overall completion. If a report is supposed to track original ownership instead, use the approved field that stores it. The checkbox records completion, while the owner column defines the grouping.
Use a checkbox as an eligibility flag rather than a status diary
A checkbox labelled Include can mark a row for a particular export. A box labelled Verified can indicate that a person reviewed a value. Use a label that says what decision it represents. If the same sheet needs to record when an item was checked or who checked it, use the appropriate additional fields and process. A Boolean value alone stores the current state.
Build a simple reviewed-hours report
Put Name, Activity, Verified and Hours in four columns. Use one row per activity. Check the box after the hours have been reviewed under the agreed process. Sum only verified records and compare with an independently checked example. If a reviewer discovers a correction, use the defined workflow for clearing or restoring the verified state. Verify the resulting amount after the correction.
Separate inclusion from completion
An activity can be complete and still be excluded from a particular summary. If those facts matter, use separate fields or a defined status. For example, a completed optional task might stay out of the required progress percentage. Test an optional checked row and a required unchecked one. Make sure the count reflects the intended audience and scope.
Keep changes understandable in a shared sheet
Agree who may check a row and when. If editing permissions or a protected range apply, verify that the intended users can do their job. Validation controls the values a cell can contain. Permissions control who can edit it. Check both when a person says they cannot change the box or can change a field they should only read.
Verify imported Boolean and text values before calculating
A column copied from another system can contain true Boolean values, text labels or empty cells. They can look similar in a sheet. Open representative cells and inspect their actual values. Check what your chosen rule uses. Where an import uses Yes and No, decide whether to keep those custom values or normalise the approved working copy to Boolean values.
Do not change only the appearance
Adding a checkbox to a range helps users see and edit the states. The workbook’s formulas still need to compare the intended stored value. Test one imported row before applying the workflow to a whole table. If it should be checked, confirm that the summary increments. If it should remain unchecked, confirm that the denominator still includes the real task.
Handle a blank as a distinct case where necessary
A blank value can represent data that has not been entered, a row outside the task list or an invalid import. Decide what a blank means for each column. If it is an error for a real task, flag it for review. If it is expected for unused rows, exclude those rows using the task key. Check a real row with a blank value before choosing a calculation.
Test a sheet that uses custom text values
Choose Yes and No in a harmless copy. Count Yes with a text criterion and compare it with a test using TRUE. Verify which one reflects the checkboxes. Check any conditional-formatting rule and filter that reads the same column. Update the dependent formulas together before applying a value change to the live tracker.
Review a completion report with a hand-calculated example
Use six harmless rows. Mark four as required tasks, one as optional and leave one task name blank. Check three required tasks, the optional task and the blank row. The required completion percentage should be three out of four, or 75%. The count of all checked real tasks should be four. The count of every checked box, including the unused row, should be five. Each number answers a different question.
Change the scope and predict the result
If you add a requirement that the task belongs to Maya, check both the numerator and denominator for that owner. If you move a task outside the reporting period, verify that both counts use the same date rule. Predict the outcome before editing the formula. The arithmetic is easier to check when the reporting definition is already clear.
Compare the percentage with the list of matching tasks
Create a verified filtered list with the same scope and inspect the completed tasks. Compare the list length with the summary count. If they differ, check blank-row handling, owner criteria and the exact checkbox values. A percentage alone can hide the reason for an unexpected result. Use the underlying records to verify it.
Keep the checklist usable after someone copies it
When a team makes a copy, inspect the checkbox rules, formula ranges and any references to other tabs. Check a new task near the end of the list. Confirm that editing a checkbox changes the intended summary in the copied workbook. If the copy has a different number of rows, update the rules and calculations together.
Check the output when the work is shared as a snapshot
A PDF or fixed table can display the state at a moment in time. Open the delivered version and verify the labels and figures. If the recipient needs to tick boxes, use the approved editable spreadsheet workflow. If the recipient needs only a record, retain the accepted snapshot under the relevant process. Choose the format by what the reader should be able to do.
Verify the field name before adding a new checkbox
A new column labelled Approved might look like Reviewed to somebody scanning the sheet. Give it a clear meaning and explain any difference the report uses. Update the right formulas and test a row where the two states differ. This catches a summary that reads the wrong field while still producing a plausible number.
Frequently asked questions
How do I insert checkboxes in Google Sheets?
Select the cells on a computer and choose Insert, then Checkbox.
What do checked and unchecked mean in a formula?
Default checkboxes use TRUE and FALSE. Custom values can replace those states.
How do I ignore unused rows?
Add a condition that requires a real task name or other reliable key.
Why does COUNTIF return zero?
Check the range, the stored value and whether the rule uses custom values instead of TRUE.
Can I sum only checked items?
Use SUMIFS with the amount range and a checkbox condition. Add other criteria if the task requires them.
When should I use a dropdown instead?
Use a dropdown when the field needs more than two states, such as Not started, In progress and Done.
Will sorting a single column preserve the task status?
Keep the complete record together. Sort the full table or use the intended view, then verify a known task and its checkbox state.
Helpful reading
- Google Docs Editors Help on adding checkboxes and custom values
- How to Make a Dropdown List in Google Sheets
- How to Use FILTER in Google Sheets
- How to Use XLOOKUP in Google Sheets
- How to Use SUMIFS in Google Sheets With Multiple Criteria
- How to Create a Pivot Table in Google Sheets
Test the checkbox and the calculation together
A checkbox is ready to use when its value means the same thing to the person ticking it and the formula reading it.