What is a Bullet Chart?
The Bullet Chart is one of the best secret weapons of a world-class financial analyst. The Bullet graph can be used as an alternative to the gauge chart and is excellent for use in dashboards as well as comparing actual results to forecast. It is one of the most effective types of graphs.
This guide will show you exactly how to build a bullet chart in Excel from scratch.
Steps to build a Bullet Chart (10 step guide)
Step 1 – Input chart data
The first step is to make a table with the information you want to include in the bullet graph. The table must include the following data:
- Actual result (the will be the “bullet” in the center)
- Level 1 threshold
- Level 2 threshold
- Level 3 threshold
That’s it! If you use our free bullet chart template, the rest of the numbers are calculated for you automatically using formulas.
Take our Free Excel crash course if you want to become a master of Excel.
Step 2 – Insert Stacked Bar/Column Chart
Select the data in the table and then on the Insert tab of the Excel Ribbon choose Stacked Bar (or Stacked Column) to insert a horizontal (vertical) bullet graph.
Step 3 – Switch rows and columns if necessary
You may need to switch the rows and columns if Excel is not properly detecting the right format. To do this, select the chart, then on the Design tab of the Ribbon click the button that says “Switch Row/Column”. Ensure your graph looks the same as ours here.
Step 4 – Delete gridlines and legend
Click on the gridlines of the bullet chart and press the delete key. Next, click on the legend and delete that too. This will make the graph much cleaner.
Step 5 – Set “gap width” to 0%
In this step, the bullet graph really starts to take shape. In order to have the “bullet” appear in the center we need to set the series gap width to zero. In order to do this, right click on any series and click “format data series”. From there, make sure the series overlap is 100% and the gap width is 0%.
Step 6 – Format the bullet chart area
In this step, we can clean up the bullet chart bit by doing thee easy things:
- Remove axis labels – click on the axis labels and press delete
- Remove border – right click on the chart, select “format chart area”, select the paint can then select no border
- Remove title – select the title and click delete
Step 7 – Recolor the bullet chart
Click on the series with the bullet and then click again on just the bullet data point itself. This will allow you to change the color of only the bullet. Choose a strong color, like dark blue in our example.
Next, select the two data points on either side of the bullet and recolor them a light gray. After that, select each of the series on top and recolor them different shades of gray (or some other light color).
Step 8 – Add data label (and remove others)
Click on the chart series that contains the bullet, right click on it, and then choose “add data labels”. Next click the data label, right click, and format data labels. At this point you can set it to be a percent, currency, or whatever format you want.
Step 9 – Add tick marks (optional)
You may want to add tick marks to make the graph more readable. If you want tick marks, like in the example we created, click on the axis, then right click, format axis, and choose major, minor, or both levels of tick marks.
Step 10 – Bullet chart finishing touches
As the last step you may want to do some final formatting. For example, change the size of the bullet graph, alter the thresholds/levels, modify the major units/tick markets, and final color changes, and make sure it will look great in a presentation or dash board.
Adding additional elements of the bullet chart
There are some additional elements you may wish to add, such as a single data point for the “target” or an affordance that highlights the gap between actual and target results (variance analysis).
There are some complicated methods of adding this to the chart in Excel, but it’s generally easier to add this information.
Most common uses of bullet charts
Bullet charts are most commonly used in dashboards and executive presentations comparing actual results to a forecast, targets, or some goal. They are great for illustrating progress, relative to some criteria for evaluation or measurement of success. Bullet charts are one of best types of graphs to illustrate results vs a goal.
Download the Free Template
Enter your name and email in the form below and download the free template now!
To keep developing your skills and become a world-class financial analyst we highly recommend these additional resources: