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

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 of a Bullet Chart

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.

4 Simple Steps to Create a Bullet Chart in Excel

table to create a bullet chart in excel
  1. First four values are for comparison range.
  2. Achievement value is for achievement bar.
  3. 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.

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

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

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.

  1. Select that dot and change the marker to a rectangular bar.
  2. Change marker size to 20.
  3. Use a solid color for fill (like black).
  4. No line for the border.
  5. 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.

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

Step-4. Make Comparison Range

ready to use bullet chart in excel

Additional Information

  1. 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.
  2. Always use solid colors for achievement bar and target marker.
  3. Use different shades of single color for comparison range.
  4. 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

Content Protection by DMCA.com
2018-11-16T06:13:35+00:00

4 Comments

  1. Bojan 21 Nov, 18 at 4:21 pm - Reply

    Thanks,

  2. Amila Perera 21 Nov, 18 at 3:59 am - Reply

    That’s Great ..Thanks.. keep ing up

  3. Kiran 6 Nov, 18 at 10:44 am - Reply

    Thanks, that was helpful!

  4. Squalle 21 Feb, 17 at 3:03 am - Reply

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

Leave A Comment