Conditional Format Excel 2013

show more Conditional formatting provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training show less
please wait ...

Conditional formatting

In this worksheet called Conditional Formatting, we might want to make some of the years in column F standout a little more prominently. Conditional Formatting practically explains itself. We're saying we want certain numbers, those above a certain amount, within a certain range, to have a different look. And as we make the choice here, we've selected column F ahead of time, Conditional Formatting, highlight Cells Rules. How about those Greater Than? Greater than what? And immediately, we see some color changes in column F. We haven't even decided what we want yet.

Format cells that greater than eight. Well, maybe we want to do this for those that are greater than nine in other words the 10's and above. Do we want to use light red fill and dark red text? Well, maybe so, that looks okay. We can see it already. We don't get a preview on these but we can imagine what some of these we might use. How about a red border maybe, something like that? Well, that looks okay. How about red text or whatever? And if these aren't good enough, create your own custom format. How about yellow fill? Anyway, click OK. You've made your choice.

It is dynamic, too. If we make an adjustment to the Hire Dates and one of these years slips below 10, it's not going to be highlighted anymore, or one goes above, the opposite effect, it will be highlighted. But there are other features, too, maybe on the salaries here. How about those above average? Conditional Formatting, we've got Top/ Bottom Rules here, and quite a few choices. We might want to highlight just the top 10% or the bottom 10 entries or top 10. How about those above average? There we go. And once again, a similar kind of selection, we may or may not like this capability here.

Let's just cancel this. We can certainly explore that option, too. Let's go back again to Conditional Formatting. How about Data Bars? What we see here is a different bar for each entry, and the width of the bar, more or less, conforms to the higher salaries, as we see this. Now it's a little bit difficult perhaps to read the numbers or read the bars depending upon how you're viewing this. But we can see what's happening here, a quick visual addendum to the data here to point out which of the salaries are higher. And there is by the way, as we'll see, a way to hide the numbers and just show the bars.

So, that's certainly an option there. A lot of choices here, too. And in a similar vein, how about Color Scales instead? What Excel does here is it divides the data into fifths or sixths or whatever here and then applies color here. It actually uses more than you might think at first, and some of the color shadings are very subtle. It's a bit hard to figure out the differentiation on some of these colored variations. But I think you can have a sense here. In the example, if I were to pick this one, the lowest salaries are dark green and the highest salaries are dark red. And the early one here, this is just the reverse of it-- higher salaries are dark green, the lower salaries are dark red.

So you've got some options here, too. All these selections here are dynamic and they do react to changing data. So you might want to try that, maybe better yet though. How about Icon Sets? This will divide our data into thirds with these choices here using different icon choices; or how about fourths this way or fifths even? And we've got different shapes, different choices here. We might want to use, say this scheme here--four arrows. Greens are the highest salaries, reds the low, the two yellows are in between.

If we don't want to see the salary, we have to come back and choosing Conditional Formatting, Manage the Rules. Now we're not deleting the cell, what we're doing is hiding the salary and we do this by way of editing the rule. So here's the rule in question, Edit the Rule. And there's a box, Show Icon Only. Click OK. So if you want to make a presentation of this data, click OK again, and not show the actual salary--but just give a rough idea of which range this falls into--we probably want to center this as well, this way then we have that.

Now, if you click a cell, you will see the Salary in the formula bar and it's still there. But nevertheless, sometimes this gives us the broad picture that's all we want for a certain presentation. And certainly with Job Rating, we could certainly use some of the schemes here, too. So it's a rich feature. It will take you a long time to explore a lot of the variations here. The three major ones, the ones that get the most attention are Data Bars, Color Scales and Icon Sets as we'd just seen in these examples here.

Conditional formatting
Video duration: 4m 21s 6h 32m Appropriate for all


Conditional formatting provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training

Business Education + Elearning
please wait ...