Articles Blog

Google Sheets – Conditional Formatting Based on Another Cell

Google Sheets – Conditional Formatting Based on Another Cell

Alright, I’m guessing you made it to this
video because you may already know how to do conditional formatting in Google Sheets,
but you’re trying to format based on another cell’s values. I am going to take this data that I have and
I’m going to try to keep it simple at first and just conditionally format one column based
on the values in the column next to it. We’ll walk through this set of steps here. You go to format and you go to conditional
formatting. I’ve already done that, that’s why this box
is over here waiting for me. I’m going to press “+” to get a new rule. Our range is just going to be column A because
that’s the range we are going to have formatted. I am going to specify column A by saying just
column A, start at A2 and don’t stop, just go all the way down. That’s how you specify that, with that syntax. And then, to find the custom formula, you
drop this box down and then go down to “custom formula is”. Here it is expecting you to type in a formula
just like you would in a spreadsheet. You start it with an “=” sign and envision
us starting in A2. So, when you are in A2, you’re going to be
looking at what’s the value in B2. I’ve chosen for this example to highlight
every date on which Joan makes a sale. We are going to say, look, if B2 is equal
to Joan – I’m going to stop right here to show you a few things. You have to surround the word Joan in quotes
because that’s what you have to do when you are specifying a string in a spreadsheet as
opposed to a number. The second thing is, see the rectangle around
the formula right now is red so it is telling me it’s not ready yet, it is trying to help
me out. It says, “It�s not a valid formula”. If I close the quote, it tells me it’s ready
to go. It doesn’t say it’s right, it just says it’s
a valid formula. Let’s just change the formatting a little
bit to yellow so we can see it better – eh, we actually can’t see that any better, let’s
go back to green. That’s giving you a preview of what this is
going to do. Joan made three sales. You say done, look at the data. There we go. This is formatting based on the data over
here. Now, after looking at the results, it may
not be exactly what you want. You may need to highlight the entire row so
it’s easier to see so we’ll walk through that as well. You’re formula is over here. It is waiting for you to see if you want to
change anything. It messed with my range, I wanted it to be
infinite. I’m not sure why it changed that, but we’ll
change it back. Having said that, we’re actually going to
do a few things. We’re going to do that all the way over to
G. Then, we’re going to change…if you hit done
now, it doesn’t change anything. You would think it would do the entire row,
but it doesn’t. What you need to do is you go back in here
and you have to change the formula. What’s happening is this formula increments
if you will just like any other spreadsheet formula. This conditional formatting works from the
left to the right. At first, it’s going to look at January 6
and see if this is Joan. It’s not going to highlight it. Since you expanded the range, it’s going to
look at B2 and see if this is Joan. It is going through the entire spreadsheet,
but the formula isn’t fixed so it is moving where it looks. When it gets to A4, it says this is Joan,
so I’m going to highlight. But then when it gets to B4, it says this
isn’t Joan. Come over to your custom formula and say – continue
looking at the same column, don’t move. I want the 2 to move because I want it to
go down a row every time but we want to fix the column. Just like you would if you were inside a spreadsheet,
use a dollar sign to indicate that value shouldn’t move when the formula moves. So you see, it’s magically working over here
now. This is just what we wanted, right? Joan made three sales, you can see right away
from looking. You can even see some others. We’ve covered two things here. I think highlighting columns based on values
that are outside the column and then making it look a little better by highlighting the
entire row. The values in there are just one of the cells. This gets pretty easy when you just use custom
formulas. So, I hope that was helpful. You can take this into your next presentation
and wow everyone. You’ll just be amazing;) Have fun with it. Thanks!

