Google Sheets: Highlight a Row When a Checkbox Is Checked

Yes. In Google Sheets, you can highlight an entire row when its checkbox is checked by applying conditional formatting to the row range and using a custom formula that refers to the checkbox column. For example, if checkboxes are in column A and your data begins on row 2, apply a rule using =$A2=TRUE to the rows you want to format.

Set up the checkbox and row range

First, make sure the cells contain actual Google Sheets checkboxes rather than typed symbols such as ☑ or text that says “done.” Select the checkbox cells, then choose Insert > Checkbox. By default, a checked box has the value TRUE and an unchecked box has the value FALSE. The checkbox is a cell control, so its value can be used by formulas and conditional formatting.

Next, decide which cells should change appearance. If column A contains the checkbox, and columns B through F contain the task details, select the data range B2:F100 to color only the details, or A2:F100 to color the checkbox and the rest of each row. The range should begin on the same row used in the formula. To apply the format to more future rows, you could use a range such as A2:F, provided the sheet layout supports it.

Select the rows or cells you want the rule to format.
Choose Format > Conditional formatting.
In the Conditional format rules panel, confirm the range in “Apply to range.”
Open “Format cells if” and choose Custom formula is.
Enter the checkbox formula, choose a fill color or other formatting, then click Done.

The rule changes the formatting, not the cell contents. Checking a box does not delete a task or move it; it simply makes the condition true, so Sheets displays the chosen style for the applicable row cells.

Why the custom formula works

With checkboxes in A2:A100 and the formatted range beginning at row 2, enter =$A2=TRUE. The dollar sign fixes the column reference on A, while the row number remains relative. As Sheets evaluates the rule down the selected range, it checks A2 for row 2, A3 for row 3, and so on. This is what makes one rule work across many rows instead of checking only the first checkbox repeatedly.

The comparison to TRUE makes the intention explicit: format the row when its checkbox is checked. For a standard checkbox, the shorter =$A2 also works because the cell already contains TRUE or FALSE. The explicit version can be easier to understand later, especially for someone who did not create the rule.

Pay attention to the alignment between the formula and the top row of the range. If “Apply to range” begins at row 5, the formula should refer to the checkbox in row 5, such as =$A5=TRUE. If the range starts at row 2 but the formula refers to A1, the rule is offset and may color the wrong row. A common setup is to select A2:F100 and use a formula that references A2.

A practical task-list example

Imagine a task tracker with a checkbox in column A, the task name in B, an owner in C, a due date in D, and notes in E. The header occupies row 1, while task records start in row 2. To shade the full task row when complete, set “Apply to range” to A2:E100 and use =$A2=TRUE. Choose a subtle gray or green fill. Checking A7 will then format A7:E7, while leaving other rows unchanged.

If you would rather keep the checkbox column visually plain, apply the rule to B2:E100 instead. The condition can still refer to A2: the cells being formatted do not have to include the cell that controls the rule. This is useful when the checkbox is a control column and the rest of the row is the information readers should scan.

For a live tracker with more than 100 tasks, extend the applied range far enough to include new entries, or use an open-ended range such as A2:E. The open-ended approach can be convenient, but avoid applying formatting to entire columns without considering sheet size and other rules. A carefully chosen range keeps the formatting easy to inspect and limits accidental effects on unrelated cells.

Using custom checked and unchecked values

Google Sheets also lets a checkbox use custom values instead of the default TRUE and FALSE. This can help when a sheet needs labels such as “Complete” and “Pending,” or when another formula or connected workflow expects specific text. To configure this, select the checkbox cells and open Data > Data validation. In the checkbox criteria, enable the option to use custom cell values and enter the values for checked and unchecked states.

When custom values are enabled, update the conditional-formatting formula to match the checked value exactly. For example, with checkboxes in A2:A100 whose checked value is the text Complete, and a formatted range beginning at row 2, use =$A2="Complete". This formula compares the cell with the checked label rather than with TRUE. If the chosen checked value is the number 1, the comparison should use that number instead. Match the actual checkbox configuration; do not assume the default Boolean values still apply.

Custom values can make a sheet more readable in formulas and exports, but they also introduce a dependency: changing the checkbox values later may break conditional formatting that still looks for the old value. If you change the validation settings, revisit the custom formula and any other formulas that refer to the checkbox column.

