Format a Row When a Checkbox Is Checked in Google Sheets

by

Use conditional formatting to color a whole task row when its checkbox is checked in Google Sheets.

Select the columns you want to color, then use a custom formula that points to the checkbox column.

Format a whole row when a checkbox is checked

In this example, task details are in A and B. The Done? checkboxes are in column C.

Task Owner Done?
Reconcile invoicesMayaUnchecked
Send reportOmarChecked
Review contractsPriyaUnchecked
Update forecastLeoChecked

Use these steps to color the task, owner, and checkbox cells whenever Done? is checked.

  1. Select A2:C5. Leave out row 1 so the headers keep their own formatting.
  2. Choose Format > Conditional formatting.
  3. Under Format cells if, choose Custom formula is.
  4. Enter =$C2=TRUE.
  5. Choose a fill color and click Done.
=$C2=TRUE

Rows 3 and 5 are formatted because their checkboxes are checked. Rows 2 and 4 remain unchanged.

Google Sheets task list with Send report and Update forecast rows highlighted because their Done checkboxes are checked
Google Sheets conditional formatting sidebar with the checkbox formula =$C2=TRUE applied to A2 through C5

Google documents both the Insert > Checkbox command and custom conditional-formatting rules.

Some English-language Sheets interfaces label this menu command Tick box. It creates the same checkbox control.

Google Sheets Insert menu with the Tick box command outlined

Why the formula uses $C2

A checked default checkbox has the Boolean value TRUE. The formula asks whether the checkbox in column C is TRUE for the current row.

The dollar sign locks column C. That keeps every cell across a row tied to its row’s Done? checkbox.

The row number has no dollar sign. Sheets checks C2 for row 2, C3 for row 3, and continues down the selected range.

See the GeoSheets absolute reference guide for a closer look at locked and moving references.

Choose the range that should change color

The rule can inspect column C while formatting other columns. Its Apply to range setting determines the part of each record that changes.

Apply to range Result when C is checked
A2:C5Colors the task, owner, and checkbox cells.
A2:B5Colors the task and owner but leaves the checkbox cell unformatted.
C2:C5Colors only the checked checkbox cell.

To include extra fields, widen the applied range. For example, use A2:F if the checkbox remains in C and your record extends through F.

Use custom checkbox values

Default checkboxes use TRUE and FALSE. Google Sheets also lets you assign your own checked and unchecked values.

Use these steps when your checkbox’s checked value is Yes instead of TRUE.

  1. Select the checkbox cells and choose Data > Data validation.
  2. Choose Checkbox, then enable custom cell values.
  3. Set the checked value to Yes.
  4. Update the conditional-formatting formula to =$C2="Yes".
=$C2="Yes"

The formula must match the value stored by the checkbox. Do not use =TRUE after replacing the checked value with text.

Fix a row that does not format

First, check Apply to range. It must cover the columns you expect to color and begin on the same row as the formula reference.

Next, confirm the checkbox’s stored value. A default checked box returns TRUE, while a custom checkbox can return text or another value.

Finally, make sure the formula locks only the checkbox column. =$C$2=TRUE makes every row depend on C2.

For a broader method that checks text, numbers, or two conditions, read how to highlight a row based on another cell.

Other Google Sheets articles you may also like

Leave a Comment