Data Visualization with Excel's Conditional Formatting

Conditional formatting. You'll use conditional formatting to format cells based on that cell's value. This is the perfect topic to go over before we take a look at charts because this is a way of visualizing information on the information itself.

In order to apply conditional formatting, you're going to need to select a range of cells. The conditional formatting drop down command is on the home tab. We have five exercises we can work with here.

I, as an instructor, usually go over the first exercise. I think of a situation where I might be asked to highlight all the values that are less than 80,000. So within this table, I'm going to select all the cells in the total sales column.

I need to do this because I have to select all the cells that could potentially be formatted. It's one of the few instances where you'll need to select everything before you do something in Excel. Most of the time you could just select one cell, but in this case, we're going to select all the values.

We'll go to the home tab, and then we'll go to conditional formatting. Now you have several preset options. We'll start with the first one, which is highlight cell rules.

I want to highlight cells when the values are less than, and then I'll click that option, and then I get to enter the amount. Now it's defaulting to 122,500, which is not the amount I want. I want to format cells that are less than 80,000.

A default formatting option is selected here, light red fill with dark red text, but I could always click the drop down and choose another default preset, or I can click custom format and create my own custom format. I'll leave it at light red fill, dark red text, and I'll click OK. Now someone can instantly see all the values that are less than 80,000.

Then I challenge the students in the class to do the following exercises. Now if they get stuck in this spreadsheet, if I click on the little plus sign, it will actually reveal the answer. So let's take a look at exercises 2, 3, 4, and 5. Which stores are exceeding sales expectations, earning more than 120,000? Well if I click the plus sign, I'm going to be asked to use the same range that I just used.

I'm going to head over to conditional formatting and choose highlight cell rules, but instead of choosing less than, I'm going to choose greater than. And when I click here, I'll type in 120,000. Now if I don't change the color, I'm going to run into a problem here.

I'm going to confuse a lot of people who are not going to be able to tell the difference between values that are less than 80,000 and values that are greater than 120. So it would be appropriate here for me to choose a different color. I'll choose green fill with dark green text.

When I click okay, now people can visualize the data using colors. Next, does each store have its own full-time manager? An appropriate answer to that question for someone not familiar with conditional formatting would be, how do I know? I don't work there. Well in this case, they're not asking you for inside information.

What they want to know is, is there a situation where there's a manager working at more than one store? Because if that's the case, then each store does not have its own full-time manager. So I'm going to select all the values for manager. I'll head over to conditional formatting and over in highlight cell rules, there is an option called duplicate values.

If I click that, I get to choose a color that will apply for duplicate values. I'll click the drop-down and choose yellow fill with dark yellow text. I'll click okay and now I can answer the question, does each store have its own full-time manager? The answer would be no.

Poor Michelle is working at stores 2 and 13 at the same time. I'll click the plus sign here and there's the answer. Which locations have above average customer accounts? According to this, I want to highlight all the values for customer accounts.

I'll go to conditional formatting. When I go to highlight cell rules, I will not find my option here to highlight cells that are above average. So this is not the only place that you can go to when it comes to conditional formatting.

If you move down just one menu option down, you'll have top and bottom rules. And there you can highlight values that are the top 10 items, top 10 percent, bottom 10 items, bottom 10 percent. If you're looking for the top, let's say 5 percent or top 1 percent, when you click these options, you can actually change the value from the default 10 that's there.

For this exercise, I want to highlight all the values that are above average. All it takes is one click. I'll choose yellow fill and now every single value that's above average is highlighted in yellow.

I'll simply click OK and there we go. I didn't have to come up with the average. Excel is automatically doing that behind the scenes.

Now for our last exercise, I want to visualize our average dollar per sale. So I'm going to highlight that column just like we would do with all conditional formatting. I'll go to conditional formatting and look for data bars.

Data bars allow you to visualize your data as if the column was a bar chart. It's going to actually use the values in the cell. So this is in cell formatting.

Now for data bars, I'm told to go over to solid fill and choose any color I want. I'm going to choose green. I'll click here and just like that, I can instantly look at this list and know which values are larger than others.

I don't have to look at the digits to figure that out. Now I want to do something to change the appearance of this column appearance. So what I want to do is select the entire column and I don't want to see the numbers.

I'd much rather look at this column of values as bars. So I'll head over to conditional formatting. I'll head over to manage rules and when I choose manage rules, I'll be able to manage the rules that I've just selected.

I'm going to select data bar and choose edit rule and then the option I want to select is the only check box in this dialog box. There's lots of options to choose from here. We're only focusing on this lesson.

We're focusing on show bar only. I'll click the check box there and this does what it says. It will only show the bar.

So show bar only. Now when I click the first okay, nothing yet happens. That's because we haven't applied it by either clicking the final okay or clicking apply.

If I want to see what it looks like before I click the final okay, I'll just click apply. Now I can see what it's going to look like. If I like what I see, then I can click the final okay and now I have my data bars applied to the average dollar per sale column.

There are a couple of other options that we didn't look at just to give you a sense of what of how they would look if you were to apply them. We could also apply color scales. These are different colors based on the amount that you have in the column.

This is green, yellow, and red. So green is the highest value. Yellow is somewhere in the middle and red are the lower values.

You also have icon sets. This is where it becomes its most iconic. You're using icons to display the values as either high, medium, or low.

You can use arrows. You can use stoplights. You can use geometric shapes, flags, stars, pie charts.

So this gives you a quick visual indicator for values that are high, low, or in the middle. Down below, if you scroll down, we have some reference material that pretty much covers what we just went over. You can use conditional formatting to quickly highlight your data and showcase values that are either duplicates, higher or lower than a certain value, and visualize your data.