Conditional formatting allows you to format cells based on that cell's value and a given condition. We'll walk through various examples and applications of conditional formatting in this video tutorial.
Conditional Formatting
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 gonna 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.
Exercise 1: Less Than
I usually go over the first exercise together. I think of a situation where I might be asked to highlight all the values that are less than 80,000. 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 can just select one cell but in this case, we're gonna 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 highlights 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 120 to 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. I'll click OK. now someone can instantly see all the values that are less than 80,000.
Exercise 2: Greater Than
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 & 5. which stores are exceeding sales expectations earning more than 120,000. if I click the plus sign I'm gonna be asked to use the same range that I just used. I'm gonna head over to conditional formatting and choose highlight cell rules but instead of choosing less then I'm gonna choose greater than and when I click here I'll type in 120,000.
Now if I don't change the color I'm gonna run into a problem here. I'm gonna 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,000. 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 OK, now people can visualize the data using colors.
Exercise 3: Duplicates
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 gonna select all the values for the 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 texts. I'll click OK 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 two and thirteen at the same time. I'll click the plus sign here and there is the answer.
Exercise 4: Averages
Which locations have above average customer counts? According to this, I want to highlight all the values for customer counts and another 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 roles and there you can highlight values that are the top 10 items, top 10%, bottom 10 items, bottom 10%.
If you're looking for the top let's say 5% or top 1%, 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.
Exercise 5: Data Bars
Now for our last exercise, I want to visualize our average dollar per sale so I'm gonna 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 gonna 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 gonna 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 even have to look at the digits to figure that out.
Now I want to do something to change the appearance of this column. 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 and head over to manage rules and when I choose to manage rules I'll be able to manage the rules that I've just selected. I'm gonna select the data bar and edit rule and then the option I want to select is the only checkbox in this dialog box. there are lots of options to choose from here we're only focusing on this lesson we're focusing on the show bar only. I'll click the checkbox 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 ok 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 gonna look like. If I like what I see then I can click the final ok and now I have my data bars applied to the average dollar per sale column.
Other Options
There are a couple of other options that we didn't look at just to give you a sense 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.