Goodwill Community Foundation creating
opportunities for a better life
you may already have some experience
using charts and sparklines to visualize
data in Excel conditional formatting
offers yet another way to visualize your
data using things like color coding
based on the cells value in this example
I have the sales figures for a number of
different salespeople and I'd like to
find out who's meeting or exceeding
their monthly sales goal of course we
could just look at the numbers but it
might be easier if they were highlighted
somehow which we can do using a
conditional formatting rule first select
your data then go to conditional
formatting on the Home tab next mouse
over highlight cells rules and in this
case we're going to choose greater than
now the sales goal for each person on
this list is $4,000 per month if we
enter that number here you can see in
the background that any cell greater
than 4,000 is being formatted
automatically if you want you can even
choose a different formatting option
just click OK when you're done as you
can see the formatted cells really stand
out now so we can easily tell who's met
or exceeded their goal
looks like August wasn't a very good
month for example but September and
October were much much better next let's
take a look at some of the conditional
formatting presets we're just going to
reselect our data and then go back to
the conditional formatting command you
have three different types of presets to
choose from data bars color scales and
icon sets to learn more about each one
mouse over the options in the menu
data bars are kind of like bar graphs
the higher the value the longer the bar
in the cell color scales on the other
hand will change the background color
based on the cells value icon sets can
be used to add different symbols ratings
and indicators and you have quite a few
styles to choose from depending on what
you need I think this one's especially
easy to read the icons will help us see
which items are the highest and also the
lowest when compared to each other we
can also see which ones are about
average you might have noticed that the
first rule we applied is still visible
the one that's changing the fill color
for cells above 4,000 that's because
Excel lets you have multiple conditional
formatting rules at the same time so you
can highlight different things to clear
the current formatting rules just select
your cells then go to conditional
formatting and mouse over the option
that says clear rules you can clear the
selected cells or the entire sheet in
this case though I'd just like to remove
the greater than 4000 rule while keeping
the rest of the icons we can do that by
going to manage rules instead
here you'll find a list of all the rules
that are currently applied to your cells
to remove one just select the rule you
want then click delete rule and click OK
conditional formatting is a great tool
for visualizing parts of your worksheet
so practice using some of the rules on
your own and try combining them to see
what they can tell you about your data