What is a Bullet Chart in Excel?
Dashboards need to tell the story of the entire data in a single frame of the window (preferably). But many people struggle to fit in all the insights in a single page due to lack of optimum utilization of resources. If you see any dashboard you must have seen visualizations isn’t it? Yes, visualizations do consume a considerable amount of space in the worksheet area so most of the users struggle with it. Choosing the right kind of chart will add more value to your dashboard skills.
Performance gauging often showed in the dashboard through gauge chart like the below one.
Since this chart consumes most part of the worksheet area users may not involve so many other insights from the data. So here we have a replacement for this chart i.e. “Bullet Chart”. In this article, we will show you how to create a bullet chart in detail.
Excel Bullet Chart was invented by “Stephen Few” which helps us to analyze the performance against the set of standards.
For example when you collect the survey result from the public you might be expecting a satisfaction level of 90% which will be treated as target and after collecting opinions you may get the satisfaction level as 75%, so showing these numbers in bullet chart to gauge the performance.
How to Create Bullet Chart in Excel?
Below are examples of bullet chart in excel.
Bullet Chart Example #1
Assume you are collecting opinions about your product experiment from customers and you have set the below bands to your customers to given satisfaction scores.
From the satisfaction survey, you have targeted an average score of 5 but you have got 4 as the average score. So now we will show this in bullet chart to visualize this. Below are the details I have entered in the worksheet.
Follow the below steps to create a bullet chart.
Step 1: Select the data from A1 to B7 first then, insert the Stacked Column Chart.
Now we can see the below chart.
Step 2: Select the chart and “Switch Rows/Columns” under the design tab.
Now we have a chart like the below one where all the bars are combined together.
In this chart topmost bar (orange color) is the target.
Step 3: Select the Orange Color bar, right-click and choose “Change Series Chart Type”.
Step 4: Now in the below window scroll down and select the “Target” series check the box “Secondary Axis” and change the chart type to “Stacked Line with Marker”.
Now we can see the “orange dot” instead of a bar for the “Target” series.
Step 5: Now select the “Avg Score” bar, right-click and chose “Change Series Chart Type”.
As usual, we will see the Change Chart Type window. From this window for the “Avg Score” series check the “Secondary Axis” box.
Step 6: Click on Ok and it will come back to the chart and our chart looks like this now.
Step 7: Now select the blue colored area and press Ctrl + 1 as the shortcut key to open the format data series.
In this format data series make the “Gap Width” to 500%.
Step 8: Now select the target series “dot” and again press Ctrl + 1.
Select “Fill & Line” >>> Marker >>> Marker Option and make below changes.
Now our “dot” replace by “Marker”.
Step 9: Now select each band bars and change the color from light grey to dark grey starting from the top bar. And our bullet chart looks like this now.
Bullet Chart Example #2
Now we will see how to build a multi bullet chart in a single chart in excel. Below is the data I am going to use.
This table shows what the target for each product is and what is achieved. On the other hand, we have a range of values split into three categories i.e. “Bad”, “Good”, and “Very Good”.
For example for Product “A” target is 250 and the actual is 245. In the range it says if 150 is actual then it will be treated as “Bad”, if 220 is actual then it will be “Good” and if 250 is actual then it will be “Very Good”.
Ok, let’s create a bullet chart for this.
Step 1: Select data from D2 to F6 and insert the “Clustered Bar Chart”.
Now the chart looks like as shown below:
Step 2: Select a green colored bar and press Ctrl +1 and make the “Series Overlap” as 100% and “Gap Width” as 50%.
Step 3: Now right-click on the chart and choose “Select Data”.
Step 4: In the below window click on “Edit” at the Horizontal (Category) Axis Labels.
Step 5: Now choose Product Name and click on Ok.
Step 6: Now we can see Product Names horizontally.
In the same window select “Very Good” and move this to top using up arrow as shown below.
Arrange in this order “Very Good”, “Good”, and “Bad”.
And the chart looks like this.
Step 7: Now choose a blue-colored bar and press Ctrl + 1. Go to Fill and choose any of the dark fill colors.
Similarly, for remaining bars keep filling with light colors of the same blue and the chart should look like this now.
Step 8: Right-click on the blue bar chooses “Select Data”.
In the next window choose Target Values.
So the same for Actual as well.
Click on Ok and we will have a chart like this now.
Step 9: Right-click on the blue-colored bar and choose “Change Series Chart Type”.
Step 10: In the chart change widow chose the “X Y Scattered” Chart for “Actual”.
Repeat the same for the “Purple” bar as well.
Now we will have a chart like this now.
Step 11: Right-click on the purple dot and chose the “Select Data” option. In the next window select “Target” and choose the “Edit” option.
Step 12: In the next window for X Values choose values from B3 to B3 and for Y Values enter the numbers 10,30,50,70.
Similarly for Actual select C3 to C6 for X Values and for Y values enter 10,30,50,70.
Click on Ok, we will have a chart like this now.
Step 13: Click on Blue Dot >>> Design >>> Add Chart Element >>> Error Bars >>> Percentage.
Now chart look like this.
Under the Format tab choose “Target X Error Bars”.
Now on the format data series choose “Minus” and “No Cap”. For percentage enter 100.
Now make the line style as 8 Pt for width.
Now click on Purple Dot and repeat the same steps. While adding error bars follow below image instructions.
In the same format data series go to Line style and make below changes.
Now the chart looks like this.
Select the dot and make the marker as no line and our bullet chart is ready to rock. Wow!!!
Things to Remember
- The bullet chart is not a built-in excel chart.
- By adding various other charts and customization we need to create this one.
- Follow all the steps to create a neat bullet chart in excel, do not skip any steps.
This has been a guide to Bullet Chart in Excel. Here we provide step by step guide to creating a bullet chart in excel along with examples and a downloadable template. You may learn more about excel from the following articles –