Google Sheets: What to Do If a Checkbox Is Checked

In Google Sheets, a checked checkbox is usually the Boolean value TRUE, and an unchecked checkbox is FALSE. To show a result when a checkbox in A2 is checked, use =IF(A2,"Complete","Open"); it displays “Complete” when checked and “Open” when unchecked.

What a checked checkbox means

A checkbox is a cell value with a visual control attached to it. In the standard setup, clicking the box changes its value from FALSE to TRUE, or back again. That means formulas can test the checkbox just as they test other logical values. You do not need a special checkbox function.

This is why a checkbox can drive a task list, approval tracker, attendance sheet, or small workflow. The box records a yes-or-no state; a formula can then display text, calculate a number, count completed items, or return matching rows. The checkbox itself does not automatically perform those actions. You decide what should happen by adding a formula or a formatting rule.

There is one important exception to the default TRUE/FALSE behavior: a checkbox can be configured with custom checked and unchecked values. For example, its values might be “Yes” and “No.” In that case, formulas should test for those values rather than assuming the cell contains TRUE or FALSE.

Insert and configure checkboxes

In Google Sheets in a web browser, select the cell or range where the boxes should appear, then choose Insert > Checkbox. For example, select A2:A20 to put one checkbox in each of those cells. Clicking a box toggles its state; selecting several checkbox cells at once lets you insert the controls across a range.

To inspect or change how a checkbox works, select it and open Data > Data validation. The validation rule identifies the checkbox criterion. Depending on the setup, you can use the standard checked and unchecked values or specify custom values. A custom-value checkbox is useful when another system or a formula expects particular labels, but it changes what you must test in formulas.

Standard values: Checked is TRUE and unchecked is FALSE.
Custom values: The cell contains the chosen checked or unchecked value, such as “Yes” or “No.”
No checkbox rule: A cell that looks empty or contains typed text may not be a checkbox at all, so first confirm the data validation rule.

Checkboxes are not the same as a drawing or an image of a box. A visual symbol typed into a cell does not provide the same TRUE/FALSE value or interactive behavior. Use Insert > Checkbox when you want formulas to respond to a click.

Use IF to return a result

IF is the most direct way to make one cell respond to a checkbox. It checks a condition and returns one value when the condition is true and another when it is false. With a standard checkbox in A2, Sheets can use the cell directly as the condition; comparing it explicitly with TRUE is also possible, but usually unnecessary.

Assume A2 contains a standard checkbox. Enter this formula in B2; when A2 is checked, B2 returns “Complete,” and when A2 is unchecked, it returns “Open”:

=IF(A2,"Complete","Open")

You can use numbers instead of labels. Assume A2 is a checkbox and B2 contains a task’s value of 25. Enter this formula in C2; it returns 25 when A2 is checked and 0 when it is not:

=IF(A2,B2,0)

This is useful for calculating only approved expenses, completed units, or selected scores. The formula does not change B2; it returns a result in C2. Keeping the input, checkbox, and calculated result in separate cells makes a sheet easier to inspect and reduces accidental overwriting.

With custom checkbox values, compare the cell with the configured checked value instead. Assume A2 is a checkbox whose checked value is “Yes,” while B2 contains a task name. Enter this in C2; it returns the task name when checked and “Not selected” otherwise:

=IF(A2="Yes",B2,"Not selected")

If you are unsure which kind of checkbox you have, click it and look at the cell’s value in the formula bar or check its data validation rule. A formula that tests TRUE will not match a custom text value such as “Yes.”

Apply the logic to a list

For a checklist with one checkbox per row, put the formula in the first result row and fill it down. Assume standard checkboxes are in A2:A20 and task descriptions are in B2:B20. Enter this formula in C2, then copy or fill it through C20; each row displays its own task name when selected and “Not selected” otherwise:

=IF(A2,B2,"Not selected")

Because the references are relative, the formula in C3 checks A3 and returns B3, and so on. If you need a reference to stay on one column while filling across, use a dollar sign, as in $A2. The dollar sign fixes the column but still allows the row number to change.

You can also require more than one condition. For example, a checked box might indicate that a request has been selected, while a neighboring status cell records whether it has been approved. Assume A2 is a standard checkbox and B2 contains an approval status. Put this formula in C2; it returns “Ready” only when the box is checked and the status is “Approved”:

=IF(AND(A2,B2="Approved"),"Ready","Hold")

This distinguishes “selected” from “approved,” which can prevent a simple checkbox from being mistaken for a completed review. Choose labels that make the meaning clear to other people using the sheet.

Count or filter checked rows

To count how many standard checkboxes are checked, use COUNTIF with TRUE as the criterion. Assume A2:A20 contains standard checkboxes. Enter this in B1; if seven boxes are checked, the result is 7:

=COUNTIF(A2:A20,TRUE)

For custom checkbox values, use the checked value as the criterion. Assume A2:A20 uses “Yes” when checked and “No” when unchecked. This formula returns the number of checked boxes; if seven cells contain “Yes,” it returns 7:

