Articles > Better KPI Icons
Summary
Stephen Few presented in his book Information Dashboard Design a new, effective concept to encode KPI states, we used in our award winning Airline Dashboard. This technical article shows you how to create effective KPI icons with Excel conditional formatting.
Download the sample sheet 3StateKPIIcons.xls
Background
Often KPIs states are represented as 3 state traffic light icons:
This encoding could be problematic, since it can not be distinguished by people suffering from red-green color blindness, which is an 8% of the male population. Even worse it gives all KPI states the same visual weight.
Stephen Few presented in his book Information Dashboard Design a new concept to encode KPI states. We use small red icon that draws our attention to the problematic figures. A saturated red indicates a bad status. We encode the satisfactory state with a lighter red, what gives it less visual weight, what’s appropriate for the state. If the figure is doing well we show no icon at all.
This KPI encoding, enriched with MicroCharts sparklines and bullet graphs, does a much better job:
The sparkline as a replacement for the trend indicator shows you the figure over the last 24 months. The bullet graph, designed like a thermometer, a graduation of gray values indicate you if you are doing good, satisfactory or bad.
Better KPI Icons with Conditional Formatting
Excel conditional formatting is the main trick to get better KPI icons in Excel.
Before we go and format the KPI icons, we have to extent our Excel color palette, so that it can show the red shades. Excels 56 default colors do not have such red shades. You can modify the Excel default colors under Tool > Options > Color to configure the red shades yourself or you can simply copy the color palette from the articles sample sheet.
How to copy the color palette:
• Open 3StateKPIIcons.xls
• Open your workbook
• Open the dialog box Tool > Options > Color
• Select copy colors from 3StateKPIIcons.xls
We use the character n in the font Webdings as KPI icon, it represents a small filled circle. You have to format the cell with Webdings and the following conditional formatting:
| Rule | Font Color | Number Format |
| =B5=1 | White | ;;; |
| =B5=0 | Light Red | “n”; “n”; “n”; |
| =B5=-1 | Saturated Red | “n”; “n”; “n”; |
The cell B5 contains the KPI value state, where -1 is encoded as bad, 0 as satisfactory and 1 as good. The number format “n”; “n”; “n”; shows the character n for positive and negative numbers. When we format this cell with Webdings it shows the KPI icon.