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 cell's 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 re-select our data… then go back to the Conditional Formatting
command.
You have three different types of presets to choose from: Data Bars, Colors Scales,
and Icon Sets.
To learn more about each one, mouse over the options in the menu.
Data Bars are kind of like a 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 cell's 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 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 4,000" 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.