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

Standard conditional formatting in Google Sheets makes it simple to highlight a single cell if it contains a number or text. However, in project trackers, client pipelines, and inventory sheets, you almost always need to highlight an entire row based on the status of a single column (for example, lighting up the whole row green when column E says "Complete").

In this guide, you will learn the exact formula syntax to format rows based on another cell's value, how to lock column references with dollar signs ($), and how to apply formatting based on text, numbers, and dates.

1. The Secret to Highlighting Entire Rows: The Dollar Sign ($)

When you apply conditional formatting across multiple columns (like A2:F100), Google Sheets evaluates each cell individually by default. To force Sheets to check only one specific column while styling every column in the row, you must lock the column letter using a dollar sign ($).

• E2 = "Paid": Shifts as the rule checks column B, C, D... (Formatting breaks).
• $E2 = "Paid": Locks Google Sheets to look strictly at Column E, applying the style across the entire matching row.

2. Step-by-Step: Highlight a Row Based on Text (e.g., "Complete" or "Pending")

Step 1: Select Your Data Range

Highlight your full dataset excluding the header row. For example, select A2:F50.

Step 2: Open Conditional Formatting

Click on Format in the top menu and choose Conditional formatting.

Step 3: Choose Custom Formula Is

1. In the panel on the right, verify your Apply to range reads A2:F50.
2. Under Format rules, open the "Format cells if..." dropdown and select Custom formula is.
3. Enter the following formula into the input field:

=$E2="Complete"

4. Under Formatting style, select your preferred background fill color (for example, soft green).
5. Click Done.

Note: Text values inside the formula must always be wrapped in double quotation marks ("Complete"). Text matching is not case-sensitive, so "complete" matches "Complete" and "COMPLETE".

3. Highlight Rows Based on Numbers (Greater Than / Less Than)

To highlight entire rows where a budget or quantity exceeds a threshold (for example, flagging rows where order total in Column D is greater than 500):

=$D2 > 500

Do not use quotation marks around numeric values. Quotation marks force Google Sheets to read the entry as text, which prevents mathematical comparisons.

4. Highlight Rows Based on Dates (Overdue Items)

If you are tracking deadlines in Column C and want to flag every row that is overdue compared to today's date:

=AND($C2 <> "", $C2 < TODAY())

The AND($C2 <> "", ...) check ensures that empty, unpopulated date cells do not accidentally get highlighted as overdue.

5. Common Mistakes to Avoid

Recommended Next Steps

Now that you can style data dynamically based on cell values, explore these related tutorials to automate your workflow even further:

• Track Deadlines Automatically: Set up automated alerts before tasks become overdue using our guide on how to automatically send email reminders for overdue dates in Google Sheets.
• Protect Formatted Tables: Prevent team members from accidentally overwriting your conditional formulas with our tutorial on how to lock and protect specific cells in Google Sheets.
• Measure Metric Changes: Track before-and-after variance across your formatted project data using our guide on how to calculate percentage change in Google Sheets.