Highlight only when other conditions are met

A checkbox does not have to be the only condition. You might want a row to turn green only when its checkbox is checked and a task name has been entered. With checkboxes in A2:A100, task names in B2:B100, and the applied range A2:E100 beginning at row 2, use =AND($A2=TRUE,$B2<>""). The AND function requires both conditions to be true: the checkbox is checked and the task-name cell is not blank.

Another useful pattern is to highlight incomplete items instead. With the same layout, apply a rule based on =$A2=FALSE if the unchecked state should receive a warning color. If you use that rule across rows that do not yet contain tasks, blank rows may also be highlighted because their checkbox cells may be unchecked. You can prevent that by adding a nonblank test for the task name, as in the previous example, while reversing the checkbox condition.

Keep multiple rules understandable. If a row can match more than one conditional-formatting rule, its appearance may be affected by overlapping formatting choices. Give each rule a clear purpose, check the rules listed in the Conditional format rules panel, and use different styles only when the distinction is meaningful.

Common problems and how to fix them

The wrong row changes: Check that the formula row number matches the first row in “Apply to range.” A range starting at row 3 generally needs a reference to row 3.
Every row responds to one checkbox: The checkbox column may not be locked. Use =$A2=TRUE, not =A2=TRUE when formatting multiple columns, so the reference stays in column A as the rule moves across the row.
Nothing changes when you click: Confirm the cell is an inserted checkbox and check whether it uses custom values. A formula comparing with TRUE will not match a custom checked value such as Complete.
Only the checkbox cell gets colored: Expand “Apply to range” to cover the other columns in the row. The formula determines when to format; the range determines where formatting can appear.
Headers are highlighted: Exclude the header row from the applied range, or make sure the formula’s row reference matches the first data row rather than the header.

When debugging, temporarily use a conspicuous fill color so it is easy to see which cells the rule affects. Then inspect one checked row and one unchecked row, and verify the formula against the actual checkbox value. If the rule was copied from another sheet or moved after rows were inserted, recheck both its formula and its applied range.

Conditional formatting versus other ways to mark a row

Conditional formatting is usually the simplest choice when the row should respond automatically to a checkbox. It keeps the checkbox as the source of truth and updates the appearance immediately when the state changes. It is also reversible: unchecking the box removes the conditional style, while the row’s text and other cell values remain intact.

Manual fill color is different. A person can apply a color from the toolbar, but it will not automatically disappear when the checkbox is cleared. A separate status column can communicate completion with text, and a filter can show only checked or unchecked records, but neither by itself gives the same automatic row highlighting. These methods can complement conditional formatting—for instance, a status column can make the state explicit while color provides a quick visual cue.

Google Sheets conditional-formatting steps are not identical to Excel steps. Excel has its own conditional formatting interface and checkbox options that vary by version. The formula idea—referencing the checkbox cell relative to each row—is broadly similar, but the menu paths and availability of checkbox controls differ. The steps here are specifically for Google Sheets in a web browser.

Frequently Asked Questions

Can Google Sheets highlight an entire row when I check a box?
Yes. Set the conditional-formatting range to all the row cells you want colored, then use a custom formula that references the checkbox column, such as =$A2=TRUE when the checkbox is in A2 and the range starts on row 2.

Do I need to write TRUE in the formula?
No. For standard checkboxes, =$A2 is also valid because a checked box evaluates to TRUE. The explicit comparison makes the condition easier for many people to read. For a checkbox with custom values, compare against its checked value instead.

Can I highlight the row without coloring the checkbox cell?
Yes. Set “Apply to range” to the other columns, such as B2:E100, and keep the formula pointed at the checkbox in column A. The condition cell and the cells receiving the format can be different.

Why is the row above or below the checked box highlighted?
The formula’s row reference is likely misaligned with the first row of the applied range. If the range starts at row 2, reference row 2; if it starts at row 5, reference row 5. Also check that the range does not accidentally include the header.

Will the formatting work with custom checkbox values?
Yes, but the custom formula must test the checked value you configured. If the checked value is Complete, compare the checkbox cell with Complete rather than TRUE. The value and capitalization should match the validation setup.

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.