100 thoughts on “Google Sheets – Conditional Formatting Based on Another Cell”

  1. Hi there, Can I also add a formula with the same principles but based on another tab on the same sheet? example: I would like to format column B in tab1/sheet1 and a rule that if a dropdown certain drop-down choice is made in tab2/sheet2 column it automatically fills in the answer in column B from tab1/sheet1(example: column B in sheet1 = to chest training I would like that it automatically fills in hypertrophy in column C in sheet one next to it). Does my question make sense to you? Thank you for your reply. Kind regards, Christopher

  2. Came here looking for a refresher on what formula syntax to use to get conditional formatting based on cells above or below the current row. I know it's possible, because I did it before, but understandably not a common application. Thanks!

  3. Based on your sheet: I'm looking for a way to add together the total amount of units sold by Joan and displayed in a separate cell or graph. How would this be possible?

  4. Thanks for this video – I was struggling with the syntax to get the custom formula to run across the whole row. Nice video!

  5. Very helpful. I do have one problem though:
    I am trying to change the fill color on a weekly M-F schedule as the date changes ( day and date at top of a 6 cell column ) Seems I have to rewrite the formatting for each week though, because the original referenced cell stays the same, and it is a year long schedule.

  6. Great video. I'm using a drop down in my Google Sheets and want to highlight the whole row if it's says "In progress". With conditional formatting it's only highlighting the cell, any ideas?

  7. How can I make this work with a range of columns? I keep trying and it will work with the column next to the one with the conditional formatting, but it will not work with the other columns even if it is indicated in the formula.

  8. Is it possible to conditionally format 12 rows of data based on 1 row? I'm trying to do this for a 12 month budget vs actual sheet, where d2:q2 is budget category and d4:q15 is 12 months of actual spending. I want to highlight cells red only when they exceed the category budget. Using conditional formatting, I made a rule to format cell when greater than d2:q2, but it's not working.

  9. does anyone know if it is possible to do conditional formatting while having the formula pull data also? by this I mean I am programing a sheet that can be used for different groups of people so I have it programed so that if the original sheet is being used by 2 people we can call them Nate and Kevin they change a cell called person 1 to say Nate and another cell listed as person 2 to Kevin and the entire sheet changes any reference of person 1 and 2 to Nate and Kevin respectively. This is where my question comes in I want the conditional formatting to change based on the first letter of whatname is entered. so if each person is assigned a color Nate = purple and Kevin = blue I want a column to have conditional formatting to those colors based on if an N or K is entered. I am trying to us the formula '=Left($A$2,1)' to make the cell purple if an 'N' is entered and Nate is the value of A2. Any thoughts?

  10. How would I set the formula to check the string against an entire column for a specific cell?
    In other words, and using Joan as the example, if I wanted D5 to highlight in blue if "Joan" appears anywhere in column B.

  11. I am trying to rank sports teams in my youth tournament. The problem is when I get multiple teams with same point totals. I want to add conditional formatting to rank based on other criteria. Is this possible?

  12. What is I wanted it to return the value if it contains a word, rather than matches a word exactly from cell B#?

  13. What's missing here is how to define the actual format of the cells you have chosen using the conditional statement. My sheets only has a couple of formats and a line that says "Custom Format" but clicking on that has no effect. I can only choose from the predefined six options. I've Googled and can't find any way to create a new "custom format" (in my case I want to put a line between the rows when the value changes).

  14. If I want to do conditional formatting based on two cells – one cell has data and one cell has no data (which means blank cell), what formula should I put in the Conditional format rules?

  15. Hey Prolific Oaktree! Thank you for a great channel.. Hope you can help me out. I have a cell which is called "score". It looks at another cell for some criterias and then sets a score based on some numbers. (=IF(AND(B6 >= 0,B6 <= 1000000),"10",IF(AND(B6 > 1000001,B6 <= 5000000),"8",""))). Lets say the actual cell score is now "10". I have a lot of rows of these scores, and I want to sum them up lower in the spreadsheet, to get a combined total. However, When I use this formula : =SUM(J2:J72) The total value is just "0". I believe it has something to do with the text value or something. Would appreciate if you could help me out. All the best!

  16. Hi! I wonder if you could help me, please.
    I have a sheet where in column A there are names, in column C-D-E there are checkboxes (which were recently implemented in Google Sheets). Basically I'm collecting money for a gift for a friend, and when people give their money through a bank transfer (column C), PayPal (D) or cash (E) I will check the right box.
    What I want to do is, using the first row as an example:
    "When C1, D1 and E1 are all FALSE (unchecked), change A1 color to red"
    So basically the person's name turns red if they didn't pay yet.
    Do you have an idea on how to do this? Thank you very much in advance

  17. I'm trying to format so a cell is highlighted if it still needs to be completed. So in this scenario, I have Column B with the task to be done, and Column C with the status. But I also have a lot of empty rows at the bottom for future tasks. I'm trying to figure out the formula to say If B contains text and C does not, then highlight it. If B does not contain text and C does not contain text, then don't highlight it. I've tried a bunch of things and nothing seems to work — help! 🙂

  18. Can you help ? I have created an expense income sheet. When an expense is made by credit care, I would like to the sum to subtract from the row which contains the running credit card balance as well as subtract from the row which contains the overall balance.

  19. Great tutorial, especially the use of "$". My problem for you is that there is a new checkbox that can be inserted in a cell with the values of TRUE or FALSE depending on the box being checked. I am trying to create a conditional format with the strikethrough font type when the box is checked. For some reason it just doesn't work. I have created my range E1: E and the custom formula of =A1="TRUE" (for example when the checkbox is in column A and the area I want the font change is in column E) but it doesn't format. Do you have any idea how to accomplish this?

  20. EXACTLY what I needed. Thanks dude. And THANK YOU for just diving right in and solving the problem pronto. You had a clear video name and did that exact thing within the first few minutes. THANK YOU! I hate it when I know the answer I need will take a few seconds and all the videos I find are 15 minutes long. So thanks again.

  21. Is there a way to insert text into one column based on the value in another column. For example, if the value of a cell in column C is <1 then the text in column G is "Overdue"

  22. Hi, nice tutorial, but not working for me. I don't wanna tell the value in custom formula. I mean i need to do something like this:
    "change color to red in cells in B column if the value of B cell is lower than C cell in C column in the same row"

  23. How would you colour format column e and column f (in your example spreadsheet) so that each row is independent of each other. I want to make green the higher number and red the lower number between e and f on each row. I can only do this by formatting each row seperately which would take be a long time. If I copy a row and "paste conditional formatting only" to another row it puts all the rows together and doesn't deal with each row independantly. Is there a quick way I can do this? Does it have a name?

  24. Very instructive. Do you have a vid with a similar example but for when the cells in one sheet are being compared to cells on a different sheet? Perhaps using the Indirect function? I've been trying to use Indirect since I've noticed ppl around the web mentioning that it can be done using that one function, but I have yet to crack it. My equation in conditional formatting doesn't give me an error message but it just won't do what it is supposed to do in terms of the formatting. Any help much appreciated. Thanks.

    Great video btw.

  25. Nice Video, Can you Help me? with your sheet how to formatting color 7 days before date in column Order Date. so the date will automatic Red Color before 7 days, from date is 31/12/2018

    *Colom date is 31/12/2018 colum date will red in date 25/12/2018

  26. Can a specific cell reference from another google sheet file be used in conditional formatting. Eg Google file 1 using the conditional formatting rules; Google file 2 sheet control has the specific criteria to be used in Google file 1? thanks, Alex Esparza

  27. I was looking for a training video for a colleague and I found this one. I've been using spreadsheets for more than 20 years. This is the most comprehensive and enjoyable video I've ever seen on the topic and I learned a few things!

  28. Would you know how to… see if the contents in one column's rows has any matches to any of the other pieces of data in another column? So if column D row 2 matched anything in column F from row 2 to row 1,000?

  29. I am learning a lot with your videos. Thanks a lot sir…. great job. You are the best teacher!!! I want to know if you have a video which explain if the hour that you have scheduled in a cell has passed using the hour of the computer. An example: you had to call somebody at 9:00 am and wrote it in a cell but now the time is 10:00 am. How to use the format to change the color of the cell if the time has gone by.

  30. Maybe this sounds crazy, but I want for the formatting in a cell containing text to match the formatting in a cell containing numbers, but that cell containing numbers also has conditional formatting based on a color scale with other number cells.
    Any ideas on how to go about doing that?

  31. Hi, when creating charts how do you select data for the horizontal and vertical axis in google sheets for Android?

  32. Could you make it so the cell copies its colour to an adjacent cell?
    I have a document that uses over 200 colours. I don't want to enter each one manually again.

  33. but how to evaluate the cells just inside the given rage to see if them have a certein numbers of characters?

  34. Excellent, within the first 2 min you taught me exactly what I needed. Bonus, I learned A1:A highlights the whole column. Thanks!

  35. for some weir reason this is not working for me. i have however created a custom value of "isna(VLOOKUP($E5,IST!B5:E9,1,FALSE))" and nothing is highlighting at all…

  36. Your film was very helpfull. Thank you!
    I also have a question and was wondering if you could help: I have a doc with a few tabs (one for every month) and I need to make another one that summarises the other tabs (the whole year). By summary I mean to make a list with the whole year. That's not a problem. The kicker is that it also needs to have the same background color as the cell I am importing. It wouldn't be a problem to simply copy the tabs to the last one but other people are working on this document and change the background color in the monthly tabs. So my problem is: how to import the background color even when someone changes it.
    Would really welcome any advice.

  37. Very well explained – great stuff. Not many videos are this easy to follow. And thanks for not blowing your own trumpet with pointless graphics.

  38. is there a way to change the color of the labeled cells? Like the 123 and abc cells? Mine are green and I don't know how to get them back to grey or a different color

  39. Very helpful. So basically:

    As the conditional formatting evaluation is moving through the range, cell by cell, each cell being evaluated is considered the new relative A1 (first column, first cell) to the formula with B2.


    B2 indicates that the second column, second row (B,2) relative to the current cell being examined. A1 through G1, A2 through G2, all the way to A1000 to G1000 (upper max of 1,000 rows examined).

    However, we can break that relative processing.

    $B2 indicates that regardless of the current cell being examined (A1 through G1000), the formula should always only apply to the sheet's B2 cell, and NOT relative to the current cell being iterated.

    OP: Does that sound accurate?

  40. I've watched several of your tutorials and I'm not a teacher or a student. Excellent for any person using the program. Thanks for your time and keep up the good work!

Leave a Reply

Your email address will not be published. Required fields are marked *