Google Sheets Checkbox Options: Values, Setup, and Uses

Google Sheets checkboxes can store the default values TRUE and FALSE, or custom checked and unchecked values such as Yes and No. In a browser, select the cells and choose Insert > Checkbox to add them; to change their stored values, open Data > Data validation and configure the Checkbox rule.

What the checkbox options mean

A Sheets checkbox is a visual control attached to a cell, not a separate object floating over the grid. Clicking it changes the cell’s value. By default, a checked box stores the Boolean value TRUE, and an unchecked box stores FALSE. These are logical values, so formulas can test them directly.

The main choice is whether to keep those defaults or assign custom values. With custom values, a checked box might store Done and an unchecked box might store Not done. You can also use values such as 1 and 0. The cell then contains the selected value even though Sheets displays a checkbox rather than the value itself.

Default values: TRUE when checked and FALSE when unchecked. This is usually best for formulas and straightforward task lists.
Custom values: Values you choose for the checked and unchecked states, useful when another sheet, export, or workflow expects specific labels.
No checkbox rule: A cell can be cleared or have its validation removed, so it is not necessarily restricted to a checked or unchecked state forever.

How to add checkboxes

For ordinary checkboxes, select one cell or a range in Google Sheets on the web, then choose Insert > Checkbox. A checkbox appears in each selected cell. You can also select a range first and add a whole column or block of controls at once; there is no need to insert them one by one.

Select the destination cell or range, such as A2:A20.
Choose Insert > Checkbox.
Click a box to check or uncheck it. The cell value changes as you do so.

If the checkboxes are for tasks, put task names in the neighboring column and the controls in a clearly labeled checkbox column. For example, use A1 for Status, B1 for Task, and rows 2 through 6 for the items. Keep the checkbox cells separate from descriptive text so formulas can refer to a clean, consistent range.

Set custom checked and unchecked values

Custom values are set through data validation. Select the cells, choose Data > Data validation, and add or edit a rule. In the rule panel, set Criteria to Checkbox and turn on Use custom cell values. Enter the value for the checked state and the value for the unchecked state, then save the rule. The exact panel layout can change as Google updates Sheets, but the Checkbox criterion and custom-values setting are the relevant controls.

For instance, entering Done as the checked value and Open as the unchecked value makes each checkbox cell contain one of those words. Use values that make sense to the people and tools that will consume the sheet. Avoid changing values casually after formulas or connected processes have been built around them: a formula looking for TRUE will not match the word Done.

Custom values change what is stored, not the checkbox’s appearance. The cell still shows a checked or empty box, so use a header or a nearby note to explain what checking means. Values should also be consistent throughout the range; mixing default Booleans and custom text can make summaries harder to interpret.

Use checkbox values in formulas

With default values, formulas can count selected boxes or trigger a result from a checked state. For example, with checkboxes in A2:A6 and three of those boxes checked, this formula counts the checked items:

With checkboxes in A2:A6, where A2, A4, and A5 are checked, the result is 3: =COUNTIF(A2:A6,TRUE)

To calculate a completion rate, put task names in B2:B6 and checkboxes in A2:A6. If three of the five tasks are checked, this returns 60% when the result cell is formatted as a percentage: =COUNTIF(A2:A6,TRUE)/COUNTA(B2:B6)

The task-name range matters: counting checked boxes alone does not tell you how many tasks exist. COUNTA(B2:B6) counts nonempty task cells, so the percentage uses the number of listed tasks as its denominator. If there are no task names, the denominator is zero; adjust the formula if an empty list is a normal state in your sheet.

For custom values, use the stored label in the condition. With custom checked value Done and checkboxes in A2:A6, this formula counts checked items: =COUNTIF(A2:A6,"Done") If the custom value is numeric 1, use 1 as the criterion instead. Make sure the formula matches the actual configured value, including spelling and spaces.

Make checkbox lists easier to read

A checkbox is most useful when its meaning is obvious. Add a column heading such as Complete? or Approved?, and put a short instruction near the table if the state needs interpretation. A checkbox does not display a text label inside the cell, so a reader should not have to guess whether checking means accepted, reviewed, or still needed.

