Join Dennis Taylor for an in-depth discussion in this video Creating value-based formatting using data bars and color scales , part of Excel 2007: Advanced Formatting Techniques.
As we look at this worksheet, columns G and H contains salaries, perfectly readable numbers and understandable, and yet at the same time, you might want to make them appear different by accompanying those values with either cell color backgrounds or possibly even icons, such as arrows or circles. 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.
- Saving time with keyboard shortcuts
- Adjusting fonts, cell borders, and fills
- Setting themes
- Formatting numeric data with cell formats
- Using the Conditional Formatting feature
- Hiding repeating column information
- Printing double-spaced data
- Elbow formatting