How to Highlight Rows Based on Cell Value in Google Sheets

A well-organized spreadsheet shouldn't require you to read every row to understand what's going on. Color-coding rows based on cell values transforms a wall of text into a visual dashboard — overdue tasks jump out in red, active items glow green, and pending items stand out in yellow.

In this guide, you'll learn 5 methods to highlight entire rows based on cell values, from basic conditional formatting to automated rules that update continuously.

Why Color-Code Your Rows

• Instant visibility: Spot critical items in seconds without reading every cell.
• Prioritization: See overdue tasks, low inventory, or high-value leads at a glance.
• Error detection: Unusual values (negative numbers, missing dates) become immediately visible.
• Team collaboration: Color-coded sheets are self-documenting — new team members understand the data faster.
• Status tracking: Track workflow stages (pending → in progress → done) visually.

Method 1: Conditional Formatting — Single Cell

Google Sheets' built-in conditional formatting can highlight individual cells based on their values. This is the simplest starting point.

Steps:

1. Select the cells you want to format (e.g., Column C — the "Status" column).
2. Go to Format → Conditional formatting.
3. Under "Format cells if", choose "Text is exactly".
4. Enter the value (e.g., Overdue).
5. Choose a fill color (e.g., red).
6. Click "Done".

Result: Only cells in Column C that say "Overdue" will turn red. But the entire row won't be highlighted — just the matching cell.

Method 2: Custom Formula — Highlight the Entire Row

This is the most powerful built-in method. A custom formula lets you highlight every cell in a row based on the value of one specific column.

Steps:

1. Select your entire data range (e.g., A2:F100).
2. Go to Format → Conditional formatting.
3. Under "Format cells if", choose "Custom formula is".
4. Enter the formula:

=$C2="Overdue"

5. Choose your fill color (e.g., light red: #fce8e6).
6. Click "Done".

How It Works

When you apply this to range A2:F100:

• For row 2: checks if $C2 = "Overdue" → highlights A2:F2
• For row 3: checks if $C3 = "Overdue" → highlights A3:F3
• For row 4: checks if $C4 = "Overdue" → highlights A4:F4
• ...and so on for every row in the range.

Method 3: Multiple Colors for Multiple Values

Want different colors for different statuses? Create multiple conditional formatting rules.

Example: Traffic Light System

Steps:

1. Select range A2:F100.
2. Add the first conditional formatting rule (e.g., Active → green).
3. Click "Add another rule".
4. Repeat for each status/color combination.
5. Drag rules to reorder priority (top rules take precedence).

Method 4: Date-Based Highlighting

Highlight rows based on how old or recent a date is — perfect for tracking overdue tasks, expiring subscriptions, or aging inventory.

Common Date Formulas:

Method 5: CleanSheet Automated Rules (Easiest)

If conditional formatting feels limited — no scheduled updates, complex formula syntax, no action beyond coloring — CleanSheet takes row highlighting to the next level.

What CleanSheet Adds Over Conditional Formatting:

Steps:

1. Install CleanSheet (free trial).
2. Open the sidebar from Extensions → CleanSheet.
3. Create a rule:
   • Select sheet and column
   • Choose condition: "equals" → Overdue
   • Action: "Highlight" → pick color
4. Add more conditions with AND/OR for complex rules.
5. Preview → Run → Optionally enable auto-schedule.

Real-World Use Cases

🏢 CRM: Flag Overdue Leads

Highlight leads that have been "Pending" for more than 30 days. Your sales team instantly sees which prospects need follow-up.

Rule: IF Status = "Pending" AND Date > 30 days old → Highlight yellow

📦 Inventory: Low Stock Alert

Color-code products that are running low. Red for out of stock, yellow for below reorder point.

Rule: IF Quantity < 10 → Highlight red | IF Quantity < 50 → Highlight yellow

📋 Project Management: Overdue Tasks

Automatically flag tasks past their deadline that aren't marked as complete.

Rule: IF Due Date < Today AND Status ≠ "Done" → Highlight red

💰 Finance: Large Transactions

Highlight transactions above a certain threshold for review.

Rule: IF Amount > $10,000 → Highlight blue

Frequently Asked Questions

How do I highlight an entire row based on one cell's value?

Select the entire data range, go to Format → Conditional formatting, choose "Custom formula is", and enter a formula like =$C2="Overdue" (using a $ before the column letter but not the row number). Set your desired fill color and click Done.

Can I use multiple colors for different values?

Yes! Create multiple conditional formatting rules, each with a different formula and color. For example, green for "Active", yellow for "Pending", and red for "Overdue". Rules are applied in order, so put higher-priority rules at the top.

Can I highlight rows based on dates?

Yes. Use custom formulas like =$D2<TODAY()-30 to highlight rows where the date in Column D is older than 30 days. CleanSheet also has built-in "older than X days" conditions that make date-based highlighting even easier — no formulas needed.

Why isn't my conditional formatting working on the entire row?

The most common mistake is the formula reference. Make sure you use =$C2 (dollar sign on column only), not =$C$2 (locked to row 2) or =C2 (shifts both column and row). Also ensure your "Apply to range" covers all columns you want to highlight.