Google Sheets Checkbox IF Statements: Examples & Tips

In Google Sheets, use an IF formula to return one result when a checkbox is checked and another when it is unchecked. For a checkbox in A2, enter =IF(A2,"Done","Pending") in another cell; a checked box returns “Done,” and an unchecked box returns “Pending.”

How Google Sheets checkboxes work

A checkbox in Google Sheets is a data-validation control linked to the value in its cell. With the usual settings, checking it gives the cell the Boolean value TRUE, while clearing it gives the cell FALSE. These are logical values, not the text strings “TRUE” and “FALSE.” That distinction matters when you write formulas: refer to the checkbox cell directly or compare it with TRUE, rather than adding quotation marks around TRUE.

To add a standard checkbox, select the cell or range and choose Insert > Checkbox. You can then click a box to change its state. In a list, each row’s checkbox can control a formula on that same row, such as a status label, completion date, or calculation.

Keep the input and result separate. A cell used as a checkbox stores the checked or unchecked value; putting an IF formula into that same cell would replace the checkbox’s value with the formula result. Put the formula in a neighboring column instead. For example, a task name can go in column A, the checkbox in column B, and the status formula in column C.

Basic IF formula for a checkbox

IF tests a logical condition and returns one value if the condition is true and another if it is false. The general syntax is =IF(condition, value_if_true, value_if_false). The final argument is optional, but it is usually helpful to specify what should appear when the box is unchecked.

With a checkbox in A2, enter this in B2 to show different text for each state: =IF(A2,"Approved","Needs review"). If A2 is checked, B2 displays “Approved”; if A2 is unchecked, it displays “Needs review.” Copy the formula down column B to use the checkbox in each corresponding row.

If you prefer a blank result while a task is incomplete, use two quotation marks as the true blank-text result. With the checkbox in A2, this formula in B2 shows “Complete” only when the box is checked: =IF(A2,"Complete",""). The cell looks empty when unchecked, although it still contains a formula. This is useful for clean-looking reports, but it can be less clear to someone who does not know that a blank means “not complete.”

Using custom checkbox values

Some workflows need words or other values instead of TRUE and FALSE. Google Sheets lets you set custom values for a checkbox. Select the cells and open Data > Data validation, then edit or add a checkbox rule and choose the option to use custom cell values. Set the checked and unchecked values you want, such as “Yes” and “No.” The IF condition must then test the checked value exactly.

With a checkbox in A2 configured to store Yes when checked and No when unchecked, enter this in B2: =IF(A2="Yes","Ready","Waiting"). The comparison uses quotation marks because Yes is text. If the custom checked value is the number 1 instead, the condition should compare with the number 1, not with the text “1.” Make sure that the formula matches the configured value and its type.

Custom values are handy when a sheet feeds another process or when a team wants readable cell contents. They also add a small maintenance cost: a formula written for standard TRUE/FALSE boxes will not necessarily behave as intended after the values are changed. If you inherit a spreadsheet, check its data-validation rule before assuming what a checked box stores.

Practical examples for task lists and calculations

Checkboxes can control more than a status word. A formula can show a note, return a number, or include a row in a calculation. Choose the output that fits what the sheet needs to communicate.

Show a completion message: With the checkbox in B2, put =IF(B2,"Finished","Open") in C2. Copy it down to produce a row-by-row status.
Release a value only after approval: With an approval checkbox in B2 and an amount in C2, put =IF(B2,C2,0) in D2. The amount appears in D2 when approved and the result is zero otherwise. This is a display or calculation choice, not a substitute for permission controls.
Count checked items: With standard TRUE/FALSE checkboxes in B2:B10, put =COUNTIF(B2:B10,TRUE) in another cell. It counts checked boxes in that range; unchecked boxes are not counted.
Show a message only when every required box is checked: With required checkboxes in B2:B5, put =IF(AND(B2,B3,B4,B5),"All steps complete","Still in progress") in another cell. AND is TRUE only if all four checkbox values are TRUE.

For a percentage of tasks completed, divide the number checked by the number of task rows, and decide how the sheet should handle an empty list. For example, with standard checkboxes in B2:B10, use =IFERROR(COUNTIF(B2:B10,TRUE)/COUNTA(A2:A10),0) in the summary cell, assuming task names are in A2:A10. Format that result as a percentage. This returns 0 if there are no task names to count, rather than displaying a division error. It assumes each nonblank task-name cell represents one task.

Combining IF with AND and OR