=COUNTIF(A2:A20,"Yes")

To show only rows whose checkbox is checked, FILTER can return the associated information. Assume A2:A20 contains standard checkboxes, B2:B20 contains names, and C2:C20 contains tasks. Enter this formula in E2, where there is room for the returned rows; if rows 3 and 7 are checked, it returns the names and tasks from those rows:

=FILTER(B2:C20,A2:A20=TRUE)

FILTER returns an array of matching rows, so the cells below and beside the formula need to be clear for the results to appear. If no rows match, Sheets reports that the filter found no matches. You can handle that case with IFERROR. Assuming the same ranges, this version displays “No checked items” instead of the error when nothing is checked:

=IFERROR(FILTER(B2:C20,A2:A20=TRUE),"No checked items")

Highlight checked rows without a formula column

If the goal is visual rather than numerical, conditional formatting can color a row when its checkbox is checked. Select the range to format, such as B2:D20, then choose Format > Conditional formatting. Under “Format cells if,” choose “Custom formula is,” enter the rule below, choose a formatting style, and click Done.

Assume A2:A20 contains standard checkboxes and B2:D20 is the range to highlight. Use this custom formula; it highlights the cells in each row of B2:D20 whenever the corresponding checkbox in column A is checked:

=$A2=TRUE

The dollar sign before A keeps the checkbox test in column A as the formatting rule applies across columns. The row number is not fixed, so the rule checks A2 for the first row, A3 for the next, and so on. Make sure the row number in the formula corresponds to the first row of the “Apply to range” selection. If your range begins at row 5, use $A5 instead.

Conditional formatting changes appearance, not cell values. A colored row is not a substitute for a formula if you need a count or a result to use elsewhere. Conversely, if the only need is to make checked tasks easier to spot, formatting avoids adding a separate result column.

Common problems and how to fix them

The formula does not react to a checked box: Check whether the checkbox uses custom values. If its checked value is “Yes,” a test for TRUE will not match; update the formula to test for “Yes.”
Every row shows the same result: When copying a formula down, confirm that each row reference changes as intended. A fixed reference such as $A$2 always checks A2.
The formula shows an error: Check that the ranges used by FILTER have the same number of rows, and that the output area is empty. A blocked array result can cause an error even when the formula’s logic is correct.
A blank cell acts like an unchecked box: A formula that tests a blank cell as a logical condition will generally treat it as false. If blanks should mean “not entered” rather than “unchecked,” test for blanks separately instead of treating all non-checked states as equivalent.
The checkbox disappeared or cannot be clicked: The cell may have been overwritten, or its checkbox validation rule may have been removed. Reapply the rule with Insert > Checkbox or inspect Data > Data validation.
Someone can change a result cell: A formula result is editable unless the sheet is protected. If preserving the calculation matters, consider protecting the formula range while allowing people to edit the checkbox input cells.

A common misconception is that checking a box automatically marks the entire row as complete or sends an alert. It only changes the checkbox cell’s value. A formula or conditional formatting rule can respond to that value, but a checkbox by itself does not send a message, move a row, or update a separate application.

Choosing the right checkbox response

Start with the outcome you need, rather than adding several formulas by default. Use IF when one cell should display a status or calculate a value. Use COUNTIF when you need a total. Use FILTER when you need a changing list of checked records. Use conditional formatting when you only want checked items to stand out visually.

Think about what the checkbox represents, too. “Done,” “approved,” and “include in report” are different decisions. If a workflow needs several stages, separate status fields or clear labels may be more dependable than one box whose meaning changes over time. In a shared sheet, add a header such as “Complete?” so collaborators know what checking the box means.

For larger or more consequential workflows, consider who is allowed to edit the checkbox and the formulas around it. Google Sheets sharing permissions and protected ranges can help limit accidental edits, but they do not turn a checkbox into an approval system with an audit trail. If a record must document who approved something and when, a simple checkbox may not capture enough information.

Frequently Asked Questions

What value does a checked Google Sheets checkbox contain?
A standard checkbox contains TRUE when checked and FALSE when unchecked. A checkbox configured with custom values contains the values chosen for its checked and unchecked states instead.

How do I make text appear when a checkbox is checked?
For a standard checkbox in A2, use =IF(A2,"Yes","No") in another cell. It displays “Yes” when checked and “No” when unchecked.

How do I count checked checkboxes?
For standard checkboxes in A2:A20, use =COUNTIF(A2:A20,TRUE). For custom checked values, use that value as the COUNTIF criterion instead.

Can I highlight an entire row when a checkbox is checked?
Yes. Apply conditional formatting to the row range with a custom formula that tests the checkbox column, such as =$A2=TRUE for a range beginning on row 2.

Why does my checkbox formula not work?
Check whether the checkbox uses custom values, whether your formula points to the correct row, and whether the cell still has checkbox validation. For FILTER, also check that the output cells are clear and the input ranges have matching dimensions.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.