Google Sheets Checkboxes: How to Add and Use Them

To add checkboxes in Google Sheets, select the cells where you want them and choose Insert > Checkbox. A checked box normally stores TRUE and an unchecked box stores FALSE, so you can use checkboxes in formulas, filters, and task lists—not just as visual marks.

How to Add Checkboxes in Google Sheets

In the Google Sheets web browser interface, select one cell or a range, then use Insert > Checkbox. Each selected cell becomes a checkbox. To create a column of boxes beside a list, select the matching range first; for example, select B2:B20 if the tasks are in A2:A20. You can also add a checkbox to a single cell and copy it into other cells.

For a small list, you might put task names in column A and checkboxes in column B. Use a heading such as Task and Done in row 1, then insert checkboxes in B2:B10. Click a box to check or uncheck it. In a shared sheet, another editor can click the same kind of control; the change appears for collaborators according to the sheet’s sharing and editing permissions.

One box: Select a cell, then choose Insert > Checkbox.
A range: Select all intended cells before choosing the menu command.
More rows later: Add checkboxes to extra rows as the list grows, or copy a checkbox cell into the new range.

Checkboxes are cell contents governed by a checkbox validation rule. They are not floating shapes sitting on top of the grid, which is why they can be sorted, filtered, counted, and referenced by formulas like other cell values.

What Checked and Unchecked Mean

With the standard setting, a checked checkbox has the logical value TRUE and an unchecked one has the logical value FALSE. These are Boolean values, not the text strings TRUE and FALSE. That distinction matters in formulas: a formula can compare a cell with TRUE directly, and a count can look for TRUE without putting it in quotation marks.

For instance, if the boxes are in B2:B9, a checked box can represent a completed task while an unchecked box means it is still pending. The cell may look like a small square, but formulas and other spreadsheet features work with its underlying value. If a formula returns an unexpected result, check whether the checkbox uses the default values or custom values, and whether the cell actually contains a checkbox rule.

The visual state is not a substitute for a separate status system in every situation. A box records two states: checked or unchecked. It cannot, by itself, distinguish between not started, waiting for approval, and blocked. Use a dropdown or multiple columns if the workflow needs more than two states.

Set Custom Checkbox Values

Google Sheets lets you assign custom values to the checked and unchecked states. This can be useful when another formula, report, or connected process expects values such as Yes and No, or numeric codes. Select the checkbox cells and open Data > Data validation. In the data validation panel, choose the checkbox criterion and enable the option to use custom cell values. Enter the values for checked and unchecked, then apply the rule.

Choose values deliberately. For a simple approval column, checked might mean Yes and unchecked might mean No. If unchecked is left blank in your chosen setup, blank cells and unchecked boxes may be harder to tell apart in some workflows. Use values that make sense to anyone reading the sheet, and explain them in a nearby note or heading when they are not self-evident.

Custom values affect formulas. A formula looking for TRUE will not count a checkbox whose checked value is Yes. Update formulas to match the actual value, or keep the standard TRUE/FALSE settings if you do not need custom labels. If several ranges have different checkbox rules, inspect each range’s validation settings before building a summary.

Use Checkboxes in Formulas

Checkboxes become especially useful when formulas summarize or respond to their values. The following examples use standard checkboxes, where checked means TRUE. Put formulas in a cell outside the checkbox range so they do not overwrite the controls.

Count completed items: With checkboxes in B2:B9, =COUNTIF(B2:B9,TRUE) counts the checked boxes. If five of the eight boxes are checked, the result is 5.
Show a status label: With a checkbox in B2, =IF(B2=TRUE,"Done","Open") displays Done when B2 is checked and Open when it is not. For a row-based task list, enter the formula beside the first task and fill it down.
Calculate completion percentage: If task names are in A2:A11 and checkboxes are in B2:B11, =IFERROR(COUNTIF(B2:B11,TRUE)/COUNTA(A2:A11),0) divides checked boxes by the number of nonblank task names. With 7 of 10 tasks checked, it returns 0.7; format the result as a percentage to show 70%.

For the percentage example, keeping task names and checkboxes aligned is important: a checkbox in a blank task row can be counted as completed even though it is not a real task. The formula uses COUNTA on the task-name range to avoid dividing by a fixed number of rows, but the ranges should still cover the same set of rows. If you use custom checkbox values, adjust the criterion in COUNTIF to the checked value, such as Yes.

Build a Practical Checklist

A useful checklist separates the item being tracked from the checkbox itself. For example, a weekly equipment inspection might use column A for the inspection item, B for Complete, C for the person responsible, and D for a note. This layout makes the checkbox easy to scan while leaving room to explain exceptions. A checkbox alone cannot record who checked an item or when, so add columns if that information matters.

