To perform Google Sheets conditional formatting based on another cell, you simply have to use the Custom formula is option in the conditional formatting menu. But, the trick is to know what kind of custom formula you need to build. Read on to learn step by step how to perform conditional formatting based on other cells with several easy to understand examples.
What is Conditional Formatting?
Conditional formatting is a tool in Google Sheets with a lot of functions such as highlighting alternate rows. 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 on a test (as shown below). You can use Conditional Formatting to highlight cells with a score of less than 35 in red and with more than 80 in green (because red=bad).
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.
Tackle Everything at Once
Learning to conditionally format based on another cell is an intermediate skill that newer users may have some trouble with. If you feel like you fit into this category, don’t worry. You can take a comprehensive Google Sheets course to brush up on your skills. Or, if you need to learn about Excel too, there are courses on LinkedIn for that too.
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 us Google Spreadsheet conditional formatting based on another cell 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 menu, select ‘Custom Formula is’.
- In the Formula field, enter the formula: =B2<35
- Specify the format by clicking on the Formatting Style drop down menu.
- 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 data validation 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 cell 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. Note that you can use a similar method to highlight duplicates.
Google Spreadsheet Conditional Formatting Based On Another Cell Value
In the above example, you saw Google Sheet conditional formatting based on another cell.
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 subject. At the same time, you also want to highlight the names of the students who have scored more than 80 in all three subjects.
Here are the steps to do this:
- Select the data range that has 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. Here you can choose a background color from the color scale. 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 to create new format rules:
=AND(B2>80,C2>80,D2>80)
- Specify the color from the color scale in the Formatting Style.
- Click on Done.
This would instantly highlight the names as shown below:
Google Sheets Conditional Formatting Based on Another Cell: Text
You can apply conditional format rules in Google Sheets conditional formatting if another cell contains text too. You just have to enter the text string into quotation marks in the custom formula.
For a simple example, let’s say you work in a co-ed primary school, and you need to suggest the top-performing female students for an all-girls private school scholarship program. You could use conditional formatting to highlight all the female students. We’ll use a small fake data sample to show how it would work.
To highlight all the students that are female, we can conditionally format the column with their names to green using a text rule. To do so, all we would have to do is:
- Highlight the cells you want to apply the format rules to (A2:A9)
- Navigate to Format > Conditional formatting
- Change the Conditional dropdown box to Custom formula is
- Enter
=B2="F"
Let’s say we wanted to also add the non-binary students. We could use the OR function to do this, like so:
=OR(B2="F",B2="NB")
Conditional Formatting Based on Another Cell Range
Instead of just one cell, you can also use a cell range as an argument for your conditional formatting rule.
Looking at our example sheet, we can highlight the names of students who scored below 50 in Math.
Here’s how to do Google Sheets conditional formatting based on another column:
- Highlight the cell range you want to apply the format rules to
- Go to Format > Conditional formatting.
- Click the formula drop-down menu and choose Custom formula is.
- Enter the formula =B2:B<50
- Choose the color you want to use from the color scale in the formatting style.
- Click Done.
Conditional formatting will apply the format rules highlight all the cells in column A that correspond to a cell in Column B that is less than 50.
Google Sheets Conditional Formatting Based on Another Cell Color
Unfortunately, at the moment, there is no way to use Google Sheets custom formatting based on another cell color. You can only do so based on cells with numerical data values, text, dates or empty cells.
The only way around this is to use the value in the cells with color as the conditional format rules. But, you could highlight a cell if a Google Sheets checkbox is ticked or not.
Frequently Asked Questions
Can You Do Conditional Formatting Based on Another Cell in Google Sheets?
Yes, you can conditionally format a cell based on another cell. All you have to do is:
- Highlight the cell range you want to apply the format rules to
- Navigate to Format > Conditional formatting
- Change the Conditional dropdown box to Custom formula is
- Enter a formula such as =>B2
- Click Done
How Do You Use Conditional Formatting Based on Another Color to Show Red if Wrong and Green if Correct?
You can set your conditional format rule so that it will show green when the correct answer is typed in and red when the wrong answer is typed in.
Let’s look at an example. We want cell B2 to show green if the correct answer is put in and red if the wrong answer is typed.
To do this, you will need to use three format rules:
- One for if the cell Is equal to (the correct answer)
- Another one for if the cell Is not equal to (the wrong answer)
- The last one for if the cell is empty
Here’s how to do it:
Is equal to
- Highlight the cell range you want to apply the format rules to
- Go to Format and select Conditional formatting
- Choose Is equal to in the formula dropdown
- Type =A17 (the cell with the correct formula)
- Choose the color green in the formatting style color scale.
Is not equal to
- Click Add rule.
- Choose Is not equal to in the formula dropdown
- Type =A17 (the cell with the correct formula)
- Choose the color red in the formatting style color scale.
Is empty:
You will need to add another rule for if the cell is empty and choose white as the background color. Make sure this cell is on top of the rule for Is not equal to.
If a value matching cell A4 is typed in, the cell will highlight green, and if any other value is typed in, it will turn red, as shown below.
How Do You Use Greater Than and Less Than Together to Conditionally Format?
If you want to conditionally format your cell range using greater than and less than at the same time, then you can use the IFS function in your format rule instead of an inherent If function.
For example, if we wanted to format our sheet so that students who scored more than 40 but less than 70 are highlighted, we would use the formula:
=IFS( B2:B11>40, B2:B11<70)
Go to the conditional format window and paste this as your custom formula is.
You can see from our example that only the students who scored more than 40 but less than 70 are highlighted.
How Do You Color a Cell Based on the Value of Another Cell?
- Highlight the cell range you want to apply the format rules to
- Navigate to Format and select Conditional formatting
- Change the Conditional dropdown box to Custom formula is
- Enter a formula to set the rule for the formatting
- Select the color you want for the formatting to apply
- Click Done
How Do You Conditional Format if One Cell Is Greater Than Another?
- Highlight the cell range you want to apply the format rules to
- Navigate to Format and select Conditional formatting
- Change the Conditional dropdown box to Custom formula is
- Use the greater than operator to indicate which cell needs to have the larger figure eg: B2>C3
- Click Done
Can I Use an IF Formula in Conditional Formatting in Google Sheets?
Setting any rule for conditional formatting is essentially an IF function. For example, you could set a Custom formula is rule of B2>C3. That statements is saying IF B2 is greater than C3, highlight the cell, picture it as writing an IF function that says IF(B2>C3, highlight cell). You can also use IFS statements as custom formulas such as =IFS( B2:B11>40, B2:B11<70)
How Do I Apply Conditional Formatting Based on Another Column?
To apply conditional formatting based on another column, you simply have to use the column address as an argument in a custom formula. For example, you could use =B2:B<50 to check if any values in the B column (except for the header in B1) are over 50.
How to Conditional Format Based on Another Cell Recap
To highlight cells based on another cell in Google Sheets, you just have to follow the above easy steps in the FAQ there and use the custom formula that applies to your needs. If you found this article useful, you can also check out our guide on how to search in Google Sheets.
Want to become a Google Sheets expert? We highly recommend checking out Udemy’s Google Sheets Comprehensive Masterclass and the Complete Google Sheets Course.
If You Found This Article on Conditional Formatting Based on Another Cell Google Sheets Useful, You May Also Like the Following Google Sheets Tutorials:
- Creating a Heat Map in Google Sheets (Step-by-Step Tutorial).
- How to Make a Dependent Drop Down List
- 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 is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.
38 thoughts on “Google Sheets Conditional Formatting Based on Another Cell”
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.