(upbeat music)
- Okay.
If you were using Google sheets
and you have a table of data,
like the one that I have right here,
and you want to go through it and highlight certain rows
that fit what you're looking for.
So let's say that we're looking for
some of these apps that were made in India.
So there's a line right here that says India.
What you're looking to do is highlight the entire row
instead of just the cell that says India.
So this type of thing is done
through conditional formatting.
I already have the menu up here on the right
but how you will get that to come up
is to do format and then conditional formatting.
So you will left click on Conditional Formatting
and then it come over here
and it's waiting for your first rule.
So when you left click on, add another rule,
the first thing that you want to do
is just specify the range.
So in this apply to range,
what we want is A2,
do we want to do the entire table
all the way down to H25.
And the easiest way to do that
is to left click on this little grid
and then just do it with your mouse.
I have that selected and click OK.
So it's highlighting everything at first,
It's highlighting everything at first
because the criteria is format
the cells if they're not empty.
So if I were to come in here
and just delete one of these
it wouldn't be formatted
because it's empty now.
So let's undo that.
And what we had talked about before,
we want to highlight each row
that contains the word India in column C.
But if you were to just do it
with some of these prebuilt functions,
it seems like you might wanna do it with text as exactly.
And you could say India,
(keyboard clacking)
And this may be the point that you got to, right?
And so you're halfway there.
It's highlighting India but you want the whole row.
So you can't do that with any of these prebuilt options
because these prebuilt options are meant to look at
just the cell that's being highlighted.
So when you use conditional formatting,
we specified A2 all the way over to H25.
It does at one cell at a time.
And when you write the rule,
you write it as if it's for the cell
in the upper left hand corner.
So that will make more sense
as we do this custom formula.
And we'll step through that.
So let's go to the box that says Format Cells If
we're going to left click in there
and we're going to change it to Custom Formula Is.
So that gives you this open box here.
Let's delete the word India,
we're not going to use that just yet.
And the box turns red, which is saying,
you don't have a valid formula in there right now,
you need to type something that works.
And what we had talked about is this formula
you're writing it as if you were in A2
and then Google Sheets will shift the formula for you.
So let's look in A2.
So in A2, what you want to do
is really look to see if C2 has India.
So if you type C2="India"
that works for column A.
So A2 is being highlighted cause C2 equals India.
However, when it shifts to B2,
it starts looking this formula C2="India"
the column shifts and it starts looking in D2.
So what you need to do is go back into the formula.
I'm just going to capitalize C just so it looks better.
So if you put a dollar sign before the column reference
like this $C2,
you can see it's already working.
Sheets won't change C
So as it works from left to right,
every time it looks at C
and so now you're highlighting in the entire row
If that's all you want to do,
you're done.
But I think it's helpful to just do this
with another example, to see how it works.
And just another a custom formula.
The formulas are actually pretty simple,
but let's say that we wanted to highlight
all of the dates that occurred after June 1st 2018.
So let's say,
so you want to look at column A2,
so let's change C2 to A2.
And then right now you're looking for after.
So let's do a greater than,
and then if you just specify the date in here,
it doesn't quite work right.
So would I say June 30th 2018.
Do that and nothing highlights,
there is a little work around that you can do.
So let's go in cell J2 and just type in 6 /30/2018.
And then we're going to reference that cell.
But when we referenced that cell,
so that's in J2 you need to fix the column
and fix the row that way it doesn't shift down
because it's only in 2
and then that formula works.
So dates are a little bit weird.
If you put the date in quotes,
for some reason, it doesn't work.
But if you put the date in a cell
and reference that cell, then it works.
If you look over here,
everything after June 30th is highlighted.
So April 2018, doesn't get a highlight.
So if you like this video,
I publish a lot of other videos just like this,
where I just walk you through little Google Sheets issues.
And hopefully we learn a little bit along the way.
You can subscribe to my channel
if you wanna see more of them.
Thanks.