Icon sets

Icon sets are a great way to segregate data with an identifier based on thresholds. For example, if we can say that if at any time revenue is above a certain number, we're doing fine. If it's between two numbers, we're worried, and if it's below a certain number, we're in trouble. Icon sets let you represent data like this graphically. A firm might say that monthly revenue over 1,000,000 is fine, revenue between 750,000 and 1,000,000 is okay, and revenue under 750,000 requires management to intervene to find out what's going on.

The first tier can be represented by a green circle, the middle tier by a yellow triangle, and the last tier by a red diamond. This gives anyone even glancing at the dashboard a good idea of what needs their attention.

Tip

An option in Excel 2016 is to use the same icon but a different color to represent the different tiers, for example, a circle in red, yellow, or green. This isn't a great idea for two reasons. First, some people have trouble distinguishing colors. Second, the dashboard may get printed out for someone to take with them. If it's printed in black and white, the power of color is lost. I recommend that you use both symbols and color when using indicators.

You can also build icon sets based on any number of other criteria, including percent, a formula, or percentile rank.

For our dashboard, we'll use a straightforward example:

To set up our icon sets, follow these steps:

1. Select cells E7 through H7.
2. On the Home ribbon, go to Conditional Formatting | Icon Sets.
3. Under Shapes, select the second option, which displays a green circle, a yellow triangle, and a red diamond:
   
4. Back on the ribbon, go to Conditional Formatting | Manage Rules…
5. Click on the Edit Rules button:
   
6. To the right of the green circle icon, change Type to Number.
7. Set Value equal to 60000.
8. To the right of the yellow triangle, change Type to Number.
9. Set Value equal to 25000, click on OK, and click on OK again to close both windows:
   
   

Now, you should have icons next to the revenue amounts:

Let's do the same thing for net income using these steps:

1. Select cells E8 through H8.
2. On the Home ribbon, go to Conditional Formatting | Icon Sets.
3. Under Indicators, select the option on the right-hand side, which displays a green check mark, a yellow exclamation mark, and a red X:
   
4. Back on the ribbon, go to Conditional Formatting | Manage Rules.
5. Click on the Edit Rules button.
6. To the right of the green circle icon, change Type to Number.
7. Set Value equal to 40000.
8. To the right of the yellow triangle, change Type to Number.
9. Set Value equal to 0, click on OK, and click on OK again to close both windows:
   

Now, you should have revenue and net income with status indicators based on icon sets:

Tip

Normally, I wouldn't recommend mixing symbols like we do in this example. Mixing symbols is confusing to users. It's better to pick an icon set and stick with it, but I'm willing to break a rule or two to show off some cool options, and I think that this is a good time to break some rules.

Our initial formatting of negative numbers as red provided the bare minimum for conditional formatting. By adding them in icon sets, we were able to give context to the numbers. Even though a number is positive, it doesn't mean that the number is acceptable. Icon sets are one tool that can be used to provide that additional information. Let's move on to the very cool feature of data bars.

Tip

Though there are arrows in the icon sets, the arrows don't necessarily represent a trend. They could be used, for example, to show amounts over or under a goal. You can use a formula and arrows to show the latest direction of change. An example is when newspapers put an up or down arrow next to a sports team based on their last performance. This typically includes something like "W3" and an up arrow to show three wins in a row and a positive indicator.