Google Sheets conditional formatting based on cell color
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
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):
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
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 dont 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
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
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.
Learn more
Check out my beginner course and master key techniques to become confident with Google Sheets: Google Sheets Essentials