Bullet Chart

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

Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first module of each course. Start Free

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

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

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

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 the best types of graphs to illustrate results vs a goal.

Download CFI’s Free Bullet Chart Template

Complete the form below to download our free Bullet Chart template!
Bullet Chart Template Screenshot

Additional Resources

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

0 search results for ‘