Use AND when a result depends on multiple conditions being true, and OR when any one of several conditions is enough. For example, an approval might require both a review checkbox and a payment checkbox. With those boxes in B2 and C2, put =IF(AND(B2,C2),"Ready to proceed","Approval incomplete") in D2. The formula returns the ready message only when both are checked.

For an alternative condition, imagine that either of two contact methods is acceptable. With checkboxes in B2 and C2, put =IF(OR(B2,C2),"Contact method selected","Choose a method") in D2. The first message appears if at least one box is checked. If your condition is more complicated, split it into clear tests or use a helper column so that another person can follow the logic.

Checkboxes and conditional formatting

An IF formula changes a cell’s displayed value. Conditional formatting changes how a cell looks when a condition is met. They can be used together: a status column can contain text from IF, while conditional formatting colors the related task row when its checkbox is checked.

To color rows based on a checkbox, select the rows or range to format and choose Format > Conditional formatting. Set the rule to “Custom formula is” and use a reference to the checkbox in the first row of the selected range. For example, if the selected range is A2:C20 and each row’s checkbox is in B, use =$B2=TRUE. Choose a formatting style and select Done. The dollar sign fixes the checkbox column while the row number changes for each row. With standard checkboxes, the shorter condition =$B2 also tests whether the box is checked.

Conditional formatting does not change the underlying checkbox value or lock the row. It is only a visual cue. If you want a written status as well, use an IF formula in a separate column; if you need to prevent edits, use Google Sheets’ sharing or protected-range features rather than relying on color.

Common problems and how to fix them

The formula always shows the unchecked result: Verify that the checkbox is in the cell referenced by the formula. If the box uses custom values, compare against the configured checked value instead of TRUE.
TRUE or FALSE appears instead of a message: The formula may be missing, or the formula may refer to a different cell. Check the formula bar and confirm that the formula cell is separate from the checkbox cell.
A formula treats a value unexpectedly: Standard checkboxes use Boolean values, while custom checkbox values may be text or numbers. A comparison such as =IF(A2="TRUE","Yes","No") checks for the text TRUE and may not match a standard checkbox. Use =IF(A2,"Yes","No") for a normal checkbox.
Copied formulas check the wrong box: When copying a formula down, a relative reference such as A2 should usually change to A3, A4, and so on. If a conditional-formatting rule is applied across rows, anchor the checkbox column, as in =$B2, but leave its row relative.
Blank rows show “Pending” everywhere: An unchecked checkbox is FALSE even if the row has no task, so a basic IF formula will show its false result. Add a test for the task-name cell when you want unused rows to stay blank. With task names in A2 and a checkbox in B2, put =IF(A2="","",IF(B2,"Done","Pending")) in C2.

When troubleshooting, click the checkbox and inspect the affected formula result. Then check the checkbox’s data-validation rule and confirm that the formula points to the intended cell. This simple sequence often reveals whether the issue is a wrong reference, custom values, or an unexpected blank row.

Google Sheets versus Excel

The core idea is similar in Google Sheets and Excel: a checkbox represents a value that a formula can test, and IF can return different results for the two states. The menu paths and feature details are not identical, however. In Google Sheets, the standard route is Insert > Checkbox, and custom values are managed through Data > Data validation. Do not follow Excel ribbon instructions when working in the Sheets browser interface.

Google Sheets formulas can be used by collaborators in a shared browser-based spreadsheet, with changes generally reflected as people edit. For a workbook that must work in both Sheets and Excel, keep formulas simple and confirm that checkbox controls and validation settings carry over as expected; spreadsheet controls may not transfer identically between applications. If the file will be exchanged, test a copy before relying on it for a workflow.

Frequently Asked Questions

What formula do I use if a checkbox is checked in Google Sheets?
For a standard checkbox in A2, use =IF(A2,"Yes","No") in a different cell. It returns Yes when checked and No when unchecked.

Does a checked Google Sheets checkbox equal TRUE?
Yes, a standard checkbox stores the Boolean value TRUE when checked and FALSE when unchecked. Custom checkbox values can change what is stored.

Can I use IF with several checkboxes?
Yes. Combine checkbox tests with AND when all must be checked, or OR when any one is sufficient. For example, =IF(AND(B2,C2),"Complete","Incomplete") requires both standard checkboxes to be checked.

Can I make a checkbox show text instead of TRUE or FALSE?
Yes. Edit its checkbox rule through Data > Data validation and configure custom checked and unchecked values. Update formulas to compare against those values.

Why does my checkbox formula return the wrong result?
Check the cell reference, confirm whether the checkbox uses standard or custom values, and make sure text values are compared as text. Also confirm that the formula is in a separate cell from the checkbox.

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.