Join Dennis Taylor for an in-depth discussion in this video Create value-based formatting using logical operators, part of Excel 2016: Advanced Formatting Techniques.
- One of Excel's best features for making your data stand out is called Conditional Formatting. It's found on the Home tab, and as you slide the mouse over it, get the description. Easily spot trends and patterns in your data using bars, colors, and icons to visually highlight important values. It's relatively simple to use and it really does add impact to certain kinds of worksheets. In this worksheet we've got a column here, H, labeled Performance. The ratings are from one to five. We'd like to make the fives stand out a little more prominently.
We can surely see the numbers, but, let's select column H. Now, many, many times, but certainly not all the time, you'll be selecting an entire column before using this feature. And if this is the kind of list that might grow or shrink, we want to make sure that the rule that we're about to use is in effect in column H no matter how many entries are there. Let's go to Conditional Formatting. By the way, many of the features here sort of fall down over the worksheet. So, if we're concerned about column H and watching it at the same time, we might scroll to the right or to the left a little bit. Not truly necessary, but occasionally that's useful.
So, column H is still selected. Conditional Formatting. Some simple rules could be applied here. What if we simply want to say if this cell is equal to, and already it's prompting us for the number three here, we don't want to use that. Perhaps we use five. It's suggesting formatting scheme, which could be okay. We might keep it. We might choose some of the other options. There's no live preview feature here showing how this might look, but, still we can choose some of those or possibly even Custom Format.
Maybe we want to use some kind of a Fill pattern here. Pattern and or color. We don't like the colors and so on, we can always make our own changes. How about that bright green? So, all we're saying is format the cells that are equal to five, we give them a custom format, looks like this. And this certainly does stand out. Along with that green we could have made it bold, also, and make it stand out even more. And the feature is dynamic. When we're adjusting these entries here, Nathaniel Ramsey here really had a five. We'll change that to a five and immediately the cell turns green.
And on the other hand Bill's rating here was a mistake. That should have been a four. We'll change that to a four. And we can see how it's working just fine. If we wanted to put in a different color for the ones we could come back and do that, too. There are lots of variations on this feature, too. Come back with Conditional Formatting, Highlight Cell Rules, and you can see the move obvious ones. Greater Than, Less Than, Between certain amounts. And you can imagine doing this possibly with the salary columns, as well. And there are some other features here, too. Top/Bottom Rules, the Top 10 Items in performances.
Now there it probably wouldn't be very useful because there are a lot of ties. It's a big list, and that sort of thing. But in salaries maybe that would be useful. So, we have some other choices. How about all the ones Above Average? Again, in the performance column, wouldn't be so significant. So, let's switch over into one of the salary columns. Column G. Which of these salaries is above average? By the way, you might take advantage of the auto complete feature if it's active on your screen. I selected column G. I look at the bottom here. The average salary in this list is 56,500.
So, Conditional Formatting, Top/Bottom Rules, how about all those Above Average? And there they are. We can keep that format or decide to use a special format, and so on. There, too, the same rules are in effect in terms of changes. If we make some changes here, and we don't know what the average is unless we look here again, but if the average goes up some of these numbers might stay red, some of them might shrink depending upon what's happening here, and depending on how that average is moving. So it's always in sync with the current data. In other words, the analysis of whatever needs to be done here, say, average, these numbers are always being taken into account and in the format applied when necessary.
So, relatively straight forward and easy to use. Conditional Formatting. It's found on the Home tab in the ribbon.
- Controlling fonts, borders, and cell color
- Adjusting horizontal and vertical cell alignment
- Creating angled text
- Adjusting numeric formatting
- Building custom date and numeric formats
- Creating value-based formatting
- Working with tables
- Using styles and themes
- Adjusting row heights and column widths
- Hiding and unhiding sheets
- Copying formats
- Formatting text with Justify
- Displaying data as outlines
- Formatting images and sparklines
- Creating picture links
- Formatting column and row titles