You may already have some experience using charts to visualize data in Excel.
Conditional formatting offers another way to visualize your data using options
like color coding based on the cell's value.
In this example, I have the sales figures for a group 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 they were
highlighted somehow and the good news is that we can actually do just that with 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 use Greater Than.
Now the sales goal for each person on this list is $4,000 per month.
If we type that number here, you can see in the background that any cell with a
value 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.
It looks like August wasn't a very good month, for example, but September and
October where 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 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
these options on 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 change, 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 a special easy to read.
These icons help us see which items are the highest and also the lowest when compared to one another.
We can also see which ones are about average.
Now you might have noticed that the first rule we applied is still visible.
That's 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,
go to Conditional Formatting and mouse over the option that says
Clear Rules. You can clear the rules for just the selected cells or for the entire spreadsheet.
In this case, though, I'd like to remove the greater than 4,000 rule while
keeping the rest of the icons in place.
We can do that by going to Manage Rules instead.
Here, you'll find a list of all the rules that are currently applied here 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 your worksheet, so practice using some of these
rules on your own and try combining them to see what they can tell you about your data.