You can set up your workbook as a dashboard to showcase your company's performance. The common trend is to use icons to match performance to goals. You can achieve this in Excel 2013 by formatting cells using icon sets. In this training video, you'll learn how to apply an icon set conditional format to your PivotTable.
- One of the most popular trends in corporate management is to use icons to indicate how a company's performance compares to its goals. If you set up your workbook as a dashboard that summarizes performance, you can use indicators such as the familiar red, yellow, and green stoplight images to indicate unacceptable, acceptable and excellent results. In Excel 2013, those groups of indicators are called icon sets. To demonstrate an icon set conditional format as applied to a pivot table I will use the IconSets.xlsx Excel workbook.
You can find that in the Chapter05 folder of your Exercise Files collection. To apply an icon set conditional format to a pivot table click any cell in the data area. In this case I'll click cell B6. So I have the sales for each of my five departments. And then on the Home tab of the ribbon click Conditional Formatting and then point to Icon Sets. There are many different sets to choose from and they have from three to five different icons and you can see that there are all manner of different values.
In this case, I will stick with what I consider the traditional or usual red, yellow, and green stoplight shapes. Those are here in the second group. I'll go ahead and click them, and Excel applies that format to the selected cell. I want the format to be applied to all cells on the same level, in other words to cells B4 through B8, but excluding the Grand Total which is much larger. I'll go ahead and click the selected cells' Formatting Options action button and I'll bypass Selected cells, that's what's currently selected, All cells showing "Sum of Sales" values, which would include the Grand Total which is not what I want, and go for the final entry in this list, All cells showing "Sum of Sales" values for "Department".
Now there we go. And you can see that I have only two icons in use and the smallest values, $81,286 and $130,208, are substantially smaller than $252,486, $294,521 and $319,704. So it's no surprise that there are no intermediate values. You can change your icon set by changing the thresholds where the different icons are used. To do that select any cell in your pivot table, then click Conditional Formatting and click Manage Rules.
Click the Icon Set rule that you created and click Edit Rule. So here we have the Edit Formatting Rule dialogue box. The cell B4 is currently selected but when I apply the rule it will be applied to All cells showing "Sum of Sales" values for "Department" so I don't need to worry about the selection. I'm not applying it to individual cells. In the bottom of the Edit Formatting Rule dialogue box I have the Edit the Rule Description controls and this is what we're going to focus on. I can change the Format Style.
For example, I could go to a Color Scale or a Data Bar. Or I could also change my icon set, or my Icon Style. So if I click the Icon Style button's down arrow I can select any of the other sets that are available. If we look down at the very bottom we'll see that we have our three icons, green, yellow, and red, and we can see when they are applied. And we see that the green icon is applied when the value is greater than or equal to 67 Percent of the largest value.
So that means any value in the top third of the range. The yellow icon is used for values in the 33 to 67 Percent range, and red is when it's below 33 Percent of the maximum. We can change those values and actually we can change it in two ways. The first would be to edit the percentage value. For example, if we wanted green to be for anything greater than or equal to 80 Percent of the largest value, and yellow for anything between, let's say, 25 Percent and the largest value.
You could type in 80 and 25 in the Value boxes that apply and click OK, and click Apply. And you can see that now the value in cell B7 has a yellow icon attached to it. I'll go back to the Conditional Formatting Rules Manager, click Icon Set and click Edit Rule. And now I will change the rules so that instead of using percentages we will look at actual hard values. To do that for the green icon I'll click the Type controls down-arrow and I'll click Number.
So now if the number in a particular cell is greater than or equal to some value I enter then we will get a green icon. So I'll edit the top Value so that it is equal to 250000. So that's 250 followed by three zeroes. Now I will change the yellow icon's Type also to Number, and I will change that Value to 120000.
Everything looks good. I'll click OK and I will Apply that format. And I'm clicking Apply rather than OK so I don't close the Conditional Formatting Rules Manager dialogue box. Then I click Apply. And you can see that the new rule has been applied. We have three green cells, a yellow and a red. Click Icon Set again, click Edit Rule. And now I will Reverse Icon Order. So we're assuming, even though the worksheet doesn't really say this, that we're looking at expenses as opposed to income.
So if it was better for a value to be smaller rather than larger then I could click the Reverse Icon Order button. So now instead of having green on top I have red followed by yellow followed by green. Click OK, and this time I will click OK so that I close the Conditional Formatting Rules Manager. Then you can see that the values have flipped. We have red for the largest values, yellow for the middle and green for the smallest. Icon sets can provide useful information about large data sets but they really come into their own when you use them to summarize a relatively small amount of data.
If you, or your executive team, monitor company performance using dashboards you should strongly consider using icon sets to enhance your ability to understand the dashboard summary at a glance and to determine whether to reward strong performance and when to investigate under-performance.
- Creating a PivotTable
- Summarizing multiple data fields
- Managing subtotals and grand totals
- Grouping PivotTable fields
- Filtering with selections, rules, slicers, and search filters
- Applying PivotTable styles
- Formatting cells
- Creating PivotCharts
- Enabling PowerPivot
- Using DAX operators
- Visualizing data with matrices, cards, and tiles
- Building charts and maps