How To Create Conditional Formatting In Google Sheets

Conditional Formatting Based on Another Cell Value in Google Sheets

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.

Conditional Formatting Based on Another Cell in Google Sheets - Data

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:

Conditional Formatting Based on Another Cell in Google Sheets - Single Column3

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

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

Conditional Formatting Based on Another Cell in Google Sheets - result

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:

Conditional Formatting Based on Another Cell in Google Sheets - data2

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:

Conditional Formatting Based on Another Cell in Google Sheets - multilple result

You May Also Like the Following Google Sheets Tutorials:

Creating a Heat Map in Google Sheets (Step-by-Step Tutorial).
How to Color Alternate Rows in Google Sheets.
How to Zoom In and Zoom Out in Google Sheets.
How to Insert an Image in a Cell in Google Sheets.
How to Compare Two Columns In Google Sheets
Format Painter in Google Sheets (Copy formatting easily)

Sumit

Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.

Sumit

https://productivityspot.com/author/sumitbansal23/
Sumit

https://productivityspot.com/author/sumitbansal23/
Sumit

https://productivityspot.com/author/sumitbansal23/
Sumit

https://productivityspot.com/author/sumitbansal23/

Dear Sir,

I want to mark the less than and greater than formula in one column data. For example, I want to mark the data which is greater than 0 and less than 95 in a column.

Please advise me the formula.
I am trying to format my student's reading scores. I have certain criteria that tells me if the student can read independently, instructionally, or at the frustration level. I have those points in 2 different columns. I want the book title to be color coded for each of those levels. I cannot figure out how to get the conditional formatting to be set up dependent on those 2 different columns. I tried to do it but cannot figure out how to get the formula to match where it does not matter what cell it is, but so the formula is applicable to the whole column.
Hi,

I'm trying to Highlight Rows (Range A3:F102) to color yellow if Range H1:H102 contains "Settled".
what formula should I use?

Thank you.
- I can tell you how to highlight a cell if another cell in the same row contains the word "settled" but now sure how to highlight a whole row. Email me if you'd like my explanation 🙂
=A1>B1
Conditional formatting a Range of cells based on values in another Range.
I am stuck with this and need help.
I have a 10X10 table (B2:K11) that stores multiplication table. I want student to enter a value in a cell which should turn green if the value is correct. And turn Red if incorrect.
I stored correct values in another table (M2:V11) of same size to be used as reference of correct values.
I want to use 'value is not equal to' and used formula.
What formula should I use?
I have a Google Form that has users select today's date. I want to highlight those rows where the the two dates are different. How would I compare that entry to the timestamp?
i have 19 different cells. from 19 i have 1 cell done successful conditional formatting & change color. Rest 18 cells are empty. But I want same formatting rest of the 18 cells too, same as in 1st Cell i did successfully. Remember please 18 cell are purely empty, cannot add any detail in these cell, just idle cells. Reasons being, 1 st cell has different 12 specific value and so colurs, so 12 formats. Thus i want when 1 at a time from all 12 formats appear, when 1st cell change its color so 18 empty cell around it all be of same colur, every time
hi! Does this work if A are merged cells?
I've tried to use this Conditional Formatting to change color when I change the selection in a pull down menu I've created by conditionally formatting it to change when the words in the pulled down selection are selected, but the sheet doesn't seem to recognize the words that are contained in a pull down menu choice. Any ideas anyone?
- You're using data validation for your dropdowns, correct? Are you using the 'text is exactly _____' conditional formatting option with the text from your dropdown as the condition?
  
  If you have multiple conditional formatting rules, the hierarchy of the rules may be causing your dropdown rule to fail; ie if the dropdown rule is at the bottom of the list, move it to the top so it takes precedence over any other rules.
  
  I ran into this issue with my dropdowns, they weren't updating the color based on the formatting rule because I had another formatting rule above it that was overriding it.
Hi, what if I want to match percentage values from another tab? I am trying the indirect function but it's not working to all cells.
IF A1 is greater or equal to B1 then color it

what will be the formula, please help….
I am having the same issue, can someone please advise? I want Cell in Column I row 2 to highlight Red, or Green, based on the value of the cell in Column H row 2 and I want that formula for every cell down the columns. Any ideas?
How can I create a fixed range in conditional formatting? I want the range to always be F3:F22 but I will always be adding a row above row 3 which changes the range to F3:F23. I don't want that. I want it to always stay as F3:F22. $$ do not help. I tried using OFFSET and INDIRECT in the range field but neither are allowed.

Thanks.

Dan
This is very helpful, thank you! But would like to expand on it, if we could …

What if the conditional formatting needs to be based on text and not numbers.

Example: Turn cells in column A Green, if any cells in Column B contain the word "pending".

Thanks in Advance for your assistance!
- In that case, I would use the "SEARCH" function. For example, I was documenting details about a bunch of songs in my database. Under conditional formatting, I had the range A1:I:500, custom formula, and the formula =SEARCH("LINE DANCE", $I2)>0. This searched the boxes in column I for the text "line dance" (SEARCH is not case sensitive). Whenever a cell in column I contained "line dance," the whole row A through I was highlighted. I hope that helps!
  - This worked for me!! If column "J" said Expired, then I wanted it to highlight column "J" and column "K" which had a date. I highlighted both columns, clicked Conditional Formatting, New Rule, Use a Formula to determine which cells to format", and typed in the formula =SEARCH("Expired", $J1)>0. After that I formatted both columns to highlight green and it worked 😀 Thank you!!
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.
  - not working for me`
    - Worked for me when I used =($K3="Y"), just had to retype the inverted commas (" and ") in my formula .
      - Worked for me when I used =(K$3="Y")
        To apply it to just column.
  - I have a same issue, I cannot figure out how to use data from another sheet when making a custom conditional formatting rule
    - Use indirect. So, instead of ='sheet1′!A1=1, use =indirect("'sheet1'!A1")=1
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.

Comments are closed.