Google Sheets Checkbox Formulas: Setup and Examples

In Google Sheets, add an interactive checkbox with Insert > Checkbox; a checked box has the value TRUE and an unchecked box has the value FALSE. With a checkbox in B2, enter =IF(B2,"Complete","Incomplete") in C2 to show its status as text. The checkbox is created with a menu command, while formulas read its value or use it in calculations.

What a Google Sheets checkbox contains

A checkbox looks like a small control, but its cell stores a value. By default, checking it stores the Boolean value TRUE; clearing it stores FALSE. Those are logical values, not the text strings “TRUE” and “FALSE.” That distinction matters when you write a formula: a formula that compares a cell to the word “TRUE” may not behave like one that checks the checkbox’s actual Boolean value.

When you insert a checkbox, Sheets applies a checkbox data-validation rule to the selected cells. That rule gives the cell its clickable appearance and controls which values it accepts. The formula is separate: it can refer to the cell, test its value, count it, or use it to decide what result to display. A formula placed in an ordinary cell will not, by itself, create a clickable checkbox.

This setup is useful for task lists, attendance sheets, review workflows, packing lists, and simple approval trackers. It also makes a checkbox more than a visual mark: other cells can respond immediately when someone checks or clears it.

How to add checkboxes

Select the cell or range where you want the checkboxes, such as B2:B20.
Choose Insert > Checkbox.
Click a checkbox to switch it between checked and unchecked.
Enter formulas in other cells that refer to the checkbox cells.

For a task list, you might put task names in A2:A20 and checkboxes in B2:B20. Keep the checkbox column aligned with the corresponding tasks, and add new checkboxes as you add rows. Selecting a range before inserting checkboxes is usually easier than adding them one at a time.

If your sheet needs values other than TRUE and FALSE, select the checkbox cells and open Data > Data validation. In the checkbox rule, you can set custom values for checked and unchecked states, such as “Yes” and “No.” A custom-value checkbox still looks and behaves like a checkbox, but formulas must use the custom values rather than TRUE and FALSE.

Use IF to respond to a checkbox

An IF formula is a direct way to display a label or perform an action based on a checkbox. With a checkbox in B2, enter this formula in C2:

=IF(B2,"Complete","Incomplete")

The formula returns Complete when B2 is checked and Incomplete when it is unchecked. Because a checkbox evaluates to TRUE or FALSE, the formula can test B2 directly; writing =IF(B2=TRUE,"Complete","Incomplete") is also valid, but the comparison to TRUE is not necessary.

For example, if a task name is in A2, the checkbox is in B2, and the status label is in C2, checking B2 changes C2 to Complete without typing into the status cell. Copy the formula down column C to make the same relationship work for each row. In a copied formula, B2 adjusts to B3, B4, and so on.

Formulas should generally go in a separate output column, not in the checkbox cell itself. A cell cannot remain a user-controlled checkbox and also contain a formula that calculates a different value: entering a formula replaces the cell’s manually toggled value.

Count checked boxes and calculate progress

COUNTIF can count how many checkboxes are checked. With checkboxes in B2:B10, enter this formula in another cell:

=COUNTIF(B2:B10,TRUE)

If three of those nine boxes are checked, the result is 3. To count unchecked boxes in the same fully populated checkbox range, use =COUNTIF(B2:B10,FALSE). A reliable count depends on the range containing checkbox cells; avoid including unrelated notes or other data in that range.

A progress percentage can compare the checked count with the number of task names. With task names in A2:A10 and checkboxes in B2:B10, enter this formula in a summary cell:

=IFERROR(COUNTIF(B2:B10,TRUE)/COUNTA(A2:A10),0)

If there are four task names and two checked boxes, the calculation is 2 divided by 4, or 0.5. Format the result as a percentage using Format > Number > Percent to display 50%. COUNTA counts nonempty task-name cells, while IFERROR returns 0 if there are no task names to divide by. If the task list has gaps, the denominator counts only nonempty names, which may be preferable to counting every row in the range.

For a completion total tied to task values, a checkbox can act as a condition for adding numbers. With checkboxes in B2:B10 and corresponding point values in C2:C10, enter:

=SUMIF(B2:B10,TRUE,C2:C10)

This adds the values in C2:C10 only for rows whose checkbox is checked. For instance, if checked rows have values of 2 and 5, the result is 7. The criteria range and sum range should cover corresponding rows.

Combine checkboxes with other conditions

A checkbox can be one condition among several. For example, a review might be ready only when its checkbox is checked and a reviewer name has been entered. With the checkbox in B2, the reviewer name in C2, and the result in D2, use:

=IF(AND(B2=TRUE,C2<>""),"Ready","Needs review")

The formula returns Ready only when both conditions are true. AND requires every condition to be true; OR is useful when any one of several conditions is enough. These functions can help model a small workflow without adding a separate status dropdown for each possible combination.

For a summary that counts checked boxes only when a priority column says High, with checkboxes in B2:B10 and priorities in C2:C10, use:

