Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.
If you are struggling while selecting a chart for a single data point, you can use a bullet chart. Or, if you don’t like using gauge chart, you can replace it will a bullet chart.
So today, I’ll show you a step by step process to create a bullet chart in excel. Along with that, I’ll explain each step in detail.
In the end of this post, you’ll be able to quickly create a bullet chart.
But let me tell you something about bullet chart first.
Mainly a bullet chart has three components.
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.
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.
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.
I have used above table to create a bullet chart. In this table, we have data in three parts as we have in our bullet chart.
As I mentioned above, we have three main components in a bullet chart.
So, I have split the entire process into following 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.
I’m using the same table here which I have mentioned above. You can download this file from here to follow along.
So let’s get started.
To create a bullet chart we have to insert a 2-D cluster column chart & here are the steps.
Now, let’s get into the next step & create a target marker.
We have to create a target marker which will look like a small horizontal line.
Here are the steps.
Next, we have to make some changes to create an achievement bar for your chart.
You have 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. You just have to add a color theme for comparison range.
For creating a comparison range you have to select a color & use four different shades of that.
All you have to do, select them one by one & 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% & for achievement bar to 220%.
Congratulations your bullet chart is ready.
I hope you have found bullet chart useful for your dashboards.
The best thing about bullet chart is that it’ll cover less space in your dashboard than a gauge chart.
What do you think about bullet chart/
I would love to hear from you.
Tell me your views in the comment box.