Variations of the Alternative Bullet Graph Design: Visualization of Gaps and Exceedances, two Targets and two Gaps, conditionally formatted Actuals and dynamically sorted Multiple Rows Bullet Graphs
One of the previous posts presented An Alternative Design of Bullet Graphs: no qualitative ranges, additional data labels and a visualization of the performance gap.
These ideas can be taken a few steps further:
- Bullet Graphs with a generalized visualization of the deviation of actual from target: display the gap, if actual is smaller than target and the exceedance otherwise
- Bullet Graphs with two targets
- Bullet Graphs with two targets and two gaps
- Bullet Graphs with a conditionally formatted actual bar
- Multiple rows Bullet Graphs in alternative design
- Dynamically sorted multiple rows Bullet Graphs
Today’s posts presents this set of possible variations of the alternative Bullet Graph design. As always, the article includes a link to download the Microsoft Excel template workbook for free.
Recap 1: Original Bullet Graph
Let’s start with the well known original: a Bullet graph according to Stephen Few’s Bullet Graph Design Specification implemented in Microsoft Excel:
A black bar to visualize the metric, a red line for the target and three grey backgrounds for the qualitative ranges of poor, satisfactory and good. Nothing new under the sun.
Recap 2: Alternative Design of Bullet Graphs
The recent post (An Alternative Design of Bullet Graphs) suggested three modifications to the original Bullet Graph design:
First, the simplified version waives the qualitative ranges and adds data labels to the metric and the target:
Secondly, the value of the performance gap (target minus actual) is shown. A span line with arrows at the beginning and end sets this value into context, i.e. visually explains what the number means:
Finally, the gap may also be displayed in percent of the target value instead of an absolute number:
So much for the recap. If you are interested in the details, please refer to the post An Alternative Design of Bullet Graphs.
Variations of Alternative Bullet Graphs
As mentioned in the introduction, several variations of the alternative Bullet Graph design are conceivable:
Variation 1: Bullet Graph with Gap and Exceedance
Since the alternative Bullet Graph visualizes the gap between actual and target (if actual is smaller than target), it seems natural to also display a possible exceedance:
Variation 2: Bullet Graph with Gap and Exceedance in % of Target
According to the original alternative design, the exceedance may also be displayed as a percentage of target:
Variation 3: Bullet Graph with 2 Targets
For some metrics you may have to display two target lines instead of only one:
This may be the case if you have e.g. a minimum (“must-have”) and a maximum (“nice-to-have”) target. Having said that, the second reference line does not necessarily have to be a target. It can also visualize e.g. the actual value of the previous reporting period. Whatever it is, sometimes you need more than one reference line in your Bullet Graph.
Variation 4: Bullet Graph with 2 Targets and Gaps
It goes without saying that the idea of visualizing the gap between actual and target can also be applied to a Bullet Graph with two targets.
Different design options are possible:
The first option only displays the gap between actual and the next target. If actual is smaller than the lower target, the Bullet Graph looks like this:
If the actual is between the two targets, the Bullet Graph looks like this:
Option 2 visualizes both gaps in case the actual is smaller than the lower target. Gap one displays the deviation from the lower target, the second gap shows the difference between the two targets:
If the actual is greater then the lower target but less than the upper target, the Bullet Graph looks exactly as it does in the first option (except for the line color of the span line):
Third and last option also displays both gaps, but shows the deviation of the actual value from the two targets:
I would definitely recommend option 3, although – as you may have assumed - it is the most complicated to implement.
Variation 5: Bullet Graph with 2 Targets and Conditionally Formatted Metric
The next alternative uses a conditionally formatted bar to visualize the metric, e.g. with a traffic light color coding. If the actual value is smaller than the lower target, the bar turns red:
If the actual is between the two targets, the bar is yellow:
Finally, you guessed it, if the actual is greater than the upper target, the bar turns green:
Now, you may argue that the conditional formatting is a redundant information, because the actual bar and the target reference lines already visualize the three possible states (below lower target, between targets, above upper target). I fully agree, the color code of the bar is redundant. No doubt about it.
So, why color coding the actual? Well, in some cases the conditional formatting can be helpful. Imagine you have a dashboard with twenty or even more Bullet Graphs. If the actuals are color coded as shown above, the dashboard reveals the overall status at a glance and guide you easily to the problematic underperformers even without having a closer look.
Variation 6: Multiple Rows Bullet Graph with Gaps and Exceedances
Variation 6 does not include new design modifications. It is simply the multiple rows version of variation 2 in case you have to compare metrics of more than one category like sales regions, profit centers or the like:
Variation 7: Sorted Multiple Rows Bullet Graph with Gaps and Exceedances
Sorting data is one of the most basic and most helpful techniques in data analysis. This is especially the case for Bar Charts. Sorting the bars adds structure to the view and facilitates to understand the results and the story. Since a multiple rows Bullet Graph is just an enhanced standard bar chart, your view should enable the user to easily and dynamically sort the bars.
Two data validation drop down lists at bottom left of the chart allow the user to dynamically select the sort criteria (values, targets, gaps, gaps in % of target) and the sort order (ascending, descending, none).
Here is the view shown above sorted descending by values if you are interested in the actuals in absolute numbers:
Alternatively, you may sort by targets:
In order to quickly identify the main underperformers, you probably want to sort the view by gap in absolute numbers…
… or – even better – by gap in percent of target:
Finally, here is the same view sorted ascending instead of descending:
An Enhancement: the Definition of a Minimum Deviation from Target
If the deviation (gap or exceedance) from target is very small, it is very likely that the label of the deviation will obscure the span lines and maybe even the data label of the metric:
To avoid this, you can choose a minimum deviation in % of target. The span line and the gap/exceedance data label will then only be displayed, if the deviation equals or is greater than this parameter. For instance, if you set the minimum deviation to 10%, the target is 5,000 and the gap is 500 (or greater), the deviation will be displayed:
If the gap is only 499 (i.e. less than 10% of target) or smaller, the span line and gap label will be suppressed:
The reasoning behind that: relatively small deviations from target are probably less important and you may prefer omitting the gap information over possible overlaps. If you want to display the gap and exceedance in any case, simply set the value of the minimum deviation to zero.
The Implementation in Microsoft Excel
Providing a step-by-step tutorial of how to implement all the Bullet Graph variations in Microsoft Excel would go far beyond the scope of this article.
Having said that, you can find a brief description of the implementation of the alternatively designed Bullet Graph in Excel at the end of the recent post. All variations shown above are more or less modifications of the technique described there.
If you are interested in the details of a specific chart type, please download the workbook (download link see below) and have a look for yourself.
An Excel Oddity
If you download the workbook (see next section) and have a closer look at the implementation of the third option of variation 4 (worksheet [Two Targets Gap 3]), you probably wonder why I chose a rather complicated approach with a multiple rows Stacked Bar Chart displaying the actual and the gap labels. You may ask:
“Wouldn’t it be easier to plot the gaps as scatters on the secondary axis and use the option ‘Label contains Value from Cells’?”
Actually this was my first approach, but I encountered a serious issue: from time to time, Excel “loses” the gap labels. By losing, I mean the labels are still there and Excel even remembers the correct cell references, but the numbers are not displayed in the chart anymore. This happens only after closing and reopening the workbook and only if the values and/or targets changed. I couldn’t figure out why. An Excel oddity (or maybe even a bug).
Since the ‘Value from Cells’ option for data labels seemed to be flaky sometimes, I decided to find another way and used the multiple rows Stacked Bar Chart.
Download Link
Here is the link to download a zipped folder with a Microsoft Excel workbook containing the original Bullet Graphs, the alternative designs and all variations described above on 13 worksheets:
Download Variations Alternative Bullet Graphs (zipped Excel 2013-2016 workbook, 85K)
The advantage of this workbook: there is no VBA included and each worksheet is independent from all others. I.e. if you want to use one of the templates, you can simply copy the worksheet to your own model and link the input cells (light grey background colors) to the results of your workbook. Or – maybe even better - you copy the templates to your own Excel Chart Template Collection (I hope you are having one…).
So much for the alternative design of Bullet Graphs. At least for now. I may come back again to this topic in a couple of weeks.
More other things to come soon.
Stay tuned.