=COUNTIFS(B2:B10,TRUE,C2:C10,"High")

COUNTIFS applies both conditions to the corresponding rows. This makes it possible to answer questions such as how many high-priority items are finished, rather than counting every checked box in the sheet.

Custom checkbox values and formula changes

Custom values can make a sheet’s data easier to read when another system or person expects labels such as Yes and No. However, formulas that assume default Boolean values need updating when custom values are enabled. With custom checked and unchecked values set to Yes and No, respectively, and checkboxes in B2:B10, count checked boxes with:

=COUNTIF(B2:B10,"Yes")

For a single custom-value checkbox in B2, a status formula in C2 could be =IF(B2="Yes","Complete","Incomplete"). The test must match the value configured in the checkbox rule. A formula using TRUE will not count a cell whose checked value is the text Yes.

Before changing an existing sheet to custom values, consider whether other formulas, charts, filters, or connected processes depend on TRUE and FALSE. Changing the stored values can affect those references. For a sheet used only by its own formulas, Boolean values are usually simpler; custom values are most useful when human-readable labels are important or a particular data format is required.

Highlight rows based on checkbox state

Conditional formatting can visually emphasize checked or unchecked tasks without adding a formula column. Select the rows or cells you want to format, then choose Format > Conditional formatting. Under the formatting rules, choose Custom formula is and enter the rule below. If each row’s checkbox is in column B and the first selected row is row 2, use:

=$B2=TRUE

Choose a formatting style, such as a background color, and select Done. The dollar sign keeps the rule tied to column B as it applies across a row; the row number remains relative so each row checks its own checkbox. If you select only B2:B20, the simpler rule =B2=TRUE can color checked checkbox cells.

Conditional formatting changes appearance, not the stored checkbox value. It is useful for scanning a list, but it does not replace a formula when you need a count, a label, or a calculation.

Common checkbox formula problems

The formula checks for text instead of a Boolean: Standard checkboxes return TRUE or FALSE as logical values. Use TRUE without quotation marks in criteria such as =COUNTIF(B2:B10,TRUE). If you configured custom values, use the exact custom value instead.
The formula is in the checkbox cell: A formula replaces the manually toggled value in that cell. Keep inputs and calculated outputs in separate columns.
The count includes unexpected cells: Check that the range contains the intended checkbox cells and not headers, notes, or unrelated data. Apply checkboxes to the full intended range if you plan to count unchecked boxes as well.
A checkbox does not appear: The cell may not have checkbox validation. Select it and choose Insert > Checkbox. If you paste values or replace validation, the visual checkbox rule may no longer be present.
The formula displays an error or the wrong result: Verify the cell references and whether the checkbox uses default or custom values. Spreadsheet locale settings can also affect formula separators; if Sheets rejects a comma-separated formula, check the locale-specific syntax used by that file.

One common misconception is that checking a box writes the word “checked” into the cell. With the default rule, the value is Boolean TRUE, not a status phrase. If another application needs a text label, use a formula in a separate cell to convert the Boolean value to text.

When to use a checkbox instead of another control

Checkboxes work best for a simple yes-or-no choice, such as finished/not finished or approved/not approved. They are convenient when a person is updating the sheet directly, and their Boolean values are easy to use with IF, COUNTIF, COUNTIFS, and SUMIF.

Use a dropdown instead when a cell needs more than two states, such as Not started, In progress, Blocked, and Done. A dropdown communicates those distinctions directly, while a checkbox can represent only its checked and unchecked values. For an automatically calculated status, use a formula rather than asking someone to toggle a control that could disagree with the underlying data.

There is no separate charge for using the checkbox feature within Google Sheets. The practical cost is the extra care needed to maintain the checkbox range and keep formulas aligned when rows are added, removed, or sorted. Keeping related task data on the same row and using clear column headings makes these trackers easier to maintain.

Frequently Asked Questions

What formula counts checked boxes in Google Sheets?
With checkboxes in B2:B20, use =COUNTIF(B2:B20,TRUE). If the boxes use custom checked values, replace TRUE with the configured value, such as the text Yes.

Can a formula create a clickable checkbox?
No. Add the interactive control with Insert > Checkbox. A formula can return TRUE or FALSE or report on a checkbox, but a formula in an ordinary cell does not add checkbox validation or make that cell clickable.

How do I show text when a checkbox is checked?
With a checkbox in B2 and a result cell in C2, enter =IF(B2,"Yes","No") in C2. Replace Yes and No with the labels that suit your sheet.

How do I count unchecked boxes?
With standard checkboxes in B2:B20, use =COUNTIF(B2:B20,FALSE). Keep the range limited to cells that actually contain the checkbox controls so unrelated or empty cells do not confuse the count.

Can checkbox formulas use custom values?
Yes. Set the custom values in the checkbox rule through Data > Data validation, then make your formulas test those values. For example, if checked is set to Yes, count it as text with =COUNTIF(B2:B20,"Yes").

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.