Google Sheets Checkbox Column: Add and Use Checkboxes

To add a checkbox column in Google Sheets, select the cells where the checkboxes should go, then choose Insert > Checkbox. Each checked box stores TRUE and each unchecked box stores FALSE by default, so you can use the column in formulas, filters, and conditional formatting.

What a checkbox column does

A checkbox is an in-cell control for recording a simple yes-or-no state. It is useful when a person needs to mark a task complete, confirm that an item was reviewed, or indicate whether a response has been received. Unlike a picture of a box or a drawing placed over the sheet, a Google Sheets checkbox is part of the cell’s data and can be used by spreadsheet features.

A checkbox column does not automatically create a task list or manage workflow rules. It records a value, and you decide what that value means. For example, a checked box in a column headed Done can mean a task is complete; in a column headed Approved, it can mean a reviewer has approved a row. Clear headings help collaborators interpret the same value consistently.

By default, the cell’s underlying value is the Boolean value TRUE when checked and FALSE when unchecked. The checkbox is only the visible interface for changing that value. This is why formulas can count checked boxes without needing to inspect the appearance of the cells.

How to add checkboxes to a column

In Google Sheets in a web browser, put a heading in the first row, such as Done, and select the cells below it. For a short list, select a range such as B2:B30. For a column you expect to keep adding to, you can select B2:B rather than the heading cell. Then choose Insert > Checkbox. Sheets adds a checkbox to each selected cell.

Choose a column and enter a descriptive heading in the top row.
Select only the rows that should contain checkboxes, leaving the heading out of the selection.
Choose Insert > Checkbox from the menu.
Click a box to check or uncheck it, then try sorting or filtering the table if that is part of your workflow.

For example, if column A contains task names and column B is Done, select B2:B50 and insert checkboxes. When a new task is added, add a checkbox to its corresponding B cell as well. Selecting a generous range in advance can reduce repeated setup, but applying checkboxes to thousands of unused rows may make the sheet harder to navigate and maintain.

To remove checkbox validation, select the affected cells and use Delete or the browser’s edit menu to clear the cells. Clearing cell contents and removing formatting are separate actions in Sheets, so if a checkbox still appears, inspect the cell’s data validation or undo the checkbox insertion. Be careful when clearing a range that also contains useful entries.

Default values and custom checkbox values

Most lists work well with the standard TRUE and FALSE values. They are convenient because Google Sheets recognizes them as logical values, and functions such as COUNTIF can test them directly. A checked box is not the text string TRUE; it is a logical value. That distinction matters if a formula is comparing values or if data is exported to another program.

Sheets also lets you set custom values for a checkbox, such as Yes for checked and No for unchecked. Select the cells and open Data > Data validation. In the data validation rules panel, choose the checkbox criterion and enable custom cell values, then enter the values for checked and unchecked states and save the rule. The exact panel layout can change as Google updates its interface, but the setting is part of checkbox data validation.

Custom values can make a sheet easier for people to read or can match an existing data process. They also mean formulas must test the custom value, rather than TRUE or FALSE. Decide on the values before building calculations around the column, and use the same validation rule throughout the range. Mixing default and custom checkbox values can cause a count or status formula to miss some rows.

Use a checkbox column in formulas

Checkboxes become especially useful when they drive totals or progress indicators. For example, with checkboxes in B2:B6 and five tasks listed in A2:A6, suppose B2, B3, and B5 are checked. To count completed tasks, use =COUNTIF(B2:B6,TRUE). The result is 3 because three cells contain the logical value TRUE.

To show a completion percentage, with task names in A2:A6 and their checkboxes in B2:B6, use =IFERROR(COUNTIF(B2:B6,TRUE)/COUNTA(A2:A6),0). With three checked tasks and five nonblank task names, the result is 0.6; format the result cell as a percentage to display 60%. IFERROR prevents a division error when there are no task names. The COUNTA denominator is useful when the list may contain blank rows; a fixed total would be simpler only if every row in the range always represents a task.

You can also use the checkbox state to produce a readable status. With a checkbox in B2, use =IF(B2,COMPLETED,OPEN) only if COMPLETED and OPEN are defined named ranges; for a straightforward text result, the valid formula is =IF(B2,"Complete","Open"). If B2 is checked, this returns Complete; otherwise it returns Open. Copy the formula down beside the task list, or use a single formula for a whole range if you are comfortable working with array formulas.

