Checkbox in Google Sheets: Add and Use It

To add a checkbox in Google Sheets, select the cell or range, then choose Insert > Checkbox. Click a box to check or clear it; by default, a checked box evaluates as TRUE and an unchecked box as FALSE, so you can use checkboxes in formulas and simple tracking workflows.

What a Google Sheets checkbox does

A checkbox is a cell control for recording one of two states, such as complete or incomplete, approved or pending, or included or excluded. It is more convenient to click than to type a status in every row, and it can make a shared sheet easier to scan. The checkbox belongs to the cell: it is not a floating shape placed over the grid.

By default, the cell value behind the control is the logical value TRUE when selected and FALSE when cleared. That distinction matters because formulas can test the value directly. The visible square is only the interface; formulas, filters, and other spreadsheet features work with the underlying cell value.

Checkboxes are useful when a field genuinely has two states. They are not a good substitute for a status field that needs several choices, such as Not started, In progress, Blocked, and Done. For that, a dropdown is usually clearer and less likely to be misread.

How to insert checkboxes

In Google Sheets in a web browser, select a cell, a block of cells, or a whole range, then click Insert > Checkbox. Each selected cell receives a checkbox. To apply the control to a column of existing or future rows, select a suitable range rather than adding boxes one cell at a time.

Click the first cell where you want a checkbox.
To add several at once, drag to select the intended cells or click a range using the name box.
Choose Insert > Checkbox from the menu.
Click any box to change its state, or press Space when the cell is selected to toggle it.

For example, a household shopping list might put item names in A2:A15 and checkboxes in B2:B15. A team task sheet could use column A for task names, B for the owner, C for a due date, and D for the completion checkbox. Keeping the control in a dedicated column makes sorting and reading the sheet more straightforward.

You can copy a checkbox cell and paste it into other cells, or use the fill handle to extend it down a range. Copying a cell can also copy its formatting and validation settings, so check the destination if it already contains important data or a different rule.

Set custom checked and unchecked values

Some sheets need labels or other values instead of TRUE and FALSE. Google Sheets lets you set custom values for checked and unchecked states. Select the checkbox cells, open Data > Data validation, and edit the checkbox rule in the data-validation panel. In the rule’s checkbox options, enable Use custom cell values and enter the values for checked and unchecked. The exact placement of this option can depend on the current panel layout.

For example, you could use Done for checked and Open for unchecked. This can make the cell value more readable to people inspecting the data, but it changes what formulas should look for: COUNTIF must search for the custom checked value rather than TRUE. Choose values that are consistent across the range, and avoid changing them midway through a workflow unless you also update formulas, filters, and any connected reports.

To return to ordinary checkbox behavior, edit the rule and remove the custom values or restore the default settings. If you have already used custom values, check what is stored in the cells before changing the rule: existing entries and formulas may need cleanup or adjustment.

Use checkboxes in formulas

Checkboxes become especially useful when a formula summarizes their state. Suppose task names are in A2:A20 and default checkboxes are in B2:B20. To count completed tasks, enter =COUNTIF(B2:B20,TRUE) in another cell. If five boxes are checked, the result is 5. The same pattern can count unchecked tasks with =COUNTIF(B2:B20,FALSE).

For a row-level label, suppose B2 contains a default checkbox. In C2, enter =IF(B2,"Done","Open"). The result is Done when B2 is checked and Open when it is clear. Copy the formula down to apply it to other rows. The text labels are formula results; they do not replace the checkbox itself.

For a completion percentage, with default checkboxes in B2:B20, use =COUNTIF(B2:B20,TRUE)/COUNTA(A2:A20) when A2:A20 contains the task names and every listed task should count. Format the formula cell as a percentage. If there are 10 named tasks and 4 checked boxes, the result is 40%. Make sure the denominator reflects the items you intend to count: counting a range that includes blank rows can give a misleading percentage.

If you use custom checkbox values, match the formula to those values. For instance, with Done as the checked value and Open as the unchecked value, a count of completed tasks should test for Done, not TRUE. A formula that looks for the wrong value may return zero even though boxes appear checked.

Build a practical checklist or tracker

A well-organized sheet makes checkbox information easier to use. Give the checkbox column a clear header, keep one item per row, and include other useful fields—such as owner, due date, or category—in separate columns. Freeze the header row if the sheet is long, and use a filter when readers need to find only checked or unchecked rows.

