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 a 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.
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.
- Target Marker: A marker to represent the target. In the above example, I have used 90% as a 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 and a little bit narrow than comparison range color bars.
- Comparison Range: A comparison range of a color theme to use at the background of the achievement bar. The main idea to use this range is to create a comparison for achievement, like stages.
Steps to Create a Bullet Chart in Excel
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.
- 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 are a little bit lengthy. But, once you understand the entire process you’ll be able to create it in seconds.
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.
- You’ll get all six data values in your chart, like this.
- After that, you need to swap the data in the chart. Select your chart and click on “Switch Row/Column”.
- Make sure to have maximum axis values equals to 100%.
- Now, you have a chart like below.
- Now, let’s get into the next step and create a target marker.
Make a Target Marker
Now, you need to create a target marker that 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.
- Now, go to design tab and click on change chart type.
- Change chart type for target value to line chart with marker and tick secondary axis for it.
- At this point, you have a small dot in your chart for the target.
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.
At this point, you have a chart like above and next, you need to make some changes to add an achievement bar.
Create Achievement Bar
You need to create an achievement bar with a solid color to represent the current achievement against the target. Use the below steps.
- Select the achievement bar like you have selected the target bar using drop-down in format options.
- Now, go to series options and select secondary axis.
- Change gap width to 500% for now.
- And, use a solid color for achievement bar as a fill (like black or dark blue).
Your bullet chart is almost ready now you just need to add a color theme for the comparison range.
Step-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 it. One thing which I forget to tell you is that once you have done with it, make sure to change the gap with of comparison range to 500% and for achievement bar to 220%.
Congratulations! your bullet chart is ready.
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 and 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.
This worked well for me! I found that I needed to set the gap to 500% for the “Achievement” series and 100% for the other series (the background).
I also used black for my target bar, which allows me to keep it smaller and still be visible when performance exceeds target.
Very nice!
Thanks!
Tom
It’s pretty good. but How to remove the gap b/n Y-Axis and comparison range bar, I am unable to do it.
Yes , I got it now.
1. When clicking on your sample file link I get a ‘page not found’ error message!!!
Is there anything I need to do?
2. How do I create a horizontal bullet chart?
Thanks,
That’s Great ..Thanks.. keep ing up
Thanks, that was helpful!
I’m not seeing where to set the maximum axis values…