Google Sheets Checkbox Conditional Formatting Guide

To apply conditional formatting to a Google Sheets checkbox, select the cells you want to format, open Format > Conditional formatting, and add a rule that checks whether the checkbox is TRUE or FALSE. For example, a custom formula such as =$A2=TRUE can highlight a whole row when its checkbox in column A is checked.

How checkbox conditional formatting works

A checkbox in Google Sheets is a cell with a selectable value, not a separate visual object that conditional formatting must recognize. By default, an unchecked box stores the Boolean value FALSE, and a checked box stores TRUE. Conditional formatting evaluates that cell value and applies a chosen appearance—such as a fill color, text color, or bold text—when a rule matches.

This makes a checkbox useful as a simple switch. You can use it to mark tasks complete, flag a row for review, show which items should be included in a summary, or visually distinguish selected records. The checkbox and the formatting rule are separate: changing the checkbox changes its value, while the conditional formatting rule determines what appearance follows from that value.

Formatting can target the checkbox cell alone, a neighboring range, or an entire row. The important setup choices are the range to format and the condition to evaluate. If the rule needs to look at a checkbox in a different column from the cells being formatted, use a custom formula with the right relative and fixed references.

Set up a basic checkbox rule

For a straightforward example, imagine checkboxes in A2:A20 and a task name beside each one in B2:B20. To color checked checkbox cells, select A2:A20. To color the task names instead, select B2:B20. Then use the conditional formatting panel:

Choose Format > Conditional formatting. The conditional formatting rules panel opens on the right.
Check the Apply to range field and make sure it contains the cells you want styled, such as A2:A20 or B2:B20.
Under Format cells if, choose a condition. For a default checkbox, you can use a custom formula or choose an equality condition and enter TRUE.
Choose the formatting style, such as a light green fill, and select Done.

To use a custom formula for the checkbox in the same row, assume the checkbox is in A2:A20 and the formatting range is B2:B20. Enter =$A2=TRUE as the custom formula. The dollar sign fixes the checkbox column at A while the row number can adjust for each row. Google Sheets checks A2 for the first row, A3 for the next, and so on.

You can also use a shorter test that evaluates the checkbox value directly. With checkboxes in A2:A20 and the formatting range B2:B20, the custom formula is =$A2. The explicit TRUE comparison may be easier for someone else to read, while the shorter version works because the checkbox value is already Boolean.

Highlight an entire row when checked

To shade a complete task row, select the full data range first—for example, A2:F100—then open the conditional formatting panel. Set the apply-to range to A2:F100 and choose Custom formula is. If the checkbox is in column A, use =$A2=TRUE. Pick the desired style and save the rule.

In this layout, the dollar sign before A is important: it keeps the rule looking at the checkbox column even while the formatting is applied across columns A through F. The row number is not locked, so each row uses its own checkbox. If you instead wrote =A2=TRUE, the reference could shift across columns as the rule is applied to the rest of the row, so cells in later columns might test the wrong column.

Choose the formatting range carefully. If the table has a header in row 1, starting at row 2 prevents the header from being shaded based on the first task’s checkbox. If the table extends to column G, include G in the apply-to range. The checkbox can be inside or outside the formatted range; the formula’s reference determines which cell controls the appearance.

Use custom checkbox values

Google Sheets also allows a checkbox to use custom checked and unchecked values instead of the default TRUE and FALSE. This can be useful when a sheet needs readable labels, such as Done and Not done, or numeric values for a particular workflow. To configure this, select the cells and use Data > Data validation. Set the criterion to Checkbox and enable the option for custom cell values, then specify the checked and unchecked values shown in the dialog.

Once the values are customized, make the conditional formatting rule match the value the checkbox actually stores. For example, assume A2:A50 contains checkboxes whose checked value is the text Done, and the formatting range is B2:F50. The custom formula is =$A2="Done". Because Done is text, it is enclosed in straight double quotation marks. A rule comparing this cell to TRUE would not match the custom text value.

Custom values can make a sheet’s raw data more understandable, but they also change how formulas and filters must refer to those cells. Before changing an established checkbox column, check whether other formulas, charts, or connected processes expect TRUE and FALSE. A value change can affect those parts of the workbook even though the checkbox still looks much the same.

Useful patterns for task and review sheets

Checkbox formatting can communicate more than completion. For example, you could use one checkbox column to mark an item for review and a separate priority column to apply a stronger style only to checked, high-priority items. This lets a reader scan the sheet without treating every checked row identically.

Assume A2:A100 contains default checkboxes, B2:B100 contains priority labels, and A2:F100 is the range to format. To highlight a row only when it is checked and its priority is High, use =AND($A2=TRUE,$B2="High") as the custom formula. The dollar signs fix the two condition columns, while the row references adjust as the rule is evaluated down the range.

Other practical uses include:

Completed tasks: apply a muted fill or text color to the task description when its row’s checkbox is checked.
Items to include: highlight selected rows so they stand out before copying or reviewing them.
Approval queues: color records marked for approval, while keeping the underlying approval status in a separate column.
Simple trackers: use checkboxes for recurring routines, inventory checks, or event-preparation lists, and make completed entries visually distinct.

Conditional formatting changes how cells look; it does not delete a row, protect a value, or prevent someone from editing the sheet. If an action needs to be enforced—for example, restricting who may change a status—formatting alone is not a control for that purpose.

Choose the right rule and range

For a small sheet, a built-in condition such as “is equal to” TRUE can be quick to set up. A custom formula is more flexible when the checkbox controls cells elsewhere, when the formatting should span a whole row, or when the condition combines multiple columns. In either case, verify that the apply-to range begins on the same row used in the formula.

A useful way to think about the references is to identify what should stay fixed as the rule moves:

Use a fixed column, such as $A2, when every cell in a row should check the checkbox in column A.
Use a relative row, such as A2 rather than A$2, when each formatted row should check its own checkbox.
Use a fixed row, such as A$2, only when every formatted cell should depend on the checkbox in row 2.
Use a fully fixed reference, such as $A$2, when one checkbox should control the entire formatted range.

For example, a single “show all” checkbox might control a range using a fixed reference, but that is different from a task list where every row has an independent checkbox. Deciding whether the rule should follow each row or always look at one control cell prevents many confusing results.

Common problems and fixes

If checking a box does not change the format, first confirm that the checkbox is in the cell referenced by the rule and that the rule’s apply-to range includes the cells you expect to change. Then check whether the checkbox uses default or custom values. A rule comparing to TRUE will not match a custom value such as Done.

If the wrong rows change, inspect the row number in the custom formula and the starting row of the apply-to range. A range starting at row 3 with a formula that assumes row 2 can offset the test. Also check that the checkbox column is locked with a dollar sign when the formatting covers multiple columns, as in $A2.

If the format appears inconsistent, look for other conditional formatting rules applied to overlapping cells. Review those rules in the formatting panel and simplify or adjust overlapping conditions so the intended appearance is clear. Also verify that the checkboxes are actual checkbox cells: a typed word, an inserted symbol, or a pasted text value that looks like a box may not store the Boolean value expected by the rule.

Finally, remember that conditional formatting styles do not replace the cell’s contents. If a rule changes the text color to match the fill, the content is still present, but it may be hard to read. Choose contrasting colors and test both checked and unchecked states before applying a style across a large table.

Google Sheets compared with Excel

The underlying idea is similar in Google Sheets and Microsoft Excel: a checkbox has a value, and conditional formatting responds to that value. The setup differs by application. This guide uses Google Sheets in a web browser, where the relevant path is Format > Conditional formatting and the rule is configured in the side panel.

In Excel, checkbox behavior depends on the checkbox type and version. Traditional Form Controls can be linked to a cell, and the linked cell can then be used in a conditional formatting formula; newer in-cell checkbox features are available in supported Microsoft 365 versions. Therefore, instructions for linking a Form Control checkbox in Excel do not directly describe Google Sheets. In Sheets, the checkbox cell itself stores the value used by the rule.

If you share a spreadsheet between the applications, check the checkbox behavior and rules after importing or exporting. A visual checkbox may not preserve every application-specific validation or formatting detail. For a workflow that relies on the checkbox value, test the converted file rather than assuming the appearance guarantees that the underlying values and rules transferred unchanged.

Frequently Asked Questions

Can I format a whole row based on a checkbox?
Yes. Apply the rule to the full row range and use a custom formula that locks the checkbox column but leaves the row relative, such as =$A2=TRUE when the checkbox is in column A and the formatted range starts on row 2.

Does a checked Google Sheets checkbox equal TRUE?
By default, yes. A checked checkbox stores TRUE and an unchecked checkbox stores FALSE. If custom checkbox values have been set, the cells instead store those specified values.

Can one checkbox change the formatting of many cells?
Yes. Apply the formatting rule to the desired range and use a fully fixed reference to the controlling checkbox. That makes every cell in the range evaluate the same checkbox rather than its own row’s checkbox.

Why does my checkbox rule not work after I changed its values?
The rule may still be checking for TRUE or FALSE even though the checkbox now stores custom values. Edit the conditional formatting formula or condition so it matches the checked value currently used by the checkbox.

Can conditional formatting automatically cross out completed tasks?
Yes. Set the conditional formatting style to include strikethrough, if available in the formatting options, and use a rule based on the task’s checkbox. The text remains in the cell; the style only changes its appearance.

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.