Google sheet conditional formatting based on another cell

Conditional formatting is a super useful technique for formatting cells in your Google Sheets based on whether they meet certain conditions.In this post, youll learn how to apply c

Google sheet conditional formatting based on another cell

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

In this post, youll 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):

Conditional formatting across an entire row

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

How To Apply Conditional Formatting Across An Entire Row

Its 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, Ive selected:A2:C13

Conditional Formatting step 1

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:

Conditional Formatting menu

Step 3. Choose Custom formula is rule

Google Sheets will default to applying the Cell is not empty rule, but we dont want this here.

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

Conditional Formatting sidebar

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:

Conditional Formatting custom formula

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:

Conditional formatting across an entire row

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

Its 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 Beginners 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, Ive highlighted all of the students who scored less than 60 in class, using this formula in the custom formula field:= $C2 < 60

Conditional formatting across an entire row

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

Conditional Formatting with checkbox in Google Sheets

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")

Conditional Formatting And Rule in Google Sheets

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.

Conditional Formatting Or Rule

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.

Conditional Formatting Or Test Google Sheets

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.

Google Sheets Essentials course

Learn more
Check out my beginner course and master key techniques to become confident with Google Sheets: Google Sheets Essentials

Video liên quan