Create a Bullet Graph for Your Excel Dashboards
A bullet graph, as seen in this figure, contains a single performance measure (such as YTD [year-to-date] revenue), compares that measure with a target, and displays it in the context of qualitative ranges, such as Poor, Fair, Good, and Very Good.
The following figure breaks down the three main parts of a bullet graph. The single bar represents the performance measure. The horizontal marker represents the comparative measure. And the background color banding represents the qualitative ranges.
Start with a data table that gives you all the data points you need to create the three main parts of the bullet graph.
This figure illustrates what that data table looks like. The first four values in the dataset (Poor, Fair, Good, and Very Good) make up the qualitative range. You don’t have to have four values — you can have as many or as few as you need. In this scenario, you want your qualitative range to span from 0 to 100%.
Therefore, the percentages (70%, 15%, 10%, and 5%) must add up to 100%. Again, this can be adjusted to suit your needs. The fifth value in this figure (Value) creates the performance bar. The sixth value (Target) makes the target marker.
Select the entire table and plot the data on a stacked column chart.
The chart that’s created is initially plotted in the wrong direction.
To fix the direction, click the chart and select the Switch Row/Column button, as shown in this figure.
Isn't it pretty?
Right-click the Target series and choose Change Series Chart Type.
Use the Change Chart Type dialog box to change the Target series to a Line with Markers and to place it on the secondary axis, as in this figure. After confirming your change, the Target series appears on the chart as a single dot.
Right-click the Target series again and choose Format Data Series to open that dialog box.
Click the Marker option, and adjust the marker to look like a dash, as shown in this figure.
Still in the Format Data Series dialog box, expand the Fill section and in the Solid Fill property.
Set the color of the marker to a noticeable color such as red.
Still in the Format Data Series dialog box, expand the Border section and set the Border to No Line.
There will be no border.
Go back to your chart and delete the new secondary axis that was added to the right of your chart, as shown in figure.
This is an important step to ensure the scale of the chart is correct for all data points.
Right-click the Value series and choose Format Data Series.
In the Format Data Series dialog box, click Secondary Axis.
Still in the Format Data Series dialog box, under Series Options, adjust the Gap Width property.
Adjust the Value series is slightly narrower than the other columns in the chart — between 205% and 225% is typically okay.
Still in the Format Data Series dialog box, click the Fill icon (the paint bucket), expand the Fill section.
Then select the Solid Fill option to set the color of the Value series to black.
All that’s left to do is change the color for each qualitative range to incrementally lighter hues.
At this point, your bullet graph is essentially done! You can apply whatever minor formatting adjustments to size and shape of the chart to make it look the way you want. The following figure shows your newly created bullet graph formatted with a legend and horizontal labels.