As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013. Much of the content will be a result of my daily interactions with business users and other BI devs. In order to not forget what I learn or discover, I write it down … here. I hope you too will discover something new you can use. Enjoy!
Using Icon Sets
So far we have covered the data bar and color scale options in conditional formatting. Up next is Icon Sets. This conditional formatting option can be very valuable when creating dashboards using Excel.
Icons in Brief
Excel has a number of icon sets that can be used to visualize status and trends. These are organized in Excel into groups based on their usage as shown in the image below.
Your specific use case including the data measured and the message to communicate will determine which icon set you choose. Often icon sets are used in scorecards and with key performance indicators (KPIs).
Creating a KPI with an Icon Set
When making a choice on icons, it is important to keep in mind what you want to communicate and who your audience is. In the following example we will look at setting up a KPI or status icon set using the tri-color, differing shape set.
This is a good choice to represent data that can be shown as “good”, “warning”, or “bad” status.
Using shapes as well as colors is particularly helpful if you have color blind users.
In my example, I am using a pivot table using Power Pivot data. I have a series of poll questions and the number of submissions. Let’s start with the default implementation. First, click into the cell with the value (in my case, Submission Count). Next, select Conditional Formatting > Icon Sets > <<select the shape noted above>>. This will put an indicator in the field as noted below.
As you can see only the field selected has the icon set. What we need to do is apply the formatting to the entire column. That is done by clicking in the cell and then clicking the formatting button that pops up by the cell.
This button will show the following selections for applying formatting rules:
- Selected Cells (currently selected)
- All cells showing “Submission Count” values
- All cells showing “Submission Count” values for “PollQuestion”
Obviously, you would see your value column and your reference column names. If you have nested reference columns (e.g. a poll type) you would want to select the third option so it only was measured against the values in scope with the initial reference column. The second option will apply the formatting to the entire column no matter the level in the pivot. For this example, we will use the third option. Now look at the column results:
As you can see, the formatting has been applied to the entire column. Curious how it was applied? In the ribbon, select Conditional Formatting > Manage Rules. In that dialog, you should see the rule you created. With that rule selected (which is the only rule if you started new), select Edit Rule. In the resulting dialog, you can see that you can change how the rule is applied, the rule time, and the description. Here we will focus on the description. In the description area, you can see the Icon and the values that are applied. By default, it groups it in to percents. This is done by taking the maximum and minimum values and dividing the values into thirds and applying the value.
You can change the type from Percent to Number, Formula or Percentile. I often find that I need to use a number for that goal. However, if you use a formula you can refer to a sheet with goals, for instance, that let’s you do some what if scenarios as well.
You can also show the icon only which gets closer to the look and feel of a scorecard. I encourage you to explore the possibilities and create scorecards that can be used by your executives, managers, and other users who need to get an overview of how things are going.
Data on Wheels - Kristyna Ferris & Steve Hughes 