Bullet Chart in Excel

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 a target, and after collecting opinions, you may get the satisfaction level as 75%, so showing these numbers in bullet chart to gauge the performance.

Bullet-Chart-in-Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Bullet Chart in Excel (wallstreetmojo.com)

You can download this Bullet Chart Excel Template here – Bullet Chart Excel Template

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.

bullet chart Example 1

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 a bullet chart to visualize this. Below are the details I have entered in the worksheet.

bullet chart Example 1-1

Follow the below steps to create a bullet chart.

  1. Select the data from A1 to B7 first, then insert the Stacked Column Chart in ExcelStacked Column Chart In ExcelA stacked column chart in Excel is a column chart in which multiple series of data representations of various categories are stacked over each other. The stacked series are vertical, and comparing multiple data series is easy, but as the number of data series increases, so does the complexity of representation.read more.

    bullet chart Example 1-2
    Now we can see the below chart.

    bullet chart Example 1-3.png

  2. Select the chart and “Switch Rows/Columns” under the design tab.

    bullet chart Example 1-4
    Now we have a chart like the below one where all the bars are combined together.

    bullet chart Example 1-5
    In this chart, the topmost bar (orange color) is the target.

  3. Select the Orange Color bar, right-click, and choose “Change Series Chart Type.”

    bullet chart Example 1-6

  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.”

    bullet chart Example 1-7
    Now we can see the “orange dot” instead of a bar for the “Target” series.

    bullet chart Example 1-8

  5. Now select the “Avg Score” bar, right-click, and chose “Change Series Chart Type.”

    bullet chart Example 1-10
    As usual, we will see the Change Chart Type window. From this window for the “Avg Score” series, check the “Secondary Axis” box.

    bullet chart Example 1-11.png

  6. Click on, Ok, and it will come back to the chart, and our chart looks like this now.

    bullet chart Example 1-12

  7. Now select the blue colored area and press Ctrl + 1 as the shortcut key to open the format data series.

    In this format, the data series make the “Gap Width” to 500%.


    bullet chart Example 1-13

  8. Now select the target series “dot” and again press Ctrl + 1.

    Select “Fill & Line” >>> Marker >>> Marker Option and make below changes.


    bullet chart Example 1-14
    Now our “dot” replace by “Marker.”

  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.

    bullet chart Example 1-15

Note: For band bars, you can choose a blue color as well. If need be, you can change the target marker color and Avg Score bar color as per your convenience.

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.

bullet chart Example 2

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,” the 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.

bullet chart Example 2-1

Now the chart looks like as shown below:

bullet chart Example 2-2
  • Step 2: Select a green colored bar and press Ctrl +1 and make the “Series Overlap” as 100% and “Gap Width” as 50%.
bullet chart Example 2-3
  • Step 3: Now right-click on the chart and choose “Select Data.”
bullet chart Example 2-4
  • Step 4: In the below window, click on “Edit” at the Horizontal (Category) Axis Labels.
bullet chart Example 2-5
  • Step 5: Now choose Product Name and click on Ok.
bullet chart Example 2-6
  • Step 6: Now, we can see Product Names horizontally.
bullet chart Example 2-7

In the same window, select “Very Good” and move this to top using up arrow, as shown below.

bullet chart Example 2-8

Arrange in this order “Very Good,” “Good,” and “Bad.”

And the chart looks like this.

bullet chart Example 2-9
  • Step 7: Now, choose a blue-colored bar and press Ctrl + 1. Go to Fill and choose any of the dark fill colors.
bullet chart Example 2-9.png

Similarly, for the remaining bars, keep filling with light colors of the same blue, and the chart should look like this now.

bullet chart Example 2-11
  • Step 8: Right-click on the blue bar, chooses “Select Data.”
bullet chart Example 2-12
  • Step 9: In the next window, choose Target Values.
Target Value Example 2-13
  • Step 10: So the same for Actual as well.
Actual series Example 2-14
  • Step 11: Click on, Ok, and we will have a chart like this now.
bullet chart Example 2-15
  • Step 12: Right-click on the blue-colored bar and choose “Change Series Chart Type.”
change series Example 2-16
  • Step 13: In the chart change widow chose the “X Y Scattered” Chart for “Actual.”
Actual Scatter Example 2-17
  • Step 14: Repeat the same for the “Purple” bar as well.
Change chart Example 2-18

Now we will have a chart like this now.

chart Example 2-19
  • 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.
Select data Example 2-20
  • 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.
Target Series Example 2-21
  • Step 17: Similarly for Actual select C3 to C6 for X Values and for Y values enter 10,30,50,70.
Actual Series Example 2-22
  • Step 18: Click on, Ok, we will have a chart like this now.
chart Example 2-23
  • Step 19: Click on Blue Dot >>> Design >>> Add Chart Element >>> Error Bars >>> Percentage.
Chart Design Example 2-24

Now the chart looks like this.

Error Bar Example 2-25
Format series Example 2-27
  • Step 21: Now, on the format data series, choose “Minus” and “No Cap.” For percentage entering 100.
Error Bar Example 2-30
  • Step 22: Now make the line style as 8 Pt for width.
Error Bar Example 2-31
  • Step 23: Now click on Purple Dot and repeat the same steps. While adding error bars, follow the below image instructions.
Error Bar Example 2-28

Step 24: In the same format, data series, go to Line style, and make the below changes.

Error Bar Example 2-29

Now the chart looks like this.

BC Example 2-33
  • Step 25: Select the dot and make the marker as no line and our bullet chart is ready to rock. Wow!!!
bullet chart Example 2-32

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>