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.
Components of Bullet Chart
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.
- First four values are for comparison range.
- Achievement value is for achievement bar.
- Target is for target marker.
Steps To Create a Bullet Chart in Excel
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.
Insert a Column Chart
To create a bullet chart we have to insert a 2-D cluster column chart & here are the steps.
- First of all, select your data & use a 2-D cluster column chart.
- You’ll get all six data values in your chart, like this.
- After that, we have to swap the data in the chart. Select your chart & 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 & create a target marker.
Make a Target Marker
We have 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 & it’s hard to select the target bar. So, here is the trick. Select your chart & from format options use this drop down to select target value.
- Now, go to design tab & click on change chart type.
- Change chart type for target value to line chart with marker & tick secondary axis for it.
- At this point, you have a small dot in your chart for the target.
- Select that dot & 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.
Next, we have to make some changes to create an achievement bar for your chart.
Create Achievement Bar
You have 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 & 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. You just have to add a color theme for comparison range.
Make 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.
- 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 & 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.
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.
Over to You
What do you think about bullet chart/
I would love to hear from you.
Tell me your views in the comment box.