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!