For a shared project list, put the task name in A, the checkbox in B, and a due date in C. Use a summary cell to count completed work, and keep the task range consistent as rows are added. If a checkbox is intended to mean that a task is fully finished, explain that convention to collaborators; otherwise one person may check it to mean started while another assumes it means complete.

Think about how the sheet will be used before filling a large range. Hundreds of empty checkboxes can make a sheet visually busy and may encourage people to treat blank rows as tasks. Add controls only to rows that belong to the working list, or use a clearly bounded template that can be extended as needed.

Formatting, Sorting, and Filtering

You can make completed rows easier to recognize with conditional formatting. Select the task range, such as A2:C20, and choose Format > Conditional formatting. In the formatting rules panel, use a custom formula such as =$B2=TRUE if column B contains the standard checkboxes. Apply a muted text color or strikethrough to the selected range. The dollar sign fixes the checkbox column while the row number adjusts for each row.

Filtering can show only completed or incomplete entries. Add a filter with Data > Create a filter, then use the filter control in the checkbox column to select the value you want to display. The exact choices depend on the cell values and the sheet interface, but standard checkbox states are TRUE and FALSE. Sorting a table also moves the checkbox cells with their rows when the full table range is included. Avoid sorting only one column, which can separate task names from their checkboxes and other details.

Conditional formatting changes how cells look; it does not change the checkbox value. Likewise, hiding a row with a filter does not delete its data. Those distinctions help prevent accidental assumptions when a count or summary appears different from the visible rows.

Common Checkbox Problems

The menu command is unavailable or does not affect the cells: Confirm that you have selected the intended cells and have permission to edit the sheet. A protected range or view-only access can prevent changes.
The cells show TRUE or FALSE instead of boxes: The cells may contain Boolean values without a checkbox rule. Select them and choose Insert > Checkbox to add the control, checking first that this will not replace important existing data.
A formula does not count checked boxes: Confirm that the range is correct and that the checkboxes use the standard TRUE value. Custom values such as Yes require a matching formula criterion.
Deleting a value did not remove the checkbox: A cell’s checkbox is controlled by data validation. Select the affected range, open Data > Data validation, and remove the checkbox rule in the panel. Clearing a cell’s value and removing its validation rule are different actions.
Checkboxes shifted away from their tasks: Sort or filter the complete table rather than a single column. Include the headers and all related columns in the range where appropriate.

Before making a bulk change, select a small test range and verify the result. This is particularly helpful when a sheet has custom checkbox values, formulas, protected ranges, or several data validation rules.

Sharing and Excel Differences

Google Sheets checkboxes are designed for use inside Sheets, including collaborative browser-based editing. A collaborator needs sufficient access to change a box; view-only users cannot edit the sheet. If the file is downloaded, converted, or opened in another spreadsheet application, checkbox appearance and behavior can depend on the file format and the other application’s support. Check an exported copy before relying on it as a working checklist elsewhere.

Excel and Google Sheets both support ways to represent checked and unchecked states, but the controls are not always interchangeable. Recent Microsoft 365 versions of Excel have in-cell checkbox support, while older Excel workflows may use form controls or other approaches. Those controls and menu steps differ from Google Sheets’ Insert > Checkbox command. If you are moving a workbook between the programs, verify both the visible control and the underlying values rather than assuming that the checkbox will transfer unchanged.

Frequently Asked Questions

Can Google Sheets count checked boxes?

Yes. With standard checkbox values, use COUNTIF to count TRUE values in the checkbox range. For example, if the checkboxes are in B2:B20, =COUNTIF(B2:B20,TRUE) returns the number checked. Use the custom checked value instead if the cells have custom settings.

Can I add checkboxes to an entire column?

You can select a column or a specific range and choose Insert > Checkbox. A bounded range is often more practical than applying boxes to every row, because it avoids adding controls to unused parts of the sheet.

How do I remove checkboxes but keep other cell data?

Select the relevant cells, open Data > Data validation, and remove the checkbox rule. Check the result on a small range first if the cells also contain values or other validation rules, since removing a rule and clearing cell contents are separate operations.

Can a checkbox trigger a formula?

Yes. A formula can test whether the checkbox cell is TRUE and return a label, calculate a total, or determine a completion percentage. The formula updates when the checkbox changes, provided its references match the checkbox range and its values.

Why does my custom checkbox not work with TRUE in a formula?

Its checked value may have been changed from TRUE to a custom value such as Yes. A formula searching for TRUE will not match Yes. Inspect the rule under Data > Data validation and use the actual checked value in the formula, or restore the standard checkbox values.

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.