(bright upbeat music)
- [Dennis] On this worksheet,
we've got a hundred names or so.
The list is going to be growing as well.
We want certain numbers to stand out more prominently.
The feature that we're about to use
is called conditional formatting.
You'll find it on the home tab.
It has a variety of uses.
Let's say we're interested in the data in column G.
Now because the list is going to grow,
let's select the entire column.
I think many times when you're using this feature,
you're likely to be selecting a column.
So I'm going to click on column G.
I want those numbers above a certain amount to stand out.
When we click the conditional formatting option,
the first choice, highlight cell rules, leads us
into some choices here, fairly straightforward.
Greater than, Excel automatically picks a number.
Here it's picking 82,000.
I want to pick the number 100,000.
It suggests a formatting style.
If I like that, fine.
If not, I'll click the drop arrow here,
possibly choose one of these.
You don't get any preview
as to how these are going to look.
Possibly you'll choose custom format and go down that path.
The most obvious choice might be to fill
in a background color
but you can use border features, fonts,
different number formats, and so on.
Maybe I'll just use this yellow here and click okay.
Notice it's also doing cell G1,
later we'd remove that.
The feature is dynamic.
This number here has to be corrected.
I'll change this from 98 to 101.
And as I do, it turns yellow.
And of course, it would work on the reverse too.
If we change one of these to be
below a hundred, we'd lose the color.
We can also do this with another column.
In other words, we can do it on multiple columns
and with different reasons.
For performance, perhaps we want to see
all those with a performance level of 4 or 5.
Conditional formatting, highlight cell rules.
We could do it this way, simply greater than.
In this case, greater than 3.
Maybe we'll just go with a suggested format.
At some point, we don't want
conditional formatting to appear on these two cells
so we'll go back to conditional formatting
and simply clear the rules from those two selected cells.
As we add data to the bottom of the list here
or if we insert rows, the feature will automatically
kick in for those examples too.
So we get some good visuals here to highlight the data.
It's ideal for presentation purposes.
And even when you're working with the data by itself.
At different times, you want
the data to stand out differently.
And this feature gives you plenty
of opportunities to do that.
On this worksheet, we've got two different lists,
one in columns A through H and then off
to the right, a narrower list although quite deep,
columns J through L.
We'd like to know if there are any duplicates,
any unintended duplicates for example,
with the social security numbers in column B.
So selecting column B, we can use conditional formatting.
Highlight cell rules, duplicate values
and immediately, we see something's going on
and something that needs to be corrected.
Two different people have the same social security number.
Once again, we can go with
the automatic formatting that's here.
Notice how it says duplicate, click okay
and we'll probably either sort the data
or scroll up and down.
Find if there are any more.
Keep in mind, there might be more
than just two of them alike.
There could be three or four, hopefully not
but this brings it out beautifully.
We've got to make the corrections ourself though.
There's also an option called unique
but sometimes I want to almost challenge you
to find out where it is.
Here's another list off to the right.
This is quite a bit bigger in terms of its depth.
It's over a thousand rows.
We're tracking a bunch of transaction numbers.
We're simply interested in which state they're coming from.
But here the issue is, are we getting more
than one entry from each state?
Probably are, 'cause that's a lot of entries there,
but just to be sure, let's check and see
if there are any unique entries here.
So selecting column L, we go
to conditional formatting, highlight cell rules.
Now there's duplicate.
And of course, most of them are duplicates
but buried over here, it's easily overlooked
as a drop arrow next to duplicate, unique.
Immediately we see something off to the right there.
It looks like Wyoming is going to be there once
and perhaps another one down there that we can't quite see.
So I'll click okay here.
And there's Alaska and perhaps some others, it's a big list.
We'll find them eventually.
By the way, you can sort the list on the basis of color.
So if we were to select this data here
and go to the data tab and choose sort,
we can sort based on the data in column L.
That's the state.
And we could sort this on the basis of, in this case,
either cell color or font color
and the cell color right here, this pink background.
Click okay.
And there we see them all clustered together.
Keep in mind, the way this works
is if it's unique, it's highlighted.
Maybe this entry here that says Maine
should have been Vermont, I'll type in Vermont.
And of course, Vermont will no longer be unique
because it's there more than once.
So I think you can say how both features have their uses.
It doesn't correct data, but it certainly brings
out the fact that we have either duplicates
or unique entries within a list.
In addition to providing some obvious visual effects,
conditional formatting also can give us a bit of analysis.
Looking at the sales here, I'm selecting
column E, going to conditional formatting.
And the second choice we have is called top bottom rules.
You'll see the choices off to the right, top 10 items.
Let's click that.
Immediately, the color schemes kicking in
for the top 10 here
but the 10 can be any number we want it to be.
Maybe we only want to highlight the top five
or the top 15 of the top 20.
We pick the number that we want here.
And always, we can pick a different color scheme
if we're interested.
So if I choose five here,
only five of the entries will be highlighted.
And that certainly has its merits.
When you come back to this feature,
sometimes you forget that there's an existing rule in place.
If I go back to highlight cell rules
and say I also want to highlight,
and maybe I'm not thinking also
but I'm thinking of something different.
How about those that are less than
and this time I'll use a different color scheme.
How about green fill with dark green text?
If I click okay,
I've got two rules in place at the same time.
That could be acceptable in some situations,
perhaps not here so good
but we've got two...
If I click okay here, and I am.
We've got two different rules in effect in column E.
You do have the ability to come back
to conditional formatting and manage the rules.
And you'll see here that there are two rules in place.
If the cell value is below a certain amount, it's green.
If it's in the top five, it's red.
So think that out at different times.
What we really want to do here,
conditional formatting to proceed with some
of the other choices, is clear the rules.
If there are other rules in the worksheet
and we want to keep them, we simply want to clear the rules
from the selected cells.
At different times, you'll use either one of these
as appropriate but let's go back to column E again,
conditional formatting and in those top bottom rules,
how about top 10%?
And here too, the 10% could be any number we want it to be.
Maybe we want it to be the top 20%.
And similarly, I'll just cancel this,
we could be looking in the other direction
on the top bottom rules
for the bottom 10 items or the bottom 10%.
And as always, we have the other two choices as well.
So I think once you try these out on a few examples here,
it's fairly obvious as to how these work.
It's a great feature for bringing out the data
and as always, the numbers are dynamic and if they change,
the coloring scheme will react appropriately.
Although there are quite a few ways
of applying conditional formatting,
one approach to conditional formatting
that's not so obvious from the various choices that we see
under highlight cell rules, top bottom rules.
You'll notice in both cases
that at the bottom, you'll see more rules.
And one of the options could be used a formula
to determine which cells to format.
Now, by way of an example,
we've got some quotas in column D
and then the resulting sales figures in column E.
What we'd like to bring out
is that for those persons whose sales numbers
exceeded the quota.
In other words, we want to be comparing these two entries,
one by one in each of these, we'd like to do this
by way of conditional formatting, but no obvious way
but the way we do it is after selecting column E,
go to conditional formatting.
And we can either go to the bottom here
and choose more rules or the same thing here
or possibly just jump to this choice right here
called new rule.
And it takes us into this dialog box.
And after reviewing some of the choices here,
I think you'd come to the conclusion
use a formula to determine which cells to format.
Now what we're about to show you here
is not really difficult but it certainly isn't obvious.
I've selected column E and in so doing,
that means that cell E1 is currently the active cell
and that's the cell we'll be using in the formula.
It might sound a little odd
if you haven't worked a lot with Excel formulas
that as we type this = E1, we don't just mean E1,
we mean every cell in the selection
and we want to be comparing that entry in column E,
this is what it really means,
to see if it's greater than, that's the greater than arrow,
the entry in column D.
So we use D1.
And literally, E1 and D1 contained text
so it seems like a strange combination here of indicators
but what it really means is for every cell in column E,
we're going to be comparing it with column D
and when it's greater, we want to apply a format.
We click the format button to the right.
The most obvious choice again,
would be filling in with a color
but again, we could be using any combination
of number, font, and border features as well.
I'll simply use yellow here and click okay.
And okay.
And every time the entry in column E
is greater than the entry in column D,
we see the yellow background.
We don't want this feature in column E
but it was easy to set it up by clicking column E.
We'll just come back later
and go to conditional formatting
and clear the rules from that cell alone.
But if we want to change this,
we will be selecting column E again.
Now let's make this a bit more involved,
maybe a bit more sophisticated.
Maybe what we'd like to see
is all those cases where the sales figure
is greater than 95% of the quota.
In other words, we're looking to highlight cells
not only in those cases where the sales exceeded the quota
but also where the sales exceeded
more than 95% of the quota.
So as we select column E again,
go back to conditional formatting.
We don't need to apply a new rule, although we could.
What we want to do in this case is manage the existing rule.
And in this dialog box, there is the possibility
of seeing more than one rule, but we simply
want to edit this rule right here, edit the rule.
And here it is.
So we want to change this
and we want our indicator to now say equal E1
greater than 95% or 0.95, we can type it either way,
times, that's the asterisk, D1.
So anytime the entry in column E is greater than 95%
of the quota, we want to apply a color here, click okay.
And we click okay again
and we will be seeing more yellow cells
in column E because some of these
have not exceeded the quota,
for example this one right here,
but that's more than 95% of the quota.
Now, someone else might've created this.
When you encounter this, and you say,
I wonder what the rule is...
As you click on a cell, you can go to conditional format,
manage the rule, and we're not trying to change it here,
we're simply trying to see what it is.
Possibly, you'll see the entire formula here.
If not, go to edit rule
and you'll see it right there.
And you can imagine variations on this.
We could have done something similar
in column D, comparing it with column E
and we can certainly explore this option
with other kinds of data as well, too.
So there will be times when you want to
use a formula to determine which cells to format.
(bright upbeat music)