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.