In this video, learn about the three types of conditional formatting—data bars, color scales, and icon sets—that you can use to format your data to help you see how your company is performing.
- [Narrator] Many business managers keep track of their company's performance using worksheets called dashboards, which present summary information on a single worksheet. In this movie, I will show you how to create three different types of conditional formats that are very useful on dashboards: data bars, color scales, and icon sets. Even though this worksheet is not one you'll probably see in a dashboard, the formats that you see will be. My sample file is the new conditions, or NewConds workbook, and you can find it in the chapter six folder of the exercise files collection.
This workbook contains three copies of the same data so that you can visually compare how the three initial formatting styles that I'm gonna present work. I'll start with a data bar, and what a data bar does is create a bar within a cell, and the length of the bar is relative to the value within the cell. So I will select cells B4 through B15, which represent the number of contacts for months within a given year.
Then on the home tab of the ribbon, I will click the conditional formatting button, point to data bars, and then I will select the orange solid fill, and that's here in the bottom left. I'll click that, and you can see the values. Couple things to note is that the smallest value used to be all the way to the left, and that would distort the difference between the values. Instead, you can see the smallest values, 20,808 and 20,516, have bars that extend beyond the center range, it's about 3/4 way of the cell, whereas the largest values, such as 28,306, extend almost all the way to the end.
The reason I chose a solid fill instead of a gradient fill is because the gradient fill fades out as it moves to the right, and it lessens the distinction between values, so I prefer to go for the solid fill. Next, let's look at color scales. I will select cells E4 through E15 and then, again on the home tab of the ribbon, click conditional formatting, point to color scales, and then I can select one of the built-in scales.
You have a selection of two and three color scales. In this case the higher values are better, lower values are not as good. So I will click the third item in the top row, and that is green, white, red. And I'll click that. It's applied. And you can see that the higher a value is, the more green its cell is, the lower the value is, the more red its cell is, and mid-level values below or average are red, slightly red, more pinkish, and then a very light green for values that are above average, with the color getting darker as a value gets larger.
And as you can see, there were a number of different formats you could apply, and also if you select those cells again, click conditional formatting, and then point to manage rules, or click manage rules, you can click the format and then click edit rule, and you can change the colors. You have a three color scale, so instead of this medium red, let's say that we have a light purple. Go one shade darker.
And then for the top, let's make it orange. Click OK. Click OK, again, and that's how that would appear. Now it's not the greatest format in the world, but it gives you an idea that you can change things. Finally, you can apply an icon set. Icon sets display a particular icon or graphic within a cell. So I'll select cells H4 through H15, home tab the ribbon and click conditional formatting as before, point to icon sets, and here you have a number of shapes or sets of shapes that you can apply, and they can be either sets of three, four, or five.
I'll go with what is traditional now, the traffic lights. So I will, under shapes, click the three traffic lights that have black rims around them, so I'll click that selection, and you can see they're applied. So we have only green and red. There are no yellow. Now that's interesting, but if we expect a certain level for a given month, then we can edit those rules. So with the cells still selected, I will click conditional formatting, click manage rules, click the icon set rule, and click edit rule, and here I have values 67%, 33%, so basically top third, middle third, and bottom third, except in this case, we don't have a middle third.
So I'll drag the edit formatting rule here. Let's say any value of 27,000 or higher will be green, so green when the value is, and we'll change type from percent to number, and the value will be greater than or equal to 27,000. Good. For yellow, we'll change percent to number, and it is important to change the type first, otherwise, if you change value, sometimes type will change back, and you'll have to redo your work. So we have our value.
Oh, let's say that is 22,000, greater than or equal to 22,000, and it'll be red when it is less than those. Click OK. Click OK again, and it's been applied. So you can see that we have a lot of green, a lot of yellow, and a fair amount of red as well. As you can see, data bars, color scales, and icon sets are valuable additions to your Excel workbooks. Not only can you determine at a glance how your business is performing, you can use the information to delve further into your data, resulting in even more insights.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks