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.
1Start 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.
2Select the entire table and plot the data on a stacked column chart.
The chart that’s created is initially plotted in the wrong direction.
3To fix the direction, click the chart and select the Switch Row/Column button, as shown in this figure.
Isn’t it pretty?
4Right-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.
5Right-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.
6Still 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.
7Still in the Format Data Series dialog box, expand the Border section and set the Border to No Line.
There will be no border.
8Go 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.
9Right-click the Value series and choose Format Data Series.
In the Format Data Series dialog box, click Secondary Axis.
10Still 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.
11Still 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.
12All 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.