Conditional formatting can make a checklist easier to scan. Select the task rows and use Format > Conditional formatting to apply a style based on the status cell. For example, a rule can shade a task row when its checkbox is checked. Keep the checkbox itself available as the control; formatting is a visual cue, not a replacement for the underlying value. Avoid relying only on color, since users may have difficulty distinguishing colors or may print the sheet in grayscale.

For a shared tracker, consider protecting formula or heading cells while leaving the checkbox range editable. Use Data > Protect sheets and ranges to configure protections. Protection helps reduce accidental edits, but it is not a substitute for sharing permissions or a way to make sensitive data private from people who can view the sheet.

Checkboxes compared with dropdowns

Checkboxes work best for a simple two-state choice: complete or incomplete, included or excluded, yes or no. A dropdown is better when a cell needs three or more named states, such as Not started, In progress, Blocked, and Done. Dropdowns can also be more explicit when the words themselves must remain visible in the cell.

Use a checkbox when people need to change a binary status quickly and formulas benefit from TRUE/FALSE values. Choose a dropdown when the status has several meaningful possibilities or when users need to see the text without relying on a header. For a decision that should not be changed freely, such as a reviewed result, a dropdown or a protected range may suit the process better than an easily clicked checkbox.

Common problems and fixes

A formula does not recognize a checked box: Check whether the rule uses custom values. A formula comparing the cell with TRUE will not match a custom value such as Done.
The checkbox disappeared: The cell may have been cleared, overwritten, or had its validation removed. Select the range and use Insert > Checkbox again, or restore the validation rule.
Pasted values replaced the checkboxes: A paste operation can replace cell contents or validation. Undo immediately if appropriate, or reapply the checkbox rule to the affected range and confirm the intended values.
Some cells behave differently: Check for inconsistent validation rules, mixed custom and default values, or cells that were copied from elsewhere. Apply one rule to the full intended range.
A checkbox is not clickable: You may have view-only access, or the range may be protected. Ask the owner for editing access or permission to edit that range.

Checkboxes are not a secure approval mechanism by themselves. A collaborator who can edit a cell may be able to change its state, and a checked box does not prove who clicked it or when. If the process needs an audit trail, use an appropriate workflow or record the identity and time separately rather than treating the checkbox as evidence.

Sharing, copying, and other considerations

When you share a Google Sheet, other editors can generally click checkboxes in ranges they are allowed to edit. Viewers can see the states but cannot change them. Protected ranges and sharing permissions affect editing access; they do not alter what TRUE, FALSE, or custom values mean.

Copying a checkbox cell may carry its validation rule along with its value, depending on how it is copied and pasted. When copying a range between sheets or into another spreadsheet program, check the destination rather than assuming that the checkbox behavior will transfer exactly. Data exported as CSV contains cell values, not an interactive checkbox control; default checkboxes therefore appear as TRUE or FALSE in the exported data.

Standard checkbox insertion is a built-in Google Sheets feature and does not require an add-on. Excel has checkbox features too, but its interface and supported behavior depend on the Excel version, and checkbox cells may not transfer as interactive controls between Excel and Sheets. If a workbook will move between programs, test a copy and agree on whether the shared data should use TRUE/FALSE, text labels, or another consistent representation.

Frequently Asked Questions

Can I change what a Google Sheets checkbox stores?
Yes. Open Data > Data validation, choose the Checkbox criterion, enable Use custom cell values, and enter values for the checked and unchecked states.

What does a checked checkbox equal in Google Sheets?
By default, a checked checkbox stores TRUE and an unchecked one stores FALSE. If custom cell values are enabled, it stores the configured checked or unchecked value instead.

Can I add checkboxes to a whole column?
Yes. Select the range you want, such as A2:A100, then choose Insert > Checkbox. Selecting a specific range is often preferable to selecting an entire column because it avoids adding controls to headings or unused rows.

Can a Google Sheets checkbox have more than two states?
No. A checkbox represents two states: checked and unchecked. Use a dropdown with data validation when a cell needs several options.

Why does my checkbox formula return the wrong count?
Confirm that the formula checks the right range and matches the stored value. Default checkboxes use TRUE; custom-value checkboxes may use text or numbers instead. Also check whether the range contains cells without the intended checkbox rule.

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.