We've already looked at ways that you can visualize your data using charts and sparklines.
Conditional formatting offers a different way of
visualizing data because it allows you to apply
rules to the cells that will add formatting, such
as color coding, based on the cell's value.
In this example, we have sales statistics for a
number of different salespeople,
and let's say that the sales goal
for each person is $5,000 per month.
We can use conditional formatting to see who met
or exceeded this goal.
First, select the data, and then go to the Conditional Formatting command on the Home
tab.
And then click Highlight Cells Rules, and select
Greater Than.
And we want to format cells that are greater than
5000, so we'll type that here.
And then just choose a formatting option.
I'll choose Green Fill with Dark Green Text.
So it really makes those cells stand out.
You can see that not many salespeople met the goal
in June or August,
but September and October were much better.
Now, let's look at some of the conditional formatting presets.
I'm going to re-select these cells, and then go
back to Conditional Formatting.
There are three different types of presets: Data
Bars, which are kind of like a bar graph;
Color Scales, which change the background color
based on the value;
and Icon Sets, which put different icons in the
cells depending on their value.
I think this one's especially easy to read.
The icons help us to see which items are the highest and the lowest,
and which ones are about average.
You might also notice that the first rule we
created is still visible.
And that's because Excel lets you have multiple conditional formatting rules at the same time.
To clear all of the rules, just select the cells,
and then go to Conditional Formatting, Clear Rules.
And you'll have a few different options.
But I'd like to just remove the greater than 5000
rule, while keeping the icons.
To do this, go to Manage Rules.
You'll see a list of all of the rules that you've
applied,
and you can just select one of them, and click Delete Rule.
So practice using the different types of conditional formatting,
and try combining different ones to see what they tell you about your data.