Bullet Chart

What is a Bullet Chart?

The Bullet Chart is one of the best secret weapons of a world-class financial analyst.  The Bullet chart 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 chart.  The table must include the following data:

• Actual result (this 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 a bit by doing three 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 dashboard.

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 the 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!

Additional Resources

To keep developing your skills and become a world-class financial analyst we highly recommend these additional resources:

• Waterfall chart template
• Advanced Excel formulas guide
• Index Match Match
• Vlookup
• See all financial modeling resources
• See all Excel resources