Conditional Formatting Pt 2 - Data Bars, Color Scales & Icon Sets - Excel 2013

In this video I will illustrate how to apply conditional formatting rules to a specified range of cells. This video focuses on Data Bars, Color Scales and Icon Sets. Part 2 of 2.

Subscribe! Check out my playlists for other great Microsoft Office & Excel video tutorials.

---

Closed Caption:

>> Hello. Welcome back.
So today we're going to talk
about conditional
formatting again.
As you can see a little bit here
I've got some highlighted cells.
So in my first video I
talked about these highlight
and top bottom rules and that
is currently what is applied.
But in this video it's
not going to take as long;
we'll talk about
these right here.
I think of these as kind
of like an in line graph.
Now they're best applied to
numeric data, and so I've kind
of already got this
formatting going
on so let me clear this
conditional formatting,
there's a couple
of ways to do that;
I'm going to show you one way
and then I'll show you another.
I'm going to select column
E and so one way to get rid
of a conditional formatting
rule is to head over here
to the editing group under clear
and just clear formats, right,
and that's going to get rid
of your conditional
formatting rules
and the reason that's good is my
data's still there I just don't
have conditional formatting.
The second way to get rid
of conditional formatting is
similarly to select the range.
And now this is kind of
the cool thing to show you,
it's definitely the harder
way but it's kind of cool.
Under conditional
formatting you can always go
to the rule manager, right.
And so you head over here
and you can see oh there's
that rule I created, and
so I can select that rule,
delete that rule,
apply it, okay,
and that was just more steps
but now you've seen
the rule manager.
You can create rules from
there, modify rules from there;
that's as far as
I'm going to go.
Alright so let's look
at the various graphical
conditional formatting.
Let's start with data bars.
So I'm going to select the
height column and I want
to apply data bars, right,
it's called data bars.
They don't'-- I mean
they have names, right,
like this is blue data bar,
this is right blue data bar;
the names are kind of
meaningless so I'm just going
to pick one and we'll
see what it is.
That was a horrible choice.
I immediately regret
that decision.
I am going to select the
weight column and I'm going
to do the data bars; there's
a good reason for that.
So data bars, I'll show
you what's happening.
So what just happened is
basically that is kind
of a bar graph and you can
see it's not great data
but the shorter or the smaller
players have a lower bar
and the taller players,
or the bigger heavier
players, have larger bars.
It's just like a bar graph
but it's kind of in line.
I'm not a huge fan of these
because it does make
the data hard to read,
but if there's a pretty
wide range it's a good way
to really make the
big values stand out
and small ones stand
out as well.
The reason this was a
poor choice for height is
that they're all within about 10
of each other and they were all
about the same height.
Those are data bars.
Let's now look at
the height column.
So that was data bars,
let's talk about--
we might as well
look at color scales.
Color scales are pretty
confusing the first time you see
them; these do not
have meaningful names,
I always just apply the first
one because I don't want
to call it green yellow
red or whatever it is.
I click that and what
the heck just happened?
I scroll up and what the heck?
So I will show you what happened
and to illustrate what did
happen the best way to do
that is to sort the data so
I'm going to apply filters;
I'm going to sort it
and then we're going
to see some magic happen.
Oh now you can see
what happened, right.
So the lowest values are red and
the highest values are green,
alright, and everything
in between is yellow.
A lot of data to look at but
that's what a color scale is.
Kind of cool thing to do.
There's a time for it.
So let's look at the last of
the graphical ones and notice
that there's a rule here which
I need to get rid of first
so I'm going to select the range
and let's do this the easy way,
let's just clear the formatting.
Now let's apply the third kind
of graphical conditional
formatting, so not data bars,
not color scales, but icon sets.
Icon sets are relatively
arbitrary.
Just the idea-- it's kind of
a combination of both of them.
Let's click on this one, it's
called five arrows colored;
here's just plain
old five arrows.
They all have their own
unique sets of names.
Here's five arrows colored.
Notice that made my data really
unreadable so I'm going to have
to widen that column
out a little bit.
And so what it did is it
divided it into percentiles;
so the bottom fifth has
down arrows, the two--
the second bottom
fifth has that arrow.
It's shapes, I don't
know how to explain that
but you can kind of
see what happened.
The top values have an up arrow.
Alright so those are icon sets.
So you can see those are
all conditional formatting.
These in spirit are quite
a bit different than these.
These are going to be used
to target select cells.
This is going to be used
to take the whole range
and just give it some
graphical meaning,
and maybe you can
imagine some places
where this might
be a better choice,
this might be a better choice.
It's kind of a nice way to get,
kind of, the effect of a graph
without a full blow graph.
So that was my second video
on conditional formatting.
Of course, as I mentioned
in the last video there's
some pretty elaborate rules,
which you can create down
here in the rule manager
and through new rules.
Some of the more
difficult problems
that I've ever seen
have been rule oriented.
But most times what you're
looking for is just as simple
as just kind of clicking
the correct menu option.
Of course, let me just
mention this as well,
if you ever do need to modify
let's say an icon set I select
that range and go to manage
rules; you can edit the rules
and you can imagine we could
spend 20 minutes poking
around this dialogue box and
so you can change how you want
to break them, where you want
to break them, what the rule is,
what image you want displayed.
You can make an absolute mess
if you wanted to, and I will.
Is this a good idea?
I don't think so but you
can do whatever you want.
And now you are familiar
with conditional formatting.
Thanks for watching.