Variations of Alternative Bullet Graphs in Excel

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:

Original Bullet Graph - click to enlargeA 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:

Simplified Bullet Graph - click to enlargeSecondly, 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:

Bullet Graph with Performance Gap - click to enlargeFinally, the gap may also be displayed in percent of the target value instead of an absolute number:

Bullet Graph with Gap in % of Target - click to enlargeSo 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:

Bullet Graph with Gap and Exceedance - click to enlargeVariation 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:

Bullet Graph with Gap and Exceedance in % of Target - click to enlargeVariation 3: Bullet Graph with 2 Targets

For some metrics you may have to display two target lines instead of only one:

Bullet Graph with 2 Targets - click to enlargeThis 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:

Bullet Graph with 2 Targets and Gaps – Option 1 - click to enlargeIf the actual is between the two targets, the Bullet Graph looks like this:

Bullet Graph with 2 Targets and Gaps – Option 1 - click to enlargeOption 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:

Bullet Graph with 2 Targets and Gaps – Option 2 - click to enlargeIf 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):

Bullet Graph with 2 Targets and Gaps – Option 2 - click to enlargeThird and last option also displays both gaps, but shows the deviation of the actual value from the two targets:

Bullet Graph with 2 Targets and Gaps – Option 3 - click to enlargeBullet Graph with 2 Targets and Gaps – Option 3 - click to enlargeI 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:

Bullet Graph with 2 Targets and Conditionally Formatted Metric - Red - click to enlargeIf the actual is between the two targets, the bar is yellow:

Bullet Graph with 2 Targets and Conditionally Formatted Metric - Yellow - click to enlargeFinally, you guessed it, if the actual is greater than the upper target, the bar turns green:

Bullet Graph with 2 Targets and Conditionally Formatted Metric - Green - click to enlargeNow, 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:

Multiple Rows Bullet Graph with Gaps and Exceedances - click to enlargeVariation 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:

Multiple Rows Bullet Graphs with Gaps and Exceedances sorted 1Alternatively, you may sort by targets:

Multiple Rows Bullet Graphs with Gaps and Exceedances sorted 2In order to quickly identify the main underperformers, you probably want to sort the view by gap in absolute numbers…

Multiple Rows Bullet Graphs with Gaps and Exceedances sorted 3… or – even better – by gap in percent of target:

Multiple Rows Bullet Graphs with Gaps and Exceedances sorted 4Finally, here is the same view sorted ascending instead of descending:

Multiple Rows Bullet Graphs with Gaps and Exceedances sorted 5An 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:

Bullet Graph Minimum Deviation 1To 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:

Bullet Graph Minimum Deviation 2If the gap is only 499 (i.e. less than 10% of target) or smaller, the span line and gap label will be suppressed:

Bullet Graph Minimum Deviation 3The 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.

Add a Comment

Your email address will not be published. Required fields are marked *