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.
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.
#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
- 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.
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.
- 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.
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.
- 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.
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.
- 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 comparison range.
Step-4. Make Comparison Range
- 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.
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.