how to add conditional formatting in google sheets based on another cell

Master Conditional Formatting in Google Sheets Based on Another Cell (Step-by-Step Guide)

By Rachel Harris
AD

Conditional formatting is one of Google Sheets’ most powerful features, yet many users only scratch the surface by applying rules to a single cell. If you want to highlight an entire row based on a status in another column or change the font color when a value in a separate cell meets a specific condition, you need to master relative referencing in custom formulas. This technique allows your formatting logic to dynamically adjust based on context, giving you far more control than simple icon sets.

Understanding the Core Concept: Relative Referencing

The key to formatting based on another cell lies in how you structure your range and formula. When you select a range to format, such as A2:Z100, the conditional formatting rule evaluates the formula relative to the active cell in that range, usually the top-left corner. To base a condition on a different cell, you must anchor the formula to the specific row or column you want to evaluate while allowing the rest of the selection to change dynamically.

Setting Up the Data Structure

Before writing the rule, organize your data logically. For example, if you are flagging high-priority tasks, your sheet might have columns for Task Name, Status, and Priority. The status (e.g., "Overdue," "Complete," or "Pending") might live in column C, while the priority level in column D. If you want to highlight the entire row based on the status in column C, your formula will reference column C relative to the current row.

AD
How to Use Conditional Formatting Based On Another Cell in Google Sheets
How to Use Conditional Formatting Based On Another Cell in Google Sheets
Google Sheets - Conditional Formatting Based on Another Cell
Google Sheets - Conditional Formatting Based on Another Cell
How to use conditional formatting in Google Sheets | Zapier
How to use conditional formatting in Google Sheets | Zapier

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

To apply formatting to an entire row based on a value in another column, follow these steps. First, select the range you want to format, such as A2:Z100 to cover all data. Then, navigate to Format > Conditional formatting. In the Format rules panel, choose "Custom formula is" and enter a formula that uses the relative reference for the target column. For instance, to highlight rows where column C contains "Overdue," use the formula =C2="Overdue". Note that the "2" corresponds to the second row of your selected range, allowing the formula to adjust for row 3 as =C3="Overdue", and so on.

Using Mixed References for Column Locking

Sometimes you need to base formatting on a cell that exists in a fixed location, regardless of where the formatted range is. For example, you might want to highlight cells in column B if the corresponding value in column A is greater than 100. Here, you use a mixed reference to lock the column. Select the range B2:B100 and use the formula $A2>100. The dollar sign before the column letter A locks it, while the row number remains relative, ensuring the rule checks the correct row in column A for each row in column B.

How to Use Conditional Formatting in Google Sheets
How to Use Conditional Formatting in Google Sheets
Conditional Formatting with Google Sheets | ExcelMaster1
Conditional Formatting with Google Sheets | ExcelMaster1
ARRAYFORMULA & 5 ways to apply a formula to an entire column in Google Sheets
ARRAYFORMULA & 5 ways to apply a formula to an entire column in Google Sheets

Advanced Logic with AND and OR Conditions

Real-world scenarios often require multiple conditions. Suppose you want to format rows where the status is "Urgent" and the priority is "High." You can combine logical tests using the AND function. Select your data range and use a formula like =AND(C2="Urgent", D2="High"). This ensures that both conditions in the same row are met before applying the format. Conversely, the OR function allows you to highlight a row if any of several conditions are true, such as =OR(E2="Review", E2="Pending"), which is useful for tracking workflow stages.

Handling Dates and Numerical Comparisons

Conditional formatting shines when working with dates, such as highlighting upcoming deadlines. To flag rows where the due date in column F is within the next 7 days, use a formula like =AND(F2<=TODAY()+7, F2>TODAY()). This checks that the date is in the future but not beyond a week from now. For numerical comparisons, such as alerting when a sales figure in column G exceeds a target in a separate summary cell, you might reference an absolute cell like $H$2 to ensure every row in the range compares against the same target value.

AD

Troubleshooting Common Issues

Incorrect ranges or misplaced dollar signs are the most common causes of failure. If your formatting isn't applying, double-check that the range in the rule matches the actual data range and that the formula references the correct column letters and row numbers. Another pitfall is applying the rule to the wrong starting cell; remember, the formula is relative to the top-left corner of your selected range. Test your custom formula in a spare cell using the same logic to verify it returns TRUE or FALSE as expected before applying the format.

Google Sheets Tip: Conditional Formatting Custom Formulas – TCEA TechNotes Blog
Google Sheets Tip: Conditional Formatting Custom Formulas – TCEA TechNotes Blog
How to highlight or conditional formatting the max or min value in Google sheet?
How to highlight or conditional formatting the max or min value in Google sheet?
5 Reasons You Should Dump Excel and Start Using Google Sheets
5 Reasons You Should Dump Excel and Start Using Google Sheets
Google Sheets Formula Cheat Sheet
Google Sheets Formula Cheat Sheet
two screens showing different types of forms and numbers
two screens showing different types of forms and numbers
How to Group Rows and Columns in Google Sheets
How to Group Rows and Columns in Google Sheets

Best Practices for Maintainability

To keep your sheet manageable, use named ranges for critical reference cells, such as turning $H$2 into a target value named "SalesTarget." This makes formulas easier to read and update. Additionally, organize your rules by going to Format > Conditional formatting > Manage rules, where you can prioritize and delete obsolete conditions. Consistent formatting based on dynamic logic not only improves visual clarity but also reduces the need for manual updates as your data grows.

an image of a calendar with dates and times listed in the top right hand corner
an image of a calendar with dates and times listed in the top right hand corner
How to Automatically Change Cell Color in Google Sheets
How to Automatically Change Cell Color in Google Sheets
Conditional Formatting | Excel Cheatsheets
Conditional Formatting | Excel Cheatsheets
CHECK the Conditional Formatting - Teacher Tech with Alice Keeler
CHECK the Conditional Formatting - Teacher Tech with Alice Keeler
Excel Conditional Formatting tutorial with examples
Excel Conditional Formatting tutorial with examples
the spreadsheet is open and ready to be used for project management, as well as other tasks
the spreadsheet is open and ready to be used for project management, as well as other tasks
google sheets quick reference guide with keyword shortcuts
google sheets quick reference guide with keyword shortcuts
6 Crazy Google Sheets Formulas You Should Know
6 Crazy Google Sheets Formulas You Should Know
excel create color - coded drop - down menu
excel create color - coded drop - down menu
Ultimate Daily Routine Framework | Google Sheets & Excel Tracker📈
Ultimate Daily Routine Framework | Google Sheets & Excel Tracker📈
Add a Dynamic Mini Calendar in Google Sheets – Easy & Beginner-Friendly
Add a Dynamic Mini Calendar in Google Sheets – Easy & Beginner-Friendly
How to Use the SORTN Function in Google Sheets
How to Use the SORTN Function in Google Sheets
Free Spreadsheet Templates for Excel & Google Sheets
Free Spreadsheet Templates for Excel & Google Sheets
Analyze text using natural language with Claude for Google Sheets
Analyze text using natural language with Claude for Google Sheets
AD