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.
- 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.
Download this sample file from here to learn more.