Join Dennis Taylor for an in-depth discussion in this video Creating value-based formatting using data bars, color scales, and icons, part of Excel 2013: Advanced Formatting Techniques.
- In addition to using conditional formatting for changing font colors or cell backgrounds, we also have the capability of adding data bars, color scales and icon sets. ,Let's imagine that in column H, we wanna give some visual relevance to these numbers by choosing Conditional Formatting and using Data Bars. As I slide over one of the Gradient Fills, you begin to see what's happening in column H, that bars are wider depending upon the value. As you look at this, you might think, "Well, they overlap the numbers.
"Maybe if we make the column wider, "it's gonna look a little different." Let's back out of here for the moment, make the column wider, and since the menu actually overlaps some of this, let's scroll a little bit to the right so I can see this better. Conditional Formatting, again, for column H, Data Bars. But notice the bars have just gotten wider. They will continue to overlap the data. The Gradient Fill options here, you can see, also fade to the right, so they're somewhat lighter on the right, presumably to give you a better look at the numbers.
If you don't care for that capability, you might wanna choose Solid Fill. Here, the issue might be, the darker colors maybe don't let you read the text as clearly as you might want. So there's the option with red. Let's go back to Conditional Formatting and look at some other Data Bar options here under Solid Fill. I think the lighter colors tend to work a little better. When I use this, I use the option in the lower left corner here, this one here. Here, too, I'm making the columns wider as we saw earlier. Really doesn't impact this. Now, in addition to Data Bars, you might wanna consider Color Scales.
Now, if we start to use Color Scales, but we've left on the Data Bars, both features could be present at the same time. Probably not a great idea. What we should do first is clear the rules from the selected cells, then come back to Conditional Formatting and explore Color Scales. Now, a number of the options both here and under Icon Sets use the green, yellow, red scale, so the traffic light pattern. Now, many of these, you would say, "Well, green is good. "We like green lights." And green are the higher values here.
As we approach the lower values, they go through yellow and then red. Now, in column H, we're not seeing just three colors, but variations on three colors. If we were to sort this data, and by simply clicking on one of the cells and sorting, we will rearrange all the rows across, top to bottom here. We'll simply, on the Data tab, choose a ZA sort. You can see, we've got dark greens, but as we scroll down, the green becomes lighter. Occasionally you might see a more obvious break. You may or may not be able to see that one so clearly.
But we're getting lighter and lighter here as we move into yellow, and then farther down, a little bit of pink and then red and so on. So it's a continuum here of colors, not just three colors. Now, I'll undo the sort with control z. Let's go back to column H again. On the Home tab, Conditional Formatting, Color Scales. Quite a few choices here, 12 choices. Some of these are fairly subtle, and again, you'll just have to make the decision as to which one might work best for you.
Instead of Color Scales, we might wanna choose Icon Sets. Now, I have seen Icon Sets and Color Scales used together. For example, we might use this. What have we done here? We've divided the data into thirds, if you focus just on the icons. The green arrows are in the top third. These are the top third of the salaries. The red arrows are in the bottom third, the yellow arrows the middle third. I think this probably works best if we have only icon sets. So we could go back to Conditional Formatting, clear the rules from the selected cells, and then Conditional Formatting.
Let's use some of the Icon Sets. Recognize that some of our option here divide the data into thirds, for example these two sets of arrows. Farther down here, we've got traffic lights ... By the way, these are actually called a "Rimmed" and "Unrimmed" traffic lights here. We got two different kinds ... And different choices here. Some of these, of course, divide the data into fourths, and some divide the data into fifths. What Excel's doing in the background, and you don't necessarily have to see what's actually happening, it's dividing the data. It's analyzing the data from top to bottom and then dividing it into thirds, fourths or fifth.
Again, quite a few choices here as we apply these. If we were to use, for example, the option here that divides the data into thirds, we can see what's happening there. What if you wanted to not show the salaries? Maybe you're going to make a presentation and the actual names of the people might be visible off to the left. You don't necessarily want to reveal the actual salary, but just indicate that the salary falls into the top third, middle third, or bottom third. Conditional Formatting has what are called Rules.
We've already talked about clearing the rules. But sometimes, we want to manage the rules. This case, here's our rule. Let's edit this rule. Here's the choice, "Show Icon Only." Click that box, click Okay. And Okay. And we see just the icon. We can make the column narrower. We could center these, if we wish, that's not critial. If you happen to click on one of these cells, you will see the salary in the formula bar. Let's say you probably wouldn't be doing that in a presentation.
So, as we look at this, we can say that John Thompson is in the middle third. Now, obviously, we wouldn't show column G here at the same time. It has salaries, of course. But we can see what's happening here, just by looking in column H, we get a rough idea of how these salaries fall into place. Now, something else we might consider doing. Let's bring back the actual values. Let's go back to Conditional Formatting and Manage the Rules. Once again, editing the rule here, let's bring back the actual icon by unchecking the box here.
Notice below this what we see. The number 67, the number 33. When you're data's divided into thirds, Excel uses standard techniques. If this were fourths, we'd see actually three entries here. We'd see 75 and 50 and 25. But if we wanted to divide these into thirds a different way, we might say ... And they wouldn't be equal thirds ... We might say ... Let's say we only want the top 10% to be green. Instead of 67 here, we'd make that be a 90. And for the middle third, instead of starting at 33, we might want to start it at 10.
I'll highlight that and make it be 10. Let's say greater than 10 right here. If it's greater than 10, but it's not 90 or more, it's going to be yellow. And then all the others will be red. So as we look at column H, we're redefining here, we're about to redefine. We'll have our salaries back, too. These are in the top 10%. All those yellows are in that middle 80%, between 10 and 90. Later we'll see those in the bottom 10%. That's a little unusual perhaps, what I just did here. But at least it makes you aware of the possibility that you can redefine how these Icon Sets are being used.
Once again, that's called Manage Rules. We could come back here, edit the rule, and again change this, change it back to what it was or possibly consider, while we're here, a different icon style. Maybe we wanna divide the data into fourth. When we do that, we see the break points of 75, 50 and 25. Let's say we wanna keep those. Then we'll see the breakout this way, using arrows. So quite a few choices here, all designed, of course, to give greater emphasis to the data and to make certain kinds of data stand out more prominently.
- Adjusting fonts, cell borders, and fills
- Aligning text
- Indenting data
- Setting special formats for dates, times, phone numbers, zip codes, and more
- Creating formats based on formulas with conditional formatting
- Applying styles and themes
- Adjusting row heights and column widths
- Adding background images and pictures
- Outlining data
- Printing double-spaced data