Personal checklist: Put each task in one row and use a checkbox to mark it complete.
Event planning: Track items such as venue, invitations, and supplies, with a separate checkbox for each item’s completion.
Review queue: Use a box to flag whether a row has been reviewed, while keeping the reviewer and review date in their own columns.
Simple selection list: Let people check which items they want included, then count or summarize the selected rows with formulas.

Checkboxes do not automatically move completed items, assign work, send notifications, or record who clicked them. Those actions require additional tools or a different workflow. If accountability matters, include a name or timestamp field and establish how it will be maintained rather than assuming the checkbox provides an audit trail.

Formatting, filtering, and sharing

Checkboxes can be combined with ordinary cell formatting. For example, conditional formatting can make a completed row appear muted, helping readers distinguish finished work at a glance. In Google Sheets, select the range to format and use Format > Conditional formatting. Set a rule based on the checkbox cell’s value, and make sure the rule’s range and cell references match the layout. Formatting changes appearance; it does not change the checkbox value.

You can filter a checkbox column to show only checked or unchecked records. This is useful for focusing on outstanding tasks, but remember that a filter hides rows from the current view rather than deleting them. Sorting can also help group records by state; select the full table or use a filter so that related cells in each row stay together.

When a spreadsheet is shared, people with permission to edit can generally change checkbox states. A checkbox is not a security feature and does not prevent accidental edits. Use Google Sheets’ sharing permissions and protected ranges when you need to limit who can edit particular cells. A protected range can restrict edits, but it should be configured deliberately and is not a substitute for controlling access to sensitive information.

Common checkbox problems and fixes

A click does not change the box: Check whether you have editing access, whether the cell or range is protected, and whether the spreadsheet is still loading. A viewer may not be able to change the value. If you can edit other cells but not this one, a protected range or validation setup may be responsible.

A formula returns zero: Confirm the checkbox uses default values or identify the custom checked value. A formula counting TRUE will not count a custom text value such as Done. Also check that the formula’s range includes the actual checkbox cells and that the cells have not been replaced with typed text.

Some cells have boxes and others do not: The checkbox rule may cover only part of the column. Select the intended range and use Insert > Checkbox, or copy a correctly configured checkbox cell into the missing cells. Be cautious when pasting over cells that contain data you need to keep.

Boxes appear but behave unexpectedly: Look at Data > Data validation to inspect the rule and its range. A copied or pasted value can also leave a cell with a different rule from neighboring cells. Standardizing the validation settings across the intended range often fixes inconsistent behavior.

Checkboxes compared with dropdowns and Excel

Choose a checkbox for a binary choice that people can update with one click. Choose a dropdown when a cell needs three or more meaningful options, or when you want users to select from a controlled list. Typing status words manually is flexible but can lead to inconsistent entries such as Done, done, and Complete, which are harder to summarize reliably.

Google Sheets and Microsoft Excel both support in-cell checkbox workflows, but their interfaces and feature availability differ. The steps in this guide are specifically for Google Sheets in a web browser; do not assume that the same menu path or checkbox behavior applies in Excel. In particular, Excel’s checkbox features and menus depend on the version and the kind of checkbox used. If you are moving a workbook between the two products, verify that the controls, validation rules, and formulas still behave as intended.

Frequently Asked Questions

Can I add checkboxes to an entire column?
Select the range of cells you want to use, then choose Insert > Checkbox. For a very large sheet, select a practical range that covers expected entries rather than adding controls to many unused rows.

What value does a checked box return?
A default checked checkbox returns TRUE, and an unchecked one returns FALSE. If custom values have been set in the checkbox rule, the cell returns those custom values instead.

Can I count checked boxes?
Yes. If default checkboxes are in B2:B20, =COUNTIF(B2:B20,TRUE) counts the checked ones. If you use custom values, use the checked value in the formula instead.

Can I make a checkbox show Done instead of TRUE?
Yes. Edit its rule through Data > Data validation and set custom cell values for checked and unchecked states. Update any formulas that previously expected TRUE or FALSE.

Can someone check a box without edit access?
Usually not. View-only access does not allow a person to change cell values. Give editing access only when appropriate, and use protected ranges if editors should not be able to change particular cells.

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.