How to Highlight an Entire Row in Google Sheets Based on a Cell Value

Conditional formatting in Google Sheets makes it straightforward to highlight individual cells that match specific criteria—such as flagging overdue dates or marking cells containing numbers above a certain threshold. However, when managing project trackers, inventory manifests, or sales pipelines, highlighting only a single cell often leaves the rest of the record hard to scan visually.

Highlighting the entire row based on the value of a single cell transforms your spreadsheet into an interactive dashboard, allowing team members to identify completed tasks, urgent orders, or cancelled bookings at a glance. Achieving this in Google Sheets requires understanding how to construct custom formulas with absolute column references. This guide walks you through the exact formula syntax, step-by-step setup, multi-condition workflows, and common mistakes to avoid.

The Key Principle: Locking the Column Reference

The secret to highlighting an entire row lies in the difference between relative and absolute cell references in spreadsheet formulas:

• Relative Reference (B2): As conditional formatting evaluates each cell across columns A, B, C, and D, the reference shifts horizontally. Cell A2 looks at A2, cell B2 looks at B2, and cell C2 looks at C2. This results in only the matching cell being formatted.
• Absolute Column Reference ($B2): Placing a dollar sign ($) directly before the column letter locks the evaluation to column B. As formatting scans across every column in row 2, every cell in that row is forced to inspect the value inside column B. Because the row number (2) remains relative without a dollar sign, the rule moves down naturally from row 2 to row 3, row 4, and beyond.

By locking the column while keeping the row dynamic, the formatting condition evaluates identically across the entire horizontal span of each record.

Step-by-Step: Highlighting Rows Based on a Text Value

Suppose you have a project tracking sheet with data spanning from column A to column E, beginning at row 2 (row 1 contains column headers). Column D contains project statuses: “Completed”, “In Progress”, and “Delayed”. You want to highlight the entire row in soft green whenever column D says “Completed”.

1. Open your spreadsheet in Google Sheets.
2. Select your data range by clicking and dragging from cell A2 down to the bottom-right corner of your active table (for example, E100). Do not include your header row (row 1) in the selection.
3. In the top menu, click Format and select Conditional formatting. The Conditional format rules panel opens on the right side of your browser.
4. Under the Single colour tab, confirm that the Apply to range box shows A2:E100.
5. Under the Format rules heading, click the dropdown menu currently set to “Format cells if…” and scroll down to the bottom to select Custom formula is.
6. In the Value or formula input field, type the following formula:

   =$D2="Completed"

7. Under Formatting style, choose your desired fill colour (such as light green) or text styling (such as bold or strikethrough).
8. Click Done to apply the rule.

Every row where column D equals “Completed” will immediately display the green background across all selected columns.

Highlighting Rows Based on Numeric Comparisons

You can use standard mathematical operators (>, <, >=, <=, <>) to highlight entire records based on numerical thresholds.

Highlighting Records Below a Reorder Threshold

If column C tracks inventory quantities and you want to highlight any item with fewer than 10 units in stock:

=$C2<10

Highlighting Invoices Above a Specific Total

If column E tracks invoice amounts and you want to flag any customer order exceeding £1,000:

=$E2>1000

Highlighting Rows Based on Checkbox Ticks

Google Sheets includes native interactive checkboxes (inserted via Insert > Checkbox). When checked, a checkbox cell evaluates to boolean TRUE; when unchecked, it evaluates to FALSE.

Suppose you place interactive checkboxes in column A. To strike through and dim the entire row whenever a task’s checkbox is ticked:

1. Select your table range (for example, A2:F50).
2. Open Format > Conditional formatting.
3. Set the rule to Custom formula is.
4. Enter the formula:

   =$A2=TRUE

5. Under formatting style, choose a light grey fill colour, a subtle text colour, and enable Strikethrough.
6. Click Done.

Ticking any checkbox now automatically applies the strikethrough effect across the entire completed row in real time.

Highlighting Rows Based on Multiple Conditions

You can combine multiple criteria using the AND() and OR() logical functions.

Using the AND Function (All Conditions Must Be Met)

To highlight rows where the status in column D is “Delayed” and the priority in column C is “High”:

=AND($D2="Delayed", $C2="High")

Using the OR Function (Any Condition May Be Met)

To highlight rows where the status is either “Cancelled” or “Refunded”:

=OR($D2="Cancelled", $D2="Refunded")

Applying Different Colours for Multiple Statuses

To create a full status board where “Completed” rows turn green, “In Progress” rows turn yellow, and “Delayed” rows turn red, create multiple distinct rules within the same range:

1. Open the Conditional format rules panel with your range selected (A2:E100).
2. Click Add another rule.
3. Create rule 1: Custom formula =$D2="Completed" with green fill.
4. Create rule 2: Custom formula =$D2="In Progress" with yellow fill.
5. Create rule 3: Custom formula =$D2="Delayed" with soft red fill.

Rules in Google Sheets are prioritised from top to bottom. If a row satisfies more than one rule, the topmost rule in the list takes precedence. You can reorder rules by hovering over a card in the panel and dragging the three-dot handle up or down.

Troubleshooting Common Row Highlighting Mistakes

Problem 1: The Highlight Appears in Staggered or Wrong Columns

This occurs when the starting row in your applied range does not match the starting row in your custom formula. If your range is set to A2:E100 but your formula references =$D1="Completed", the formatting will be offset by one row. Always ensure the row number in your formula matches the very first row of your selected range.

Problem 2: Only the Target Cell Changes Colour, Not the Entire Row

You omitted the absolute reference dollar sign ($) before the column letter (e.g. typing =D2="Completed" instead of =$D2="Completed"). Add the dollar sign before the column letter to lock the horizontal evaluation.

Problem 3: Case Sensitivity and Whitespace

Standard text comparisons in Google Sheets custom formulas are case-insensitive (both “completed” and “Completed” match). However, leading or trailing whitespace inside cells will cause comparisons to fail. To guard against accidental spaces typed by collaborators, wrap your target cell in the TRIM() function:

=TRIM($D2)="Completed"

Mastering whole-row conditional formatting gives your Google Sheets professional visual hierarchy, making complex datasets intuitive to read and update collaboratively.