You can use Conditional Formatting in Google Sheets to format a cell based on its value.

For example, suppose you have a data set of students scores in a test (as shown below). You can use Conditional Formatting to highlight cells with the score less than 35 in red and with more than 80 in green.

But what if you want to highlight a cell based on other cells value.

For example, highlight the name of students who got less than 35 in Math.

You can highlight cells using conditional formatting based on another cell value in Google Sheets.

### Highlight Cells Using Conditional Formatting Based on Another Cell Value in Google Sheets

Suppose you have the data set as shown below:

Here are the steps to highlight cells with names based on the scores:

- Select the cells that have the names (A2:A11).
- Go to the Format Tab.
- Click on Conditional Formatting.
- In the Conditional Formatting rules pane, select Single Color.
- From the ‘Format Cells if’ drop down, select ‘Custom Formula is’.
- In the Formula field, enter the formula: =B2<35
- Specify the format by clicking on the Formatting Style drop down.
- Click on Done.

You will see that the cells that contain the names are highlighted based on the scores in the adjacent cells.

**How does this work?**

Conditional formula highlights the cell when a given condition evaluates to TRUE; else it does nothing.

In the above example, while we selected the cells that contain names (A2:A11), the formula used the values from the adjacent column.

For example, for cell A2, conditional formatting evaluated the formula B2<35. Since the value in B2 is 12 and the formula evaluates to TRUE, it got highlighted.

Similarly, for cell B3, the value in cell A3 is evaluated and so on.

### Highlight Cells Using Conditional Formatting Based on Other Cells Value

In the above example, you saw how to use highlight cells using conditional formatting based on another cell value in Google Sheet.

However, what if you want to evaluate multiple cells and then highlight cells based on the result.

For example, suppose I have a data set as shown below:

Now suppose you are the teacher and you want to highlight the students who have failed in one or more than one subjects. At the same time, you also want to highlight the names of the students who have scored more than 80 in all the three subjects.

Here are the steps to do this:

- Select the cells that have the names (A2:A11).
- Go to the Format Tab.
- Click on Conditional Formatting.
- In the Conditional Formatting rules pane, select Single Color.
- From the ‘Format Cells if’ drop down, select ‘Custom Formula is’.
- In the Formula field, enter the formula: =OR(B2<35,C2<35,D2<35)
- Specify the format by clicking on the Formatting Style drop down. Since we are highlighting students who failed, I chose the red color.
- Click on Done.
- In the Conditional Formatting Pane, click on the ‘Add new rule’ option.
- In the Conditional Formatting rules pane, from the ‘Format Cells if’ drop down, select ‘Custom Formula is’.
- In the Formula field, enter the formula: =AND(B2>80,C2>80,D2>80)
- Specify the format by clicking on the Formatting Style drop down.
- Click on Done.

This would instantly highlight the names as shown below:

**You May Also Like the Following Google Sheets Tutorials:**

## 15 thoughts on “Conditional Formatting Based on Another Cell Value in Google Sheets”

I am testing a conditional formula using the Excel Absolute Value convention. But when I copy and paste the conditional formula =Left($c5,9)=”Duplicate” into row 7, the formula does not change the 5 to 7 as Excel would. It keeps it at C5, even though the $ symbol is only for the Column. In Excel, the row number would still be relative to the row it is copied to.=Left ($c7,9)=”Duplicate”.

Doesn’t seem to me to be a complete copy of the Excel convention.

Very helpful! Is there a custom formula that you could help me with that I could use to change the cell color of an empty cell based on exceeding a set number of days past a entered date in another column?

My value in E2 will change weekly. I need to apply conditional formatting rules that highlight everything in column E (starting at E3 and down) based on a range from the value of E2 + .001 to .499 — how do I enter that in conditional formatting?

I can get the rule to on the same sheet. But i want a cell to change colour on sheet 2 based on the result on sheet 1. Its a simple yes no answer on sheet one. Cell in sheet 2 will display green if Yes.

Condition Format rule I can have work on same page is

=B4=”Yes”

I have tried

=Sheet!1B4=”Yes”

Comes up with invalid msg. Can this be done across pages or will I need to work out some IF codes or something

=($E$9=”Y”)

Worked for mine. It took a minute to figure out it. Not sure if you have it by now, but this was what I used. I needed to mark things with yes or no as well.

I figured it out in Google Sheets! Use conditional formatting for ONE CELL (then you’re going to grab the small + sign to copy formula to all the rest of them after you get the first one started). Suppose in the following, you want cell B highlighted in Green if it’s GREATER than A, and Red if it’s less than.

Example: A B

1: 5 10

2: 5 1

You will have 2 formulas for B1.

First one: conditional formatting, custom formula is from drop down, then box below =(B1>A1) . Color: Green

Second one: conditional formatting, custom formula is from drop down, then box below =(B1<A1) .

Color: Red

Now hover over B1 until you see the small + sign in small right corner…..drag it all the way down to all the B cells. Voila!

I agree. I too am trying to get a LOT of data formatted for finances. If Cell A has a value….all I want is when the value in B is lower than what is in A, I want it red. When it’s greater than the number in A, I want it green. It will be very tedious if I have to go to EVERY B cell and tell it a SPECIFIC number from cell A, plus my cell A changes a lot!

Example: A B

1: 5 10

2: 5 1

I want B1 to be green, B2 to be red.

How does this work if you are trying to compare values of 2 cells and identify by color of one is greater than or less that the other. For instance you have Cell A1:A9 containing integer values, and Cell B1:B9 containing integer values, and you want to compare each values for each column (A1) and (B1) and color A1 Yellow if its less than B1, and Red A1 is less than B1. Then do the same for each row.

Create 1 rule for the yellow condition, then create another rule for the red condition. The rules will have the same range, similar formula (reversing the less than/greater than), and different color formats.

HI,

Did you find out what to type as the formula for this please?

Hi, I am trying to figure out how to turn one cell green, yellow, red, etc. based on the contents of another cell but I cannot get the suggestions above to work. Please advise.

You have to use $ to reference the specific cell i.e.

Range A1:C200

Format Cell green if

Greater than

=$L$2

Cell L2 reads 5%

Result: All cells in range A1:C200 Greater than 5% are highlighted green

This is helpful, thanks!!

I am trying to format based on if the entry is actually the same as the another cell. This is for multiple choice entry and there are 100 questions. I know how to do it in xcel, but cannot figure it out in sheets. I need it to validate against the value within a given cell, not based on an entered value range or value. For example this way I can reuse the sheet for any multiple choice test and just change the answer key row and the formatting will just pull from the cell’s new value.

an example with dates and date ranges would help a lot. I want to highlight a calcuated date that falls within a week or two weeks, etc of the current date.