Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Many business managers keep track of their company's performance using worksheets called dashboards, which presents summary information on a single worksheet. In this movie, I'll show you three types of conditional formatting: data bars, color sales, and icon sets, that you can use to format your data to help you see how your company is performing. The first of these three conditional formats is called data bars, and what they show is the relative magnitude of values using a horizontal bar within a cell that contains the value. So, for example, in these cells here, B4:B15, I'll select them, and then I'll apply a data bars conditional format.
To do that, on the Home tab of the Ribbon, I click the Conditional Formatting button, point to Data Bars, and then I can select the color and style of data bar that I want. If I were to apply a gradient fill, that fill, the color bar, starts fully solid at the left edge, and then it tails off and gets lighter as you go to the right. So, it can be a little bit tricky, visually, to compare values, so that's why I like to use a solid fill, such as the orange data bar. When I click it, Excel applies the data bars to the cells.
So, you can see that the largest value, 29,000, has the largest data bar and that the smallest value, the one here 15,000 has the shortest data bar, but also the data bar is relative to 0. So, in other words, the largest value, 29,251, has longest data bar, and it extends all the way to the right edge of the cell. Because the value 15,670 is about half of that, it only extends half as far. Data bars are best used in vertical data lists, where the values can be compared visually, as is in this case with monthly sales.
The second new type of conditional format I'll talk about is the color scale. The color scale creates what some authors call a heat map, where a color gets more or less intense based on its value within the range. So, for example, here we have the same set of values, and I will select them, click Conditional Formatting, point to Color Scales, and then I'll select one of the scales here. You can select from three color scales - such as green, yellow, red - or two color scales, and the one that I will pick will be green and white.
So, in this case, the highest values will be green, and the lowest values will be white, and you'll see how that various within the color scale. So, the smallest value, 15,670, that cell, its fill is now completely white, and the largest value, 29,251, its cell value is the green or the darkest green available within the scale, and the other cells contain some level of green that increases an intensity based on the size of their value. The third new conditional format that I'd like to cover is called an icon set.
I'll just show you what it looks like, and then I'll show you how to modify that format. To define an icon set conditional format, you select the cells to which you want to apply the format, click Conditional Formatting - again, we're on the Home tab of the button - point to Icon Sets. Then you can select the set of shapes that you want to use. The shapes that you'll see most commonly on executive dashboards is this set here, the three outlined traffic lights. So, I will select it, and when I do Excel applies what it thinks would be a good rule to those cells.
So, some of the cell values have a red stoplight - those are the lowest ones - the highest values have green, and in the middle we have yellow. Now, I am going to select the cells again and edit the conditional format by clicking Conditional Formatting and clicking Manage Rules. When I do, the Manage Rules dialog appears and my Icons Set rule appears, and it's highlighted. So to edit that rule, I can click Edit Rule, which displays the rule inside the Edit Formatting Rule dialog. The details of the Icons Set rule are here.
So what it looks like Excel is doing is applying a green traffic light icon to any cell that contains a value that is in the top 67% of the value range, yellow for any value that is between 33 and 67%, and any value in the bottom-third that is below 33% is formatted with the red traffic light. What I want to do is change the rule so that we use definite numbers instead of percentages. To do that, I will click the Type control's list arrow, and then I can select either Number, Percent, Formula or Percentile.
We've seen what Percent looks like, so I will click Number, and when I do, Excel changes the value in the Value cell to 0. It's very important that you change the Type first, as opposed to changing the value first, and I'll show you why when we change the next rule. So, here we have the number, and let's say that I want anything greater than 20,000 to be given a green traffic light icon. So that's correct. And now, I can come down here, and let's say that I want anything less than 20,000 but greater than 16,000 to be given a yellow traffic light icon.
But I haven't changed the Percent yet so I need to change the Type to Number. But when I do, you see that Excel deletes the value that was in the Value cell before. That means I have to reenter it, and that's why you always want to change the Type first so you don't have to redo your work. So I'll make that 16,000, and when I press Tab I see that anything with a value greater than or equal to 20,000 will get a green icon; anything between 16,000 and 20,000 will get yellow; anything below 16,000 will get red.
Everything looks good. I'll click OK, get the Manage Rules dialog back, click OK, and Excel applies the rules. So, you can see anything above 20,000 is green, all the values between 16000 and 20,000 are yellow, and this one value which is below 16,000 is formatted with a red traffic light. 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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98797 Viewers
80 Video lessons · 141650 Viewers
59 Video lessons · 59993 Viewers
52 Video lessons · 73163 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.