Google Sheets: Conditional Formatting for Tick Boxes

Yes. In Google Sheets, you can use conditional formatting to change a checkbox’s appearance or highlight other cells when it is checked. Select the cells, choose Format > Conditional formatting, and create a rule based on the checkbox value—usually TRUE for checked and FALSE for unchecked.

How checkboxes work in Google Sheets

A checkbox in Sheets is more than a picture of a square. By default, its cell contains the Boolean value TRUE when selected and FALSE when cleared. Conditional formatting can use those values to decide when to apply a background color, text style, or other available formatting. This makes a checkbox useful as a trigger for visual changes elsewhere in a spreadsheet.

To add one, select a cell or range and choose Insert > Checkbox. You can also select a range first to add checkboxes to several cells at once. For example, a task list might put task names in column A and checkboxes in column B. Each box can then control formatting in its own row, or contribute to a larger summary or workflow.

The checkbox itself remains a cell value, so it can also be used in ordinary formulas, filters, and other spreadsheet features. Conditional formatting does not change whether a box is checked; it only changes how the selected cells look when the rule’s condition is met.

Color the checkbox cell when it is checked

To color the checkbox cell itself, select the checkbox range, such as B2:B20, then choose Format > Conditional formatting. In the conditional-formatting panel, confirm the Apply to range field contains B2:B20. Under Format rules, choose a condition such as Is equal to and enter TRUE as the value. Choose a formatting style, such as a green fill, then select Done.

For a rule that explicitly checks the cell’s Boolean value, choose Custom formula is. With checkboxes in B2:B20, the formula is =B2=TRUE. In this layout, B2:B20 contains the checkboxes, and the first cell in the range is B2. The relative reference adjusts for each row as Sheets evaluates the rule. You can instead use the built-in condition Is equal to and TRUE; that is usually simpler when the rule applies only to the checkbox cells.

To give unchecked boxes a different appearance, add another conditional-formatting rule for FALSE and choose a separate style. A clear color contrast can make status easier to scan, but avoid relying on color alone if people need to distinguish statuses quickly. A label or nearby explanatory text can make the sheet more accessible.

Highlight an entire row from a checkbox

A common task-list setup is to dim or highlight a whole row when its checkbox is selected. Suppose column A contains task names, column B contains checkboxes, and the task rows run from 2 through 20. Select A2:D20, open Format > Conditional formatting, and set Apply to range to A2:D20. Choose Custom formula is and use =$B2=TRUE.

In this layout, checkboxes are in B2:B20 and the rows to format are A2:D20. The dollar sign before B locks the rule to the checkbox column, while the row number remains relative. As the rule is evaluated across the selected range, it continues to look at column B for the current row. Choose a style such as light gray fill or strikethrough text, then select Done. Checking B2 will format A2:D2, while checking B3 will format A3:D3.

The starting row in the formula must match the first row of the Apply to range. If the selected range starts at row 5, write the rule to refer to the checkbox in row 5, not row 2. Likewise, if checkboxes are in column E, lock column E instead. These reference details are a frequent source of rules that appear to work on the wrong row.

For a different effect, select only A2:A20 as the Apply to range while keeping the checkbox reference fixed to column B. That changes the task-name cells but leaves the checkbox cells untouched. You can apply a rule to a nonadjacent area too, but for a first setup, one rectangular range is easier to reason about and troubleshoot.

Use custom checkbox values

Some sheets use words instead of TRUE and FALSE—for example, Done and Not done—so that the values make more sense in a report or when exported. To set them, select the checkbox cells and open Data > Data validation. Add or edit a rule, choose Checkbox as the criteria, and enable the option to use custom cell values. Enter the checked and unchecked values, then save the rule.

With custom values, the conditional-formatting condition must match the value stored for the checked state. For example, if the checked value is Done and checkboxes are in B2:B20, a custom formula for formatting the corresponding rows is =$B2="Done". In this layout, the checkbox values are in B2:B20 and the target rows begin at row 2. The quotation marks are part of the formula because Done is text, not a Boolean value.

Do not assume that every checkbox stores TRUE and FALSE after custom values have been configured. If a rule does not trigger, inspect a checked cell and confirm which value it contains. The visible tick only tells you that the checkbox is selected; the underlying value is what the conditional-formatting rule evaluates.

Useful checkbox formatting patterns

Checkbox-driven formatting is useful for more than completed task lists. A small amount of planning can keep a sheet understandable as it grows. Here are several common patterns:

