How to Create a Bullet Chart in Excel

Selecting a right kind of a chart is one of the toughest tasks.

While struggling with these kinds of problems, I've found a chart called “Bullet Chart”.

Bullet Chart is one of those advanced Excel charts which you should learn to get better at charting.

It’s a perfect chart to use when you need to present single point target Vs. Achievement.

According to Wikipedia:

A bullet graph is a variation of a bar graph developed by Stephen Few. Seemingly inspired by the traditional thermometer charts and progress bars found in many dashboards, the bullet graph serves as a replacement for dashboard gauges and meters.
ready to use bullet chart in excel

And in today’s post, I’m going to share with you how to make a bullet chart in Excel using simple steps.

So let’s get started.

Components

Mainly a bullet chart has three components.

how to create a bullet chart in excel components

1. Target Marker:

A marker to represent the target. In above example, I have used 90% as target. You can change the value as per your requirement.

2. Achievement Bar:

A single achievement bar to represent the actual value. This bar should have a solid color & little bit narrow than comparison range color bars.

3. Comparison Range:

A comparison range of a color theme to use at the background of achievement bar. The main idea to use this range is to create a comparison for achievement. Like stages.

Steps to Create

In the below table, you have data in three parts which you can use to create a bullet chart. You can download this file from here to follow along.

table to create a bullet chart in excel
  • First four values are for comparison range.
  • Achievement value is for achievement bar.
  • Target is for target marker.

As I mentioned above, we have three main components in a bullet chart. So, you need to split the entire process into parts.

The steps to create a bullet chart in excel is a little bit lengthy. But, once you understand the entire process you’ll able to create it in seconds.

1. Insert a Column Chart

To create a bullet chart you need to insert a 2-D cluster column chart and here are the steps for this.

  • First of all, select your data and insert a 2-D cluster column chart from insert tab.
go to insert tab insert 2d stack column to create a bullet chart in excel
  • You’ll get all six data values in your chart, like this.
insert a 2d cluster column chart to create a bullet chart in excel
  • After that, you need to swap the data in the chart. Select your chart and click on “Switch Row/Column”.
swap chart data to create a bullet chart in excel
  • Make sure to have maximum axis values equals to 100%.
  • Now, you have a chart like below.
change axis label to create a bullet chart in excel
  • Now, let’s get into the next step and create a target marker.

2. Make a Target Marker

Now, you need to create a target marker which will look like a small horizontal line.

Here are the steps.

  • First of all, you have to select your target bar. (But, the chart is totally messed-up right now and it’s hard to select the target bar.)
    • So, here is the trick: Select your chart and from format options use this drop down to select target value.
select target data bar to create a bullet chart in excel
  • Now, go to design tab and click on change chart type.
click on change chart type button create a bullet chart in excel
  • Change chart type for target value to line chart with marker and tick secondary axis for it.
change chart type from option for target data bar to create a bullet chart in excel
  • At this point, you have a small dot in your chart for the target.
change chart type of target data bar to create a bullet chart in excel

Now from here do the following things.

  • Select that dot and change the marker to a rectangular bar.
  • Change marker size to 20.
  • Use a solid color for fill (like black). 
  • No line for the border.
  • Delete the secondary axis.
change marker style to create a bullet chart in excel

At this point, you have a chart like above and next, you need to make some changes to add an achievement bar.

3. Create Achievement Bar

You need to create an achievement bar with a solid color to represent the current achievement against the target.

Use below steps.

  • Select the achievement bar like you have selected the target bar using drop-down in format options.
select achievement bar to create a bullet chart in excel
  • Now, go to series options and select secondary axis.
  • Change gap width to 500% for now.
make achievement bar to secondary axis to create a bullet chart in excel
  • And, use a solid color for achievement bar as a fill (Like black or dark blue).
after creating achievement bar to create a bullet chart in excel

Your bullet chart is almost ready now you just need to add a color theme for comparison range.

4. Make Comparison Range

For creating a comparison range you have to select a color and use four different shades of that.

All you have to do, select them one by one and apply a color. I have used four different shades of gray color here.

You can also add some formatting to your chart if you need.

One thing which I forget to tell you that once you have done with it, make sure change the gap with of comparison range to 500% and for achievement bar to 220%.

Congratulations! your bullet chart is ready.

ready to use bullet chart in excel

Additional Information

  • Make sure the width of your marker is more than the achievement bar. So that when you have more achievement than the target marker, it will show in right manner.
  • Always use solid colors for achievement bar & target marker.
  • Use different shades of single color for comparison range.
  • You can also use the same technique to create a horizontal bullet chart. You just have to replace column chart with bar chart.

Sample File

Download this sample file from here to learn more.

  • Squalle

    I’m not seeing where to set the maximum axis values…