For a custom checkbox whose checked value is Yes, with those checkboxes in B2:B6, use =COUNTIF(B2:B6,"Yes"). If three cells contain Yes, the result is 3. Remember that the formula must match the actual custom value, including any spaces or differences in spelling.

Make completed rows easier to scan

Conditional formatting can visually distinguish finished items without changing the checkbox values. To shade completed task rows, select the task table range, such as A2:B50, then choose Format > Conditional formatting. In the rule options, choose a custom formula and enter =$B2=TRUE. Set the formatting style and apply the rule. The dollar sign fixes the checkbox column while the row number adjusts for each row, so a checked box in column B formats its corresponding task row.

For a rule that formats only the task name, select A2:A50 instead and use the same formula. Make sure the row number in the formula matches the first row of the selected range. If the formula refers to the wrong row, the formatting can appear one row above or below the checked box. Conditional formatting changes appearance, not the underlying checkbox state.

Filtering is another practical option. Turn on a filter for the table using Data > Create a filter, then use the filter control on the checkbox column to show checked or unchecked entries. This can help a team review outstanding work without deleting completed rows. Sorting or filtering should be applied to the whole table, not just the checkbox column, so task names remain associated with their statuses.

Common problems and how to fix them

The cells show TRUE and FALSE instead of boxes: The range may contain logical values without checkbox validation. Select the cells and choose Insert > Checkbox to add the checkbox interface.
A formula does not count checked boxes: Confirm that the formula range covers the checkbox cells and that the rule uses default TRUE/FALSE values. If you configured custom values, count the checked value you selected instead.
Only some rows have checkboxes: Checkbox validation may have been applied to only part of the column. Select the missing cells and insert checkboxes, or copy a correctly configured checkbox cell and paste it into the intended range.
A checkbox disappears after pasting: Pasting can replace cell contents or validation depending on the paste operation and source. Check the destination cell’s data validation and reapply the checkbox rule if needed.
The heading has a checkbox: The selection included the header row. Remove the checkbox from that cell and keep the heading in a separate row above the data.

When several people edit a shared sheet, agree on what checked means and who is responsible for changing it. A checkbox itself does not record a full approval history or prevent someone from changing a status. If accountability matters, consider using protected ranges for cells that should not be edited by everyone, and communicate how collaborators should report corrections.

Checkbox columns compared with other options

A checkbox is best when each row has two meaningful states, such as complete or incomplete. If a record can have several stages—Not started, In progress, Waiting, and Done—a dropdown is usually a better fit. In Sheets, select the cells and use Insert > Dropdown to create a list of choices. A dropdown makes the available states explicit and avoids trying to represent several conditions with multiple checkboxes.

Plain text such as Yes and No can work for a small or imported dataset, but it is easier for collaborators to enter inconsistent variations such as yes, YES, or y. A checkbox limits the interaction to two states and supports logical formulas. A separate status column may still be preferable when the data is regularly exported to systems that expect text labels.

Google Sheets checkboxes are in-cell controls. Excel has its own checkbox features, and its availability and behavior can depend on the Excel version; older workbooks may use floating form controls instead. Do not assume that a Sheets checkbox will transfer as an identical interactive control when a file is opened in Excel or another spreadsheet program. Check the converted file and its cell values before relying on it.

Frequently Asked Questions

Can I add a checkbox to an entire Google Sheets column?
Select the cells below the header, such as B2:B, and choose Insert > Checkbox. It is usually better to leave the heading cell out. For a very large sheet, consider selecting only the rows you need rather than filling every available row.

What value does a checked box return in Google Sheets?
By default, a checked box stores TRUE and an unchecked box stores FALSE. If you set custom values in the checkbox’s data validation rule, the cells use those selected values instead.

How do I count checked boxes in Google Sheets?
With default checkboxes in B2:B100, use =COUNTIF(B2:B100,TRUE). The result is the number of checked cells in that range. If you use a custom checked value, replace TRUE with that value.

Can I use checkboxes with Google Sheets filters?
Yes. Create a filter for the table with Data > Create a filter, then filter the checkbox column to show the checked or unchecked records. Include the whole table in the filter range so each task stays with its status.

Can a checkbox show a word instead of TRUE or FALSE?
Yes. Open Data > Data validation, edit the checkbox rule, and set custom values for the checked and unchecked states. Formulas must then refer to those values rather than the default logical values.

Do Google Sheets checkboxes work on mobile?
Checkboxes are part of the sheet and can be viewed and changed in the Google Sheets mobile app, though the menus and editing steps differ from the browser. For creating or editing a large checkbox range or its validation settings, the web interface is generally easier to follow.

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.