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 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.
Mainly a bullet chart has three components.
A marker to represent the target. In above example, I have used 90% as target. You can change the value as per your requirement.
A single achievement bar to represent the actual value. This bar should have a solid color & little bit narrow than comparison range color bars.
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.
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.
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.
To create a bullet chart you need to insert a 2-D cluster column chart and here are the steps for this.
Now, you need to create a target marker which will look like a small horizontal line.
Here are the steps.
Now from here do the following things.
At this point, you have a chart like above and next, you need to make some changes to add an achievement bar.
You need to create an achievement bar with a solid color to represent the current achievement against the target.
Use below steps.
Your bullet chart is almost ready now you just need to add a color theme for 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.
One thing which I forget to tell you that once you have done with it, make sure change the gap with of comparison range to 500% and for achievement bar to 220%.
Congratulations! your bullet chart is ready.
Download this sample file from here to learn more.