Google Sheets: Change Cell Color When a Checkbox Is Checked

Yes. In Google Sheets, insert a checkbox, then use Format > Conditional formatting to apply a fill color when its value is checked. For a checkbox in A2, a custom formula such as =$A2=TRUE can color that cell or an entire row.

Set up a checkbox and a color rule

These steps are for Google Sheets in a web browser. The example assumes a list with a checkbox in column A and task details in columns B through D.

Select the cell or range where you want checkboxes, such as A2:A20.
Choose Insert > Checkbox. The cells now display checkboxes that you can click.
Select the range you want to color. To color only the checkbox cells, select A2:A20. To color each task row, select A2:D20.
Choose Format > Conditional formatting. The conditional-formatting panel opens.
Under Format rules, choose Custom formula is.
Enter the formula =$A2=TRUE, choose a fill color under Formatting style, and select Done.

Now click a checkbox in column A. When it is checked, the selected cell or row receives the chosen color; when it is unchecked, that conditional color is removed. The formula assumes row 2 is the first row of the selected range. If your range begins on row 5, use row 5 in the formula instead.

What the checkbox and rule are doing

A standard Google Sheets checkbox represents a value in its cell. By default, a checked box is TRUE and an unchecked box is FALSE. Conditional formatting evaluates a rule against the cell or range you specify and changes how matching cells look. The checkbox supplies the changing value; the rule connects that value to a color.

The rule does not replace the checkbox value or permanently paint the cell. It applies visual formatting while the condition is true. If you remove the rule, or the condition becomes false, the conditional formatting no longer appears. This is useful when the underlying data should remain a simple TRUE/FALSE value that formulas and filters can also use.

In the example formula, the dollar sign in =$A2=TRUE fixes the reference to column A, while the row number remains relative. As Sheets checks each row in the selected range, it tests that row’s checkbox in column A. That combination is what lets one rule color multiple rows based on their own checkboxes.

Color an entire row when its checkbox is checked

To make completed tasks stand out, apply the rule to the full task-table range, not just the checkbox column. For example, if checkboxes are in A2:A50 and task information is in B2:F50, set the conditional-formatting range to A2:F50 and use =$A2=TRUE. Checking A7 then colors the cells in row 7 across columns A through F.

Keep the formula’s row number aligned with the first row of the apply range. If the range is A2:F50, refer to row 2. If the range is A3:F50, refer to row 3. A common mistake is to select a range starting at row 2 but use a formula referring to A1; that shifts the test and can make the color appear on the wrong row.

You can also leave the checkbox column uncolored by applying the rule only to B2:F50. The formula still checks column A: =$A2=TRUE. This can keep the checkbox visible against a neutral background while highlighting the task details. Choose the apply range according to what you want readers to notice.

Use different colors for checked and unchecked items

A second conditional-formatting rule can give unchecked items a different appearance. For the same range, add a rule with =$A2=FALSE and choose a subdued fill, such as light gray. The checked rule can use a brighter color. This can make a checklist easier to scan, especially when both completed and outstanding work need to remain visible.

Another option is to color only checked rows and leave unchecked rows with the sheet’s normal formatting. That is often less visually busy. If you create multiple rules for the same cells, use conditions that do not overlap where possible, and review the rules in the conditional-formatting panel if the result is unexpected.

For a simple two-state checklist, the checkbox itself is usually enough; adding a separate status word may be redundant. But for workflows with more than two states, such as Not started, In progress, and Done, a dropdown may describe the status better than a checkbox. A checkbox communicates a binary choice: on or off, complete or incomplete.

Choose the right range and keep the rule manageable

The apply range determines which cells can change color; the formula determines which checkbox each row is tested against. Keeping those roles clear makes rules easier to edit. For a small list, a fixed range such as A2:F50 is straightforward. If you expect the list to grow, you can apply the rule to a larger planned range, such as A2:F500, so new entries within it are covered.

For a table with more than one checkbox column, make a separate rule for each checkbox if each one should control a different color. For example, if column A means approved and column B means shipped, a rule for the first status should refer to column A, while the second refers to column B. Fix the relevant column with a dollar sign so the reference stays in that column as the formatting is evaluated across a row.