Completed tasks: Shade the whole row or strike through the task name when its checkbox is checked.
Reviewed records: Highlight a row when a reviewer marks its checkbox, helping a team spot entries that have already been checked.
Inventory or packing lists: Use a distinct fill for items that have been picked or packed, while keeping the item description visible.
Optional selections: Change the color of a related label or note when a box is selected, making the consequence of the choice easier to see.

For a simple two-state task, one rule for checked and one for unchecked is usually enough. If the sheet has several workflow stages, consider whether a checkbox is the right control. A checkbox represents a yes-or-no state; it cannot by itself distinguish statuses such as Not started, Waiting, and In progress. A dropdown or separate status column may communicate those stages more clearly.

Rule order, ranges, and formatting behavior

Conditional formatting is attached to a range, not to the checkbox as a special standalone feature. The checkbox value is the condition; the Apply to range field identifies which cells receive the visual style. Those can be the same cells, as with coloring B2:B20, or different cells, as with using B2:B20 to format A2:D20.

When multiple rules apply to the same cells, their order can affect the result, especially when rules set conflicting formats. In the conditional-formatting panel, review the rules for the selected range and put the intended rule in the appropriate order. If a color seems to disappear after adding another rule, check for overlapping ranges and styles rather than assuming the checkbox stopped working.

Formatting is only a visual cue. It does not delete a task, prevent someone from editing it, or automatically move a row. A person with edit access can still change the checkbox or the cell contents, and a copied or pasted range may bring its own formatting and validation. If the sheet is shared, explain what the checkbox means and keep the rule range aligned with the data people are expected to edit.

Common problems and how to fix them

The checked box does not trigger the rule: Confirm the rule uses TRUE for a standard checkbox, or the exact custom checked value if custom values are enabled. Check that the checkbox cell is inside the range the rule evaluates.
The wrong row changes color: Match the formula’s starting row to the first row of Apply to range. For row-based formatting, lock the checkbox column with a dollar sign but leave the row number relative.
Only the checkbox changes, not the task row: Expand Apply to range to include the cells you want formatted. A rule applied only to B2:B20 cannot color task names in A2:A20.
The formula is rejected or never matches: In a custom formula, start with an equals sign and use the correct cell reference. Text values need double quotes in the formula; Boolean TRUE is not text and should not be quoted.
A rule seems inconsistent after copying cells: Recheck the Apply to range and relative references. Copying content or formatting can change the layout or introduce additional rules, so inspect the rule panel for the affected cells.

A quick diagnostic is to click a checkbox and look at the formula bar for its cell. A normal checkbox should show TRUE or FALSE, while one with custom values should show the configured text or values. Then compare that stored value with the condition in the formatting rule. This separates a checkbox setup issue from a range or formula-reference issue.

Google Sheets and Excel are not identical

In Google Sheets in a web browser, the relevant path is Format > Conditional formatting, and a checkbox can be inserted through Insert > Checkbox. Excel has its own checkbox options and conditional-formatting interface, which vary by version. Do not assume that Google Sheets menu names, checkbox behavior, or steps for custom checkbox values transfer directly to Excel.

The basic idea is similar in many spreadsheet applications: a yes-or-no cell value can control formatting elsewhere. The exact steps depend on the program and how its checkbox is implemented. If a workbook or sheet will be used in both products, verify the checkbox values and formatting after moving or exporting the data; visual controls and rules may not transfer in the same way.

Frequently Asked Questions

Can Google Sheets change a checkbox color when it is checked?
Yes. Apply conditional formatting to the checkbox cells and create a rule for TRUE, or use a custom formula that tests the checkbox value. Choose the fill or text style you want in the formatting panel.

Can I highlight a whole row when I tick a box?
Yes. Set Apply to range to the row area, then use a custom formula that refers to the checkbox column with an anchored column reference. Make the formula’s row number match the first row in the selected range.

Do Google Sheets checkboxes use TRUE and FALSE?
By default, yes: a checked box stores TRUE and an unchecked box stores FALSE. If custom checkbox values are configured in Data validation, the cells store those specified values instead.

Can I cross out a task when its checkbox is checked?
Yes. Create a conditional-formatting rule for the task-name range, base it on the checkbox in the same row, and choose strikethrough in the formatting style. The rule changes the appearance; it does not remove the text.

Why does my conditional formatting affect the wrong cells?
The Apply to range may be incorrect, or the custom formula may lock the wrong row or column. Check that the first row in the formula matches the first row of the target range, and that the formula points to the checkbox column.

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.