Let's click on column H here. And the Conditional Formatting Styles choice
here, on the Home tab, offers us a choice called Data Bars.
Now, as I slide into the blue bars here, look at column H. You will recognize
how the blue extends farther across the cell, when the number is larger.
The first entry there is 40,000. The next one is 85,000.
You see how the blue extends. We might want to consider making that column
wider, although I think this tells the story in a different kind of way.
And of course, if that's not your choice, there is green and red and we see
some other choices here, working the same way, just depending upon whichever
color works best for you. So, I can see how - the effect of that.
Now, we probably wouldn't have the similar effect in column G, although we
certainly can. Let's use a different effect there in column G. In addition to
Data Bars, we also have Color Scales. Maybe this one.
Keep your eye on column G as I slide over some of these choices.
You'll recognize that even though, in some cases, the description makes it sound
as if there are only three colors - this, for example, says Red, Yellow, Green
- if you look closely you'll see that we have
got two kinds of green there. It's kind of vague.
Maybe it's not as good as some of these. Here we see greens and that's a red and an
orange, possibly, a yellow. We see different choices in here.
So, you want to explore that on your own. Again, a lot more variations than most of
us ever would have hoped for. And what Excel is doing here, you might say behind
the scenes, is that it's apportioning the data based on percentages. In effect,
it's analyzing all the data in column G and dividing it into fifths and applying different
colors based on that. You ultimately have the ability to change
that. Let's say for the moment, we will just pick
one of these and leave it that way, but you get the general idea here.
In looking at the salaries here, in this example, the higher salaries are reddish.
Those that are not quite so high are - well those are pretty high there.
But you see what's happened. Here is one of those not quite as high.
That's sort of an orange, and you see the other color variations taking place there.
Now working on Column G again, let's go back to Conditional Formatting and
also recognize that in addition to Data Bars and Color Scales, we also have Icon Sets.
And I didn't remove the color, so possibly, you might want to include a
combination of these. I think that's too overkill myself, but we
could do this. And looking at this effect that we are about
to see, you decide well, that's too much going on there. What might we do here?
Click back on Conditional Formatting and choose Clear Rules from the Selected Cells.
Now, let's try Conditional Formatting > Icon Sets and we see some of these choices.
I think you can see here it's going to take you a while to decide which one you'd
like, although here and there, one is going to jump right out at you.
If, for example, I were to choose this one here with the different gray-colored
arrows, you'll recognize that the arrows that point upward are the higher
salaries. Those that point downward are the lowers, and other arrows point
rightward, or you might say northeast or upper-right, southeast, lower-right and so on.
You see what's happening. Here too, the data is divided into, in this
case, fifths, or percentiles, 20 percentiles. So, you have a favorite here or there.
Maybe we will choose this one. This is dividing the data into quarters, and
maybe that's just what you want. But if you want fine-tune this, and you can,
you can go back to Conditional Formatting, and perhaps you want to Manage
the Rules that say, in effect, "The rule in this case is what?"
The arrows are being apportioned on a quarterly basis, and if somehow you want to
change that and say I want only the top 10% to have green arrows, then you would
change this value of 75 to be 90 and change this 25 value here to be 10, if you
wanted to do 10 on the bottom. And again, imagine the variations you could
go through here. So, as we click this, this OK button and this
OK, we will see some changes. Now as I do an undo, keep an eye on the yellow
arrows, particularly in the section right here, and you'll see how we
have redefined the meaning of that. That wasn't necessarily the choice I would
have made, but I think you get the idea here that you do have control over these.
And if you are making a presentation, this certainly causes the data to jump out at you.
Sure, we can read the numbers, but the green arrows give us that good, visual
support that says, "These are the higher values." A great feature, under Conditional Formatting,
this ability to apply either Data Bars, Color Scales or Icon Sets and then,
if needed, adjust the formulas behind them.