Join Dennis Taylor for an in-depth discussion in this video Using conditional formatting, part of Excel 2016 Essential Training.
- Excel's Conditional Formatting feature found on the Home tab, is a great tool for highlighting data, making it stand out. As you use this feature, I'm clicking on the button right here on the Home tab, you'll see the description of it. But as you use this often, its various features spill out over across the worksheet. So if you're going to be dealing with data, it's best to move it slightly left of center on your screen. I'm going to be applying this perhaps to Column H, Column I. So I'll scroll to the right a little bit. Let's imagine that we might want these salaries to stand out in a different way.
I'm selecting Column H. Now I'm not suggesting by the way that every time you use Conditional Formatting, you'll be selecting a column, but it's common to do that. We're going to change Column H. This list might grow or shrink. We want a format applied based on some conditions. It could be something simple and straightforward like highlight cell rules that are greater than. In other words, let's make salaries stand out if they are greater than a certain amount. Excel already makes a suggestion here. I'm going to change it. It pick some midpoint, I'm going to pick 70,000 here.
And it's already applying a feature that we see to the left. Red fill dark text. But there are some other choices here, and if we don't like some of these, we can try some others. In other words, the bottom here, Custom Format. So we can make them all yellow, blue, green, put on borders, fonts, whatever. If you're in hurry, fill is pretty obvious. I'll fill them with yellow. Click OK, click OK. So we see at a glance which compensation amounts here are above 70,000. And it is dynamic.
So if this salary here gets reduced a bit to 69,000, as soon as I press enter, it will no longer be yellow. One of these down here, if this becomes 71,000, it will turn yellow. And so on. You see how that works, pretty apparent the way the feature is described. let's go back here though and look at some other features. If we no longer want to use this, sometimes we just want to turn off a feature, go back to Conditional Formatting and clear the rules. If we had other Conditional Formatting in the worksheet, we could clear those rules as well with the option Clear Rules From Entire Sheet.
We simply want to clear rules from the selected cells here. But let's come back and look at some other options here. Top/Bottom Rules. Maybe we just want to highlight the compensation amounts that are above average. That probably wouldn't be a great thing to do here because people have different status and the average probably is a misleading number, but you can imagine using that. Highlight the bottom 10 percent, highlight top 10 percent and so on. Excel does all the math by analyzing the data in the selected area. A more obvious visual would be Data Bars.
And as I slide over these, you can see what's happening over in Column H. Now you might think that by making the column wider, you could see these bars better. But that's not the case. Solid fill is a little bit different, and because some of these colors are dark, the numbers are partially obscured, particularly on some of the darker colors. So maybe this gold here might work best. But if I make the column wider, the columns or the bars will grow along with the numbers. There is an option here by the way to not show the numbers.
You could do this on the fly by making the text white, but that's probably not the best way to do it. We can go back to Conditional Formatting. When you want to change one of the settings, sometimes the option is manage the rules. In other words, a rule has been applied here. Let's go manage the Conditional Formatting rule here. And although you don't see this at first, here's a choice called Edit Rule. And buried in here, amongst all these different features, is an option, Show Bar Only.
Click OK and OK. And we've got that look. So you might be making a presentation of this data and people's names are on it, and maybe you don't want to reveal their exact salaries, but you want to reveal the approximate salary compared with others. Now if you happen to slide over this, if you click on one of these, you can see this in the formula bar. So we're not throwing the data away. We're just hiding it for display reasons. Now going back to Column H, first of all, scroll to the right. Select Column H.
Back to Conditional Formatting. If we want to bring back those numbers, let's say we do for bit, we'll come back and manage the rules again. Here's the rule in question right here, it's the only one. Edit Rule, and uncheck the box that says Show Bar Only. And click OK and OK again. And if we like to get rid of this feature to apply a new one, mistakenly, what you can do sometimes is also apply color scales on top of the bars, and that really is not what you want to do. So I'm going to clear the rules here first from these selected cells and come back again with Conditional Formatting, and take a look at Color Scales.
Now what you see here initially, it might say three colors, two colors, but actually it's a continuum of colors. For example, if I make this option here, higher salaries are red, the lower ones are green, the first button here is the reverse of that. But we're not seeing just three colors, although it looks like were seeing red, yellow and green. We're actually seeing lighter greens in here, and green yellows, whatever they're called. Oranges in here as well. So if I were to click here and press the AZ button, or ZA to sort the data on the Data tab, like this, you see how it's very red there.
But as I move downward, moving into higher salaries, see how it's getting lighter and lighter and lighter. So each one of these, you could say, is slightly different if the compensation amount is different. And we're heading into the yellows and then the green yellow and so on. Eventually, it'll become dark green. You don't have to do this of course every time you're applying these colors, but it gives you some sense of how this is working. I'll press Control Z here to unsort. So that's another option. And again, that brings out the data. And here too, we have that same option if we wish, hide the salary itself, hide the actual value there, the compensation amount.
Back to the Home tab, Conditional Formatting. And once again, I'm going to clear the rules because another choice here is a favorite of some people for sure called icon sets. Now you can divide the data into thirds or fourths or fifths. And by the way, if we choose three, this doesn't mean we're going to have an equal number of red arrows and green arrows and yellow arrows. It means the values are being analyzed, and those values in the top third value wise are going to be green; bottom third, red.
So we're not going to see necessarily the same number of entries having the same arrows and so on. There's a slight redesign from prior versions, the arrows are a little more stylized. We see some other choices here, four arrows, five arrows and so on. And they've even given names to these. These are traffic lights, these are rimmed traffic lights. These are unrimmed and so on. Different choices and different icon sets. And because the so-called Live Preview effect that Microsoft talks about is in effect, we see what's about to happen in Column H before actually making a choice.
And you can see how this gives some visual weight to the data here. Here too, if you only wanted to display the relative size of a compensation amount, you might choose this option right here, and do it like we did before, back to Conditional Formatting, manage the rules, edit the rule and consider in this case, Show Icon Only and not see those particular compensation amounts. So lots of choices here. It's dynamic, and you could use this, we could use it possibly in Column I, we could use it in Column F.
Lots of choices here, relatively easy to work with once you get familiar with. And it is dynamic. If you are working with large lists like this, I think it's convenient to select the entire column because then you don't have to worry about the effect of formatting on new records that you add at the bottom, or if there's a drastic cut back and names disappear. You're not going to be worrying about this either. So it's a great feature to bring out data. I'm going to press Control Z a couple times here. I think it's better in this case maybe to see these, but you'll have different approaches as to how you might want to use this feature, Conditional Formatting found on the Home tab.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros