Using Google Sheets for conditional formatting based on a range’s own values is simple. Formatting based on another range of cells’ values is a bit more involved. Following are step-by-step instructions to format a range of cells using values in another range of cells.
What is conditional formatting
Conditional formatting is used to highlight to the reader of the spreadsheet certain values that meet certain criteria. It can be used to show items over/under a certain amount, later/earlier than a certain date, or only the widgets sold by Joan.
Video explanation
Conditional formatting based on another cell
Our objective in this example is to highlight all of the dates on which Joan had a sale.
Select the range that you want to have highlighted. Even though you are going to be referencing another range of cells, you still only need to select the range that you are going to be formatting. In this example, we’ll be highlighting the date column.
Click Format in the top menu then Conditional formatting…
The Conditional Formatting menu option will pop up a Conditional format rules menu on the right side of the screen (on the desktop version of Sheets).
Click the plus sign to begin adding the rule. In the drop-down menu for Format cells if choose the last option which is Custom formula is.
In the box that appears below Custom formula is enter =B1="Joan". Note that you have to start the formula with a = sign just like any other spreadsheet formula. Also, note that you need to surround the word Joan with “s since it is a word instead of a number.
Now you have the sales data with dates highlighted on which Joan made sales.
Extending to the entire row
If you want to highlight the entire row based on the value of one cell, continue reading here.
Nadena Gibson says:
Is there a way to do this if a cell contains one of two things. For instance, in your example data is there a formula that says highlight the cell in column A if column B says “Joan” or “Gill”?
Prolific Oaktree says:
Yeah, sure. Just start it with the OR function.
Scott Azevedo says:
Rather than format a color in, can I format a 1 instead. =B2″furlough”. If B2=furlough, then I want a 1 in B1. Is this doable? Thanks
Prolific Oaktree says:
That wouldn’t require conditional formatting. Just use an IF statement.
n says:
we cannot study your sheet because “format” is grayed out
Prolific Oaktree says:
Go to File, then choose Make a copy. That should give you your own file with full edit rights.
Esme says:
Thank you for your guide, it was very helpful, but I have a question!
Backstory – I record all my Apple Activity stats on a Google Sheet per month. I have columns for everything I like to keep an eye on (move ring, exercise ring, water, steps etc). Most of them I can have finite numbers for, e.g. the Stand ring is always complete at 12 hours, so I can have conditional highlighting on ‘greater than or equal to 12’ which works for all conditional highlighting except 1 column.
I would like to conditionally format column D (the number that Apple Health gives me for my Move stat) based on whether it’s equal to or greater than column c (which is the Move goal I set myself and this changes every week).
Because this number changes, I can’t put ‘is greater than or equal to 600’ for example, because this might not always be the number in column C. I can’t seem to find a way to be able to have column D dependent on column C for each cell.
I don’t know if I’m explaining this right but hopefully you can understand that I mean! I know it must be simple, but I’ve read so many guides and I just can’t work it out!
Thanks 🙂
Prolific Oaktree says:
You have to use a custom formula which is the last option in the conditional formatting drop down. Then do something like IF(C2
Iver says:
Is there a way to use the empty/not empty formula for another cell? I would like to color one cell if another cell is empty. Is that possible?
Prolific Oaktree says:
Sure. You can use the ISBLANK function and just reference that other cell. Enter the formula using the Custom formula input box.
Anthony Tiseo says:
What if you want to format it if multiple cells fit a condition? What about a percentage of cells in a range?
Prolific Oaktree says:
You can do all of that. Start by looking into the AND function. You’ll have to wrap a few functions together inside of it. Practice in a spreadsheet cell first then copy and paste into conditional formatting.
Rolanda says:
I have used conditional formatting to highlight cells based on the due date of the project. I have RED for past due, ORANGE for tomorrow’s date and LIGHT RED for today’s date. What I want to do is have the cell colour change again when a second cell has been initialled as completed. I have found that the column doesn’t respond after 3 conditional formats have been applied. Would I need to create a function code to do that or is it possible in conditional formatting?
Gary Rossen says:
Hello have a question. Can you conditional format cells that are based on a formula? For example – Joe has been absent for 3 days from school…in column A6 there is a formula
=countif (a2:a5, “Absent”) That returns a value of 3 based on his weekly attendance. Then I want that value to be Red if over 2 absences, but doesn’t seem to work because the value of the cell is determined by a formula and is not straight text?
Thanks, Gary
Prolific Oaktree says:
Thanks for the question, Gary. Yeah, you should be able to and this page is probably the best guide for it. It would be a ‘Custom formula’. Something like =COUNTIF(A2:A5,”Absent”)>2