Google Sheets conditional formatting based on another cell not working

Conditional formatting is a super useful technique for formatting cells in your Google Sheets based on whether they meet certain conditions.

In this post, you’ll learn how to apply conditional formatting across an entire row of data in Google Sheets.

For example, if the continent is “Africa” in column C, you can apply the background formatting to the entire row (as shown by 1 and 2):

A template with all these examples is available at the end of this post.

It’s actually relatively straightforward once you know the technique using the $ sign (Step 5).

Step 1. Highlight the data range you want to format

The first step is to highlight the range of data that you want to apply your conditional formatting to. In this case, I’ve selected:

A2:C13

Step 2. Choose Format > Conditional formatting… in the top menu

Open the conditional format editing side-pane, shown in this image, by choosing Format > Conditional formatting… from the top menu:

Step 3. Choose “Custom formula is” rule

Google Sheets will default to applying the “Cell is not empty” rule, but we don’t want this here.

Click on the “Cell is not empty” to open the drop-down menu:

Scroll down to the end of the items in the drop-down list and choose “Custom formula is”. This will add a new input box in the Format cells if section of your editor:

Step 4. Enter your formula, using the $ sign to lock your column reference

In this example, I want to highlight all the rows of data that have “West” in column A. In this new input box, enter the custom formula:

The key point to understand is that you lock the column you want to base your conditional formatting on by adding a $ (dollar sign) to the column reference.

I start inputting the first cell of my highlighted range:

= A2

It’s really important that the row here matches the first row of your highlighted range (e.g. if your data is A10:C50 then your conditional formatting rule start with A10 too).

Then I add the $ (dollar sign) in front of the A only:

= $A2

Then I add the test condition, in this case whether the cell equals “West”:

= $A2 = "West"

As the conditional formatting test is applied across each row, the value from the first cell in column A is used in the check.

To learn more about using the $ sign and understand relative and absolute references, have a read of this post: How To Use Google Sheets: A Beginner’s Guide

More Examples Of Conditional Formatting Across An Entire Row

Based on a threshold value

This is a super useful application of this technique, to dynamically highlight rows of data in your tables where a value exceeds some threshold.

In this example, I’ve highlighted all of the students who scored less than 60 in class, using this formula in the custom formula field:

= $C2 < 60

Based on checkboxes

Google Sheets checkboxes are super useful. If you haven't heard of them or used them yet, you're missing out.

When a checkbox is selected it has the value TRUE, and when it is not selected the cell has the value FALSE. So we can use that property in our custom formula:

= $B2

Multi-Condition Examples

AND Example: Highlight Whole Row When Two Conditions Are Both True

Often we want to highlight based on two conditions. In this example, we'll see how to highlight the entire row when both conditions are true.

Here, we want to highlight all rows with "Apartment" in column B and "Buyer" in column D.

We use an AND Function to do this.

Highlight the dataset and add this conditional formatting custom formula:

=AND($B1="Apartment",$D1="Buyer")

OR Example: Highlight Whole Row When Condition A or Condition B Are True

This is similar to the previous example, but now we want to highlight rows where either (or both) of the conditions are true.

We use an OR Function to do this.

Conditions In Same Column

Firstly, let's see an example where the two conditions can exist in the same column.

We want to highlight all rows with "House" in column B or "Townhouse" in column B. Notice both conditions in column B.

This is the conditional formatting custom formula we use:

=OR($B1="House",$B1="Townhouse")

Conditions In Different Columns

This time, imagine the two conditions exist in different columns.

For example, suppose we want to highlight all rows with "House" in column B or sales price > $700,000 in column E.

Use this rule to achieve this:

=OR($B2="House",$E2>700000)

*** Notice how the rule starts on row 2 this time and is only applied to the data but not the header row. That's because the condition $E1 > 700000 evaluates to TRUE for the text heading (bizarre!), which we want to avoid.

Three Condition Example

Suppose we want to check for a third condition...

We combine an AND function with an OR function and use brackets to determine the precedence (i.e. the order) that the tests are carried out.

Consider this example:

=OR($B1="House",AND(ISNUMBER($E1),$E1>700000))

The rule will highlight the row when there is either:

• "House" in column B, OR
• Column E is a number AND it's greater than $700,000

The two conditions on column E must both be TRUE for the AND to evaluate to TRUE.

This rule is another way to solve the header issue in the previous example.

Conditional Formatting Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy...

If you can't access the template, it might be because of your organization's Google Workspace settings. If you right-click the link and open it in an Incognito window you'll be able to see it.