How to Create Bullet Chart in Excel? (with Examples)
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.
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 in Excel.
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 chart looks like this now.
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”.
- Step 9: In the next window choose Target Values.
- Step 10: So the same for Actual as well.
- Step 11: Click on Ok and we will have a chart like this now.
- Step 12: Right-click on the blue-colored bar and choose “Change Series Chart Type”.
- Step 13: In the chart change widow chose the “X Y Scattered” Chart for “Actual”.
- Step 14: Repeat the same for the “Purple” bar as well.
Now we will have a chart like this now.
- Step 15: Right-click on the purple dot and chose the “Select Data” option. In the next window select “Target” and choose the “Edit” option.
- Step 16: In the next window for X Values choose values from B3 to B3 and for Y Values enter the numbers 10,30,50,70.
- Step 17: Similarly for Actual select C3 to C6 for X Values and for Y values enter 10,30,50,70.
- Step 18: Click on Ok, we will have a chart like this now.
- Step 19: Click on Blue Dot >>> Design >>> Add Chart Element >>> Error Bars >>> Percentage.
Now the chart looks like this.
- Step 20: Under the Format tab chooses “Target X Error Bars”.
- Step 21: Now on the format data series choose “Minus” and “No Cap”. For percentage entering 100.
- Step 22: Now make the line style as 8 Pt for width.
- Step 23: Now click on Purple Dot and repeat the same steps. While adding error bars follow below image instructions.
Step 24: In the same format data series go to Line style and make below changes.
Now the chart looks like this.
- Step 25: 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 –