If you copy a sheet or move columns, inspect the apply range and formulas afterward. A rule can remain attached to a range that no longer matches the table’s layout. Clear, descriptive formatting and a consistent table structure make it much easier to spot when a reference needs updating.

Common problems and how to fix them

The color appears on the wrong row: Make the formula’s row number match the first row in the apply range. For A2:F20, start with row 2 in the formula.
Every row changes together: Check that the formula uses a relative row reference, such as =$A2=TRUE, rather than locking both the column and row as =$A$2=TRUE.
Only the checkbox cell changes color: Expand the apply range to include the other columns, for example A2:F20.
Nothing changes when you click a box: Confirm the cell is an inserted checkbox, the rule uses the correct checkbox column, and the formatting range includes the cells you expect to color.
The rule works for some rows but not others: Check for a mismatched starting row, a rule that covers only part of the list, or checkboxes that were not added to the full intended range.

It can help to open Format > Conditional formatting with a cell in the affected area selected. Review both the apply range and the formula, then make a single test by checking and unchecking a box. If you change the formula, keep the checkbox column fixed and the row relative when formatting entire rows.

Custom checkbox values and related options

Google Sheets can use custom values for checked and unchecked states through the checkbox criteria in data validation. In the data-validation settings, choose the checkbox criterion and enable custom cell values if you need values other than TRUE and FALSE. This is useful when a sheet’s existing formulas or connected workflow expects a particular value.

When custom values are used, write the conditional-formatting formula to test the actual checked value, not TRUE. For example, if the checked value is the text Done, the formula needs to compare the checkbox cell with that text. Be careful to match the value and its data type exactly; text and numbers that look similar are not always equivalent. If you do not need custom values, the default TRUE/FALSE behavior keeps the rule simple.

Changing a cell’s fill manually is not a substitute for a checkbox-based rule. A manual fill stays as formatting until someone changes it, while conditional formatting responds to the checkbox state. For an automatically updated checklist, use the conditional-formatting rule so the visual cue stays in sync with the cell value.

Google Sheets versus Excel

The idea is similar in spreadsheet programs: a checkbox supplies a value, and conditional formatting reacts to it. However, Google Sheets and Excel have separate interfaces and checkbox implementations, so their instructions and menu paths should not be treated as interchangeable. This procedure uses the Google Sheets browser menus, especially Insert > Checkbox and Format > Conditional formatting.

If you are following an Excel guide, verify that it matches your Excel version rather than copying its steps into Sheets. Conversely, a Google Sheets custom formula should be checked against the target spreadsheet program before reuse. For a shared Google Sheet, the rule travels with the sheet and responds as collaborators change the checkbox values, subject to their access and editing permissions.

Frequently Asked Questions

Can I change only the checkbox cell’s color?
Yes. Apply the conditional-formatting rule to the checkbox range, such as A2:A20, and use a formula that checks the corresponding cell, such as =A2=TRUE. Choose a fill style and save the rule.

Can I color the whole row based on one checkbox?
Yes. Apply the rule to the full row range, such as A2:F20, and use =$A2=TRUE when column A contains the checkboxes. The fixed column reference keeps the test in A while the row adjusts.

Does checking the box change the cell’s value?
Yes. With the default checkbox settings, checking it makes the cell TRUE and unchecking it makes the cell FALSE. Conditional formatting reads that value to decide whether to show the chosen color.

Can I use a different color when the box is unchecked?
Yes. Add another conditional-formatting rule for the same range and use =$A2=FALSE for a row-based rule. Select a different fill color, or leave unchecked rows unformatted.

Why is my conditional format coloring the wrong row?
The formula’s starting row may not match the first row in the apply range, or the row may have been locked with a dollar sign. For a range beginning in row 2, use a reference such as =$A2=TRUE, not =$A$2=TRUE.

When checkbox coloring is most useful

Checkbox-driven colors work well for task lists, review queues, attendance sheets, and simple approval trackers because they make a state visible without requiring a reader to interpret a formula. Keep the color palette restrained, use a clear header for the checkbox column, and make sure the checkbox’s meaning is obvious. A rule is most reliable when the checkbox column, data range, and formula all follow the same row structure.

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.