One of the most common questions I hear is how to do Google Sheets conditional formatting based on another cell. The answer is simple. I’ll show you how to use conditional formatting below, and I’ll focus specifically on how to change the formatting based on another cell. Follow along for step-by-step instructions with screenshots. That way, you’ll be able to use Google Sheets conditional formatting based on another cell.
This Article Covers:
What is Conditional Formatting?
Before we get into the more advanced topics, let’s cover the fundamentals. I already discussed conditional formatting in depth. The short version is this: It’s a way to quickly highlight important data in Google Sheets. This helps you use data to tell a story, and it’s quick. Like super quick. Conditional formatting also includes simple visual benefits like highlighting alternate rows. It’s much more exciting to use it to format based on another cell’s value.
That’s what I describe here. You’ll regularly see cells with conditional formatting based on what’s in the cell itself. This guide, however, shows you how to use conditional formatting based on what’s in another cell.
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. That gives you a quick visual reference of how many students are excelling and how many are falling behind.
That’s where it’s handy to highlight cells using conditional formatting based on another cell value in Google Sheets. Ready to learn the method?
How to Use Conditional Formatting Based on Another Cell
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. That’s the start, but it’s also important to know what kind of custom formula you need to build.
Using “Custom Formula Is” for Conditional Formatting Based on Another Cell
As always, I want to break down the syntax of the function. To do that, we’ll want to use a real-world example. So let’s consider student test scores. Here’s a data set that includes 10 names and 10 test scores:
Suppose we want to highlight a specific set of data here. Let’s use the following steps to complete Google Spreadsheet conditional formatting based on another cell to highlight cells. Our goal is simple. We’re not looking to add indents in Google Sheets or change any of the data inside. We just want to highlight names based on their corresponding scores. To do that:
- 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.
That’s it. You’ll notice that we formatted the cells in column A based on a simple evaluation in column B. The names of John and Elvis are highlighted in red because their corresponding test scores are below the 35 number we chose. In other words, we formatted cells based on adjacent cells. We could have just as easily used custom formatting based on another cell column or row. The key is to change the custom formula.
How Does the “Custom Formula is” Function Work
The reason we’re able to use conditional formatting based on another cell comes down to data validation. We evaluate whether a condition equates to TRUE. If it doesn’t, the formatting doesn’t apply. If it does equate to TRUE, we’re able to format our target cells.
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 was 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. This is the simplest version of conditional formatting, but it’s not the only one. Let’s talk about how to do this another way.
How to Use Google Sheets Conditional Formatting Based Multiple Cells
In the above example, you saw Google Sheet conditional formatting based on another cell. You can do this with multiple cells too. The method differs a little bit. Let’s talk about how to evaluate multiple cells and then highlight cells based on the result. (That includes evaluating an entire row!) You might be able to guess where this happens, as the first few steps are pretty similar.
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 instantly highlighst the names as shown below. Note that the conditional formatting, in this case, is based on multiple cells. The formatting rule takes into account a more complex formula, which evaluates multiple cells before formatting our target data. Here’s how it looks:
How to Apply Conditional Formatting Based on Text in Google Sheets
We talked about how to use conditional formatting based on data in another cell. But can you do the same kind of thing with text? You bet.
Here’s how to apply conditional format rules in Google Sheets conditional formatting if another cell contains text. The short version: You just have to enter the text string into quotation marks in the custom formula.
Here’s a real-world 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 who identify as 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")
How to Use 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. This is a little different than just basing our formatting on another cell. In this case, you’re evaluating a whole range of cells, then using Google Sheets’ conditional formatting based on the total evaluation.
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 applies the format rules to highlight all the cells in column A that correspond to a cell in Column B that is less than 50. In this case, that’s John, Elvis, and Sheldon.
Frequently Asked Questions
Here are the most common questions I hear about conditional formatting based on another cell. I’ve done my best to keep my answers concise. If you’re looking for a more detailed explanation, be sure to check out the guide above.
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
Can I Use Google Sheets Conditional Formatting Based on Another Cell Color?
Yes, you can use Google Sheets conditional formatting based on another cell color. It’s a little more complex than the methods I described above. You’ll use Google App Script. That means a little extra coding, but the result is worth it. First, you’ll filter by color. Then, you’ll apply conditional formatting based on that filter. Not looking to code? Another workaround exists for formatting based on cell color. Just highlight a cell if a Google Sheets checkbox is ticked or not.
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
I’ve broken this down into three sections. I’ll cover “is equal to” first.
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 simultaneously, 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 the rule of B2>C3. That statement 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 Use Conditional Formatting 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 and use the custom formula that applies to your needs. If you found my article useful, you can also check out our guide on how to search in Google Sheets.
Do you have any other questions about conditional formatting? What about other things you’d like to see included in my guide? Please let me know in the comments!
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).
- Phone Number Formatting in Google Sheets
- 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)
- How To Lock Cells in Google Sheets: Step-by-Step Guide
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.