Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)®. Register today!

Bullet Chart

Step-by-step guide to build a bullet chart in Excel

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.

bullet chart guide

 

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

bullet chart 1

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.

bullet chart step 2

 

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.

bullet chart step 3

 

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.

bullet chart step 4

 

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%.

bullet chart step 5

 

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

bullet chart step 6

 

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).

bullet chart step 7

 

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.

bullet chart step 8

 

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.

bullet chart step 9

 

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.

bullet chart step 10

 

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!

Bullet Chart Template

Download the free Excel template now to advance your finance knowledge!

Bullet Chart Template Screenshot

 

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

Financial Analyst Certification

Become a certified Financial Modeling and Valuation Analyst (FMVA)® by completing CFI’s online financial modeling classes and training program!