30 Advanced Excel Tips for 2026

Excel Champs / Excel Charts / Bullet Chart in Excel
By Puneet Gogia (Microsoft MVP)

How to Create a Bullet Chart in Excel

List your data in one column: the background bands as widths rather than running totals (for example Poor 60%, Fair 20%, Good 20%), then Actual (75%) and Target (90%). Select it, go to Insert → Insert Column or Bar Chart → Stacked Column, then right-click the chart, choose Select Data and click Switch Row/Column so each row becomes its own series in a single column.

Right-click the chart and choose Change Chart Type → Combo. Keep the three bands as Stacked Column, set Actual to Clustered Column and Target to Line with Markers, and tick Secondary Axis for both. Actual now stands in front of the bands, and Target shows as a single dot.

Give both value axes the same fixed scale: right-click each axis, choose Format Axis, and type 0 in Bounds → Minimum and 1 in Bounds → Maximum (1 is 100% on a percentage axis). Then hide the secondary axis with Labels → Label Position → None. Left on Auto, the two axes scale separately and Actual and Target no longer line up with the bands.

Shape the parts. Shade the bands in one colour, darkest for the poorest band, with a Gap Width of about 100%; give Actual a solid dark fill and a Gap Width of about 400% so it is narrower than the bands. Select the Target dot and, under Format Data Series → Fill & Line → Marker → Marker Options, choose Built-in, the dash type and a size of 20 or more, so it is wider than the Actual bar.

For a horizontal bullet chart, swapping the column chart for a bar chart is not enough: a line series in a combo with bars still plots against a vertical value axis, so the target marker no longer lines up with the bars. Plot the target as an XY scatter point instead, with the target as X and a fixed Y such as 0.5, and match its horizontal axis bounds to the bars’ value axis.

Selecting the right kind of chart is one of the toughest tasks. While struggling with these kinds of problems, I’ve found a chart called “Bullet Chart”.

Bullet Chart is one of those advanced Excel charts which you should learn to get better at charting. It’s a perfect chart to use when you need to present a single point target Vs. Achievement.

According to Wikipedia:

A bullet graph is a variation of a bar graph developed by Stephen Few. Seemingly inspired by the traditional thermometer charts and progress bars found in many dashboards, the bullet graph serves as a replacement for dashboard gauges and meters.

ready to use bullet chart in excel

And in today’s post, I’m going to share with you how to make a bullet chart in Excel using simple steps.

So let’s get started.

Components of a Bullet Chart

Mainly a bullet chart has three components.

  1. Target Marker: A marker to represent the target. In the above example, I have used 90% as a target. You can change the value as per your requirement.
  2. Achievement Bar: A single achievement bar to represent the actual value. This bar should have a solid color and be a little narrower than the comparison range bars.
  3. Comparison Range: A comparison range of a color theme to use at the background of the achievement bar. The main idea of this range is to create a comparison for the achievement, like stages.
how to create a bullet chart in excel components

Steps to Create a Bullet Chart in Excel

In the below table, you have data in three parts which you can use to create a bullet chart. You can download this file from here to follow along.

table to create a bullet chart in excel
  1. The first four values are for the comparison range.
  2. The achievement value is for the achievement bar.
  3. The target is for the target marker.

As I mentioned above, we have three main components in a bullet chart. So, you need to split the entire process into parts. The steps to create a bullet chart in Excel are a little bit lengthy. But once you understand the entire process, you’ll be able to create it in seconds.

Insert a Column Chart

To create a bullet chart, you need to insert a 2-D stacked column chart, and here are the steps for this.

  1. First of all, select your data and insert a 2-D Stacked Column chart from the Insert tab.
    go to insert tab insert 2d stack column to create a bullet chart in excel
  2. You’ll get all six data values in your chart, like this.
    insert a 2d cluster column chart to create a bullet chart in excel
  3. After that, you need to swap the data in the chart. Select your chart and click on “Switch Row/Column”.
    swap chart data to create a bullet chart in excel
  4. Make sure the maximum value of the vertical axis is 100% (the four comparison values add up to 100%).
  5. Now, you have a chart like below.
    change axis label to create a bullet chart in excel
  6. Now, let’s get into the next step and create a target marker.

Make a Target Marker

Now, you need to create a target marker that will look like a small horizontal line. Here are the steps.

  1. First of all, you have to select your target bar. (But the chart is totally messed up right now, and it’s hard to select the target bar.)

Here is the trick: Select your chart, and from the Format tab, use this drop-down (Chart Elements) to select the target value.
select target data bar to create a bullet chart in excel

  • Now, go to the Chart Design tab and click on Change Chart Type.
    click on change chart type button create a bullet chart in excel
  • Change the chart type for the target value to Line with Markers and tick Secondary Axis for it.
    change chart type from option for target data bar to create a bullet chart in excel
  • At this point, you have a small dot in your chart for the target.
    change chart type of target data bar to create a bullet chart in excel

Now from here do the following things.

  1. Select that dot and change the marker to a rectangular bar.
  2. Change the marker size to 20.
  3. Use a solid color for fill (like black).
  4. No line for the border.
  5. Delete the secondary axis.
change marker style to create a bullet chart in excel

At this point, you have a chart like the one above, and next, you need to make some changes to add an achievement bar.

Create Achievement Bar

You need to create an achievement bar with a solid color to represent the current achievement against the target. Use the below steps.

  • Select the achievement bar like you selected the target bar, using the drop-down on the Format tab.
    select achievement bar to create a bullet chart in excel
  • Now, go to Series Options and select Secondary Axis.
  • Change the gap width to 500% for now.
    make achievement bar to secondary axis to create a bullet chart in excel
  • And use a solid color for the achievement bar as a fill (like black or dark blue).
    after creating achievement bar to create a bullet chart in excel

Your bullet chart is almost ready; now you just need to add a color theme for the comparison range.

Step-4. Make Comparison Range

For creating a comparison range, you have to select a color and use four different shades of it. All you have to do is select them one by one and 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 it. One thing I forgot to tell you: once you are done with it, set the gap widths so that the achievement bar is narrower than the comparison range. The achievement bar (on the secondary axis) needs a bigger gap width than the comparison range, for example, 500% for the achievement bar and 220% for the comparison range.

Congratulations! Your bullet chart is ready.

ready to use bullet chart in excel

Additional Information

  1. Make sure the width of your marker is more than the achievement bar, so that when the achievement is more than the target, the chart still shows it in the right manner.
  2. Always use solid colors for the achievement bar and the target marker.
  3. Use different shades of a single color for the comparison range.
  4. You can also use the same technique to create a horizontal bullet chart. You just have to replace the column chart with a bar chart.

Bullet Chart for Multiple KPIs

You can show several KPIs side by side with the same steps. Put each KPI in its own column (with the same six rows: four comparison values, achievement and target), select the whole table, and insert the stacked column chart. Each KPI becomes one bullet, and the formatting you apply to a series applies to all the bullets at once.

If your KPIs are in different units (like sales in dollars and satisfaction in %), convert each one to a percentage of its own target first, so all the bullets share one axis.

Common Problems with a Bullet Chart

  • The achievement bar doesn’t line up with the scale: after you move series to the secondary axis, set the secondary axis to the same minimum (0) and maximum (100%) as the primary axis before you delete or hide it.
  • The target marker is hidden: the marker sits behind the bars when it’s on the primary axis, so keep it as a line with markers on the secondary axis.
  • The comparison bands are side by side: you have inserted a clustered column chart; the bands only stack on top of each other in a stacked column chart.

Sample File

Download this sample file from here to learn more.

Frequently Asked Questions

Does Excel have a built-in bullet chart?

No. There’s no bullet chart type in the Insert Chart list, so you build it from a stacked column (or bar) chart with a target marker, as shown above.

How do I make a horizontal bullet chart?

Use a stacked bar chart instead of a stacked column chart. For the target, a line chart doesn’t work on a bar chart, so use an XY scatter series with a vertical error bar as the marker.

How do I update the chart with new values?

Just change the values in the data table. The chart is linked to those cells, so the bands, the bar and the target marker move as soon as you change them.

8 thoughts on “How to Create a Bullet Chart in Excel”

  1. This worked well for me! I found that I needed to set the gap to 500% for the “Achievement” series and 100% for the other series (the background).

    I also used black for my target bar, which allows me to keep it smaller and still be visible when performance exceeds target.

    Very nice!

    Thanks!

    Tom

    Reply
  2. It’s pretty good. but How to remove the gap b/n Y-Axis and comparison range bar, I am unable to do it.

    Reply
  3. 1. When clicking on your sample file link I get a ‘page not found’ error message!!!
    Is there anything I need to do?
    2. How do I create a horizontal bullet chart?

    Reply
  4. I’m not seeing where to set the maximum axis values…

    Reply

Leave a Comment