how to create a bullet chart in excel step by step guideIf you are struggling while selecting a chart for a single data point, you can use a bullet chart. Or, if you don’t like using gauge chart, you can replace it will a bullet chart.

So today, I’ll show you a step by step process to create a bullet chart in excel. Along with that, I’ll explain each step in detail.

In the end of this post, you’ll be able to quickly create a bullet chart.

But let me tell you something about bullet chart first.

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

Components of Bullet Chart

Mainly a bullet chart has three components.

how-to-create-a-bullet-chart-in-excel-components

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.

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.

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.

table to create a bullet chart in excel

I have used above table to create a bullet chart. In this table, we have data in three parts as we have in our bullet chart.

  1. First four values are for comparison range.
  2. Achievement value is for achievement bar.
  3. Target is for target marker.

Steps To Create a Bullet Chart in Excel

As I mentioned above, we have three main components in a bullet chart.

So, I have split the entire process into following 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.

I’m using the same table here which I have mentioned above. You can download this file from here to follow along.

So let’s get started.

Insert a Column Chart

To create a bullet chart we have to insert a 2-D cluster column chart & here are the steps.

  • First of all, select your data & use a 2-D cluster column chart.

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, we have to swap the data in the chart. Select your chart & 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 & create a target marker.

Make a Target Marker

We have 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 & it’s hard to select the target bar. So, here is the trick. Select your chart & 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 & 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 & 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

  • Select that dot & 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

Next, we have to make some changes to create an achievement bar for your chart.

Create Achievement Bar

You have 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 & 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).

Your bullet chart is almost ready. You just have to add a color theme for comparison range.

after creating achievement bar to create a bullet chart in excel

Make Comparison Range

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

All you have to do, select them one by one & 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% & 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 sample file to learn to create a bullet chart in excel

Last Words

I hope you have found bullet chart useful for your dashboards.

The best thing about bullet chart is that it’ll cover less space in your dashboard than a gauge chart.

Over to You

What do you think about bullet chart/

I would love to hear from you.

Tell me your views in the comment box.

What’s Next?

If you want to learn more about single data point chart, you can learn to create a waffle chart in excel or you can also use a thermometer chart in excel.



  • Squalle

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