Bullet charts in Excel

This type of chart was devised by Stephen Few. His specification is at

https://www.perceptualedge.com/articles/misc/Bullet_Graph_Design_Spec.pdf

It is intended as a replacement for the ‘speedometer’ style dashboard gauges. These started appearing in the 1990s but are now regarded as a poor design choice.

The bullet chart shows a single measure. It can show this relative to several comparators. Typically this is a target and ranges of low, medium and high





For example, a bullet chart could be used to show NHS performance against the national target (95%) for 4 hour waits in A&E. This can be shown against a back-drop of the range of performance by peer organisations





Excel does not currently provide a bullet chart as part of its standard chart gallery. These charts can be produced in Excel. But doing this requires ‘bending’ Excel slightly to make it do what we need.

There are numerous blog articles available giving instructions on how to do this. I think Jon Peltier has produced one of the best. I have borrowed many of his ideas. Most solutions use a combination chart and adapted error bars.

There are some questions to answer before we start

• Do we want a horizontal bullet chart (bar) or a vertical (column)? 
• Do we want a single bullet chart on its own or several together? 
• Do we want a legend on the chart? 

I think the horizontal version works best (although it is slightly more complicated to set up in Excel). The sections below show how to do this in Excel.

Single horizontal bullet chart (with or without legend)

This method mixes three bar data series and two scattergram series (which we need to position parts of error bars from).

You will need to set out the source data as follows:




Step One

Highlight the cells which cover the Low, Middle and High ranges (yellow in the screenshot – do not highlight the entire block of data).

Use this range to insert a Clustered Bar Chart.

If the result looks like this





You will need to Select Data… and Switch Row and Column to get the following:




Step Two

If you want a legend, add it now.

Format the fill of the three bars to reflect the desired grey scale.






Then adjust the bar properties to 100% overlap and 50% gap width





Reduce the height of the overall chart to condense the bar height





Step Three
We now need to add the two remaining data series. We add them one at a time. Because we are using a scattergram type for these series, we need two measures for each point. The 'secondary y axis' measure is used to control the vertical position of the marker. 50% (or 0.5) will allow us to make it central

Select the Measure range and the Secondary Y axis range together (hold control key) then Copy






Click on the chart to select it.

Then select Paste Special and set options as follows





The new series should appear added like this





Select the new series and change its chart type to Scatter (X Y)




Step Four

If you want the legend, you will need to change the labelling and adjust the colour of the fill

If you do not want a legend, do not bother with this step as we will be hiding the marker later

Step Five



Adjust the secondary Y axis properties to run from 0 to 100% (this forces the 50% to the middle) then delete it

Step Six


Add the final data series in similar fashion to Step Three. Highlight the range (yellow in the screenshot) and Paste Special into the Chart

Step Seven

If you want the legend, reformat the data series and adjust the label

Step Eight

Add error bars to Target data series

Delete the horizontal error bars

Format the vertical error bars

• End style No cap
• Error amount fixed value 0.3
• Bar colour
• Increase Bar width to 1.5pt or greater

Then highlight the Target data point and set marker to None

This removes the marker leaving the error bar behind (and leaves the Legend intact if you want this)

Step Nine

Add error bars to Measure data series

Delete the vertical error bars

Format the horizontal error bars

• Direction minus
• End style No cap
• Error amount percentage 100%
• Bar colour as required (red in this illustration)
• Increase Bar width to 5pt or greater

Highlight the data point and set marker to None

Step Ten

Format to suit

If you don’t want the legend you can omit steps four and seven

Excel: creating a movable hairline date marker on a time series chart

If you want to add a marker line to a one-off chart it is probably easiest to use Excel's drawing tools (Shapes).

This article looks at a more complex situation. We want the position of the marker line to move automatically based on selections in the dashboard.

For example we may use a line chart to show a time series. A hairline marker could then be used to highlight a date of interest. The position of the marker should then move in line with currently selected values or changes to the data.

This can be set up in Excel relatively easily and without having to resort to any VBA code. The method shown here creates the hairline from a column data series aligned to a secondary axis. I devised this method myself - rather than borrowing it from any books or websites - so this may be the first time it has been described anywhere. Although given the ingenuity of Excel users,  I quite expect to find that others will also have invented it, or something like it

The illustration is a generalized chunk of a dashboard. The idea is that the user selects a week of interest using a drop down list. The data displayed is automatically updated to match the chosen week. The position of the hairline marker on the line chart also  moves to indicate the position of the chosen week, And the date displayed above the hairline is also updated to give the correct detail

I am not going to explain here how to update the table of values in line with the selected week. You will be able to do this in various ways including  using VLOOKUP() and/or INDEX()/MATCH(). The details will have to be tailored to your individual situation, in particular where you are getting your data from and how you plan to refresh it.

Neither am I going to explain how to create a line chart with a 'shadow band' effect covering a range of older data - I have covered this is an earlier article
http://bi-dashboard-design.blogspot.com/2018/05/the-problem-of-too-many-lines-on-time.html

The focus here will be on how to create the movable hairline. The main features are:

• The hairline is simply a standard Excel column data series with the properties set to make it very narrow
• The chosen week is displayed by being the only week that is not hidden.
• Hide all the other weeks by making their value calculate to #N/A when they are not chosen
• Use the secondary vertical axis to ensure that the height of the hairline remains the same whatever happens to the values in the line series
• Set the hailrlne data label by pointing it at a cell with the date in

None of this is difficult


Alternatives?

Another method from elsewhere is to use a scatterplot with an error bar