Vertical Bullet Chart in Excel: Build One Step by Step

To create a vertical bullet chart in Excel, make a stacked column chart from your performance-range bands, place a transparent actual-value column chart over it, and add a target line. You will need a small table of numeric values and a little care aligning the two charts; the result shows actual performance against a target and qualitative ranges in one compact graphic.

The instructions below are for Microsoft Excel for Microsoft 365 on Windows. Excel does not have a built-in Bullet Chart chart type, so this method layers two standard charts and a line shape. It works well for a mostly static dashboard; if the chart’s dimensions or axis scale change, check the overlay and reposition the target line.

What You’ll Need

Excel for Microsoft 365 on Windows and a worksheet where you can place the chart.
One metric, its actual value, a target, and three non-overlapping performance bands that together cover the axis range.
A maximum scale for the metric. The example uses 100, with low performance from 0 to 60, the middle band from 60 to 85, actual performance of 72, and a target of 80.

Use the same units and scale for all values. If the metric is a percentage, for example, enter 72 for 72% when the chart axis runs from 0 to 100. Do not mix 0.72 with 80 or enter text such as “72%” as a text value.

Step-by-Step

Lay out the data. Enter the following table in an unused area of the worksheet. The band entries are segment sizes, not cumulative endpoints: the middle band is 25 because it covers 60 to 85, and the high band is 15 because it covers 85 to 100.

Metric Low band Middle band High band Actual Target

Service level 60 25 15 72 80

Keep the three band values adding up to the maximum scale: 60 + 25 + 15 = 100. The actual and target are separate values; do not add them to the band totals.
Create the background bands chart. Select the Metric and three band columns, including their headers. In this example, select A1:D2. Choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. The single stacked column should have three segments whose combined height represents the full scale.
Set the background chart’s scale and colors. Right-click the vertical value axis and choose Format Axis. Under Axis Options, set Minimum to 0 and Maximum to 100. Set the major unit to a useful interval, such as 20. If Excel has placed the axis at the top, use the axis formatting options to put it at the bottom. Select each band segment and use Format Data Series > Fill to choose distinct, subdued fills, such as light gray, medium gray, and darker gray. These bands are context, so avoid colors that compete with the actual bar.
Make the background chart easy to read. Give it a concise title, such as “Service level.” Keep the vertical axis labels if readers need to see the scale. Remove the legend if its labels duplicate the ranges or make the chart cluttered: select the legend and press Delete. Reduce the gap width of the stacked series if the background column is too narrow. Right-click a band, choose Format Data Series, and adjust Gap Width under Series Options. A lower gap width makes the column wider.
Create a separate chart for the actual value. Select the Metric and Actual columns, including headers; in the example, select A1:A2 and E1:E2. If the two selections are not adjacent, select the first range, then hold Ctrl while selecting the second. Choose Insert > Insert Column or Bar Chart > 2-D Clustered Column. This chart will supply the narrow foreground bar, while the first chart supplies the colored background.
Give the actual chart the same scale. Right-click its vertical axis, choose Format Axis, and set Minimum to 0 and Maximum to 100, matching the background chart exactly. If the scales differ, the top of the actual bar will show the wrong value relative to the bands. Set the actual series’ Gap Width to a larger number, such as 250% or 300%, so its column is narrower than the background column. Adjust the number until the bands remain visible on both sides of the actual bar.
Remove visual elements from the foreground chart. Delete its title, legend, category-axis labels, and vertical-axis labels. Select the chart area and choose Format Chart Area; set its fill to No fill and its border to No line. Do the same for the plot area using Format Plot Area. The chart’s transparent areas allow the background bands and labels to show through. Format the actual bar with one clear accent color, such as blue. Avoid transparency on the bar itself unless you specifically want the bands to show through it.
Overlay and align the charts. Drag the actual chart on top of the background chart. Resize it so the chart boundaries line up, then compare the plot areas: the actual column must be centered over the background column. Chart boundaries can match while plot areas do not, because margins differ. If the bar is off-center, adjust the foreground chart’s size or position until it aligns. Keep the actual chart’s plot area transparent, and make sure its category is the same single metric as the background chart.
Add the target marker. Choose Insert > Shapes and select a straight line. Draw a short horizontal line across the plot area at the target’s height. With a 0-to-100 axis and a target of 80, place it four-fifths of the way up the plot area from the bottom. Hold Shift while drawing to keep the line horizontal. Format it with a contrasting color and a visible line weight. Add a small text box such as “Target 80” nearby if the value is not otherwise clear. The line is a shape, not a data-linked marker, so it will not move automatically when the target or scale changes.
Check the chart against the numbers. In this example, the actual bar should reach 72, above the 60 boundary and below the target line at 80. The target should sit in the middle band, which runs from 60 to 85. Confirm that the stacked background reaches 100, both charts use a 0-to-100 axis, and the actual bar is centered. Add a small source note or unit label if a reader might not know what the scale represents.
Group and protect the layout. Select both charts and the target line, then use Shape Format > Group if Group is available for the selected objects. Grouping makes it easier to move the assembled graphic without separating its layers. If the objects are hard to select, use Home > Find & Select > Selection Pane to choose them. Consider placing the charts over a clear dashboard area and saving the workbook after confirming the line and bars are still aligned.

Metric	Low band	Middle band	High band	Actual	Target
Service level	60	25	15	72	80

On Excel for Mac, the chart types and basic formatting options are similar, but ribbon labels and the location of chart-format controls can vary by release. Use the chart’s Chart Design and Format tabs, or the Format pane, to set chart fills and axis bounds. Excel for the web can edit many chart properties, but chart layering and detailed formatting controls may be more limited; use the Windows desktop app if you cannot set transparent chart areas or reliably align the layers.

Common Mistakes

Using cumulative endpoints as band sizes. If the bands are 0–60, 60–85, and 85–100, enter 60, 25, and 15. Entering 60, 85, and 100 makes the stacked column total 245 and gives the zones incorrect heights.
Forgetting to match the axes. The foreground and background charts must have identical minimum and maximum bounds. Automatic scaling can make the actual appear too high or too low, even if both charts show the same values.
Leaving the foreground chart opaque. A white chart or plot-area fill hides the performance bands. Set both areas to No fill, not just the outer chart area.
Making the actual bar too wide. If it covers nearly the entire background column, readers cannot see the qualitative bands. Increase the foreground series’ Gap Width until the bands are visible around it.
Assuming the target shape updates with data. The line and optional label are manually positioned. Updating the target, chart size, or axis scale without moving the line can make the graphic misleading.

Tips

Use three meaningful ranges rather than many tiny bands. A common interpretation is below expectation, acceptable, and strong, but choose boundaries that fit the metric.
Keep band fills muted and reserve a saturated color for the actual value. Use a different line style or color for the target so it remains distinguishable in grayscale or for readers with color-vision differences.
Label units and the scale clearly. “72” is ambiguous without context; “72%” or “72 of 100” is more informative.
For several metrics, build one chart per metric using the same maximum scale and band definitions where comparison is intended. Keep widths and colors consistent, and verify each overlay separately.
If values change frequently, consider making the target a chart series in a carefully designed combo chart, or use a dashboard tool with native bullet charts. A manually drawn line is quick for a small report but requires upkeep.

Troubleshooting

The background bands appear as separate side-by-side columns. Recreate the background with 2-D Stacked Column, not Clustered Column. Check that the selected range contains one metric row and three band series.
The actual bar does not line up with the background. Confirm both charts use the same single category and identical axis bounds. Then check the plot-area alignment, not only the outer chart edges. Slight differences in chart margins can shift the column center; resize or reposition the foreground chart and inspect the result at the intended dashboard size.
The actual column is missing or has the wrong height. Check that the Actual cell contains a number, that the foreground chart uses the Actual column, and that its axis bounds are 0 and 100. If the cell is blank or stored as text, re-enter it as a number.
The target line is in the wrong place. Recalculate its position using the current axis bounds and plot-area height. For a 0-to-100 scale, 80 belongs at 80% of the distance from the bottom of the plot area, not 80% down from the top. Move the shape and check it against the axis labels.
The target line disappears behind a chart. Select the line through the Selection Pane and bring it to the front with Shape Format > Bring Forward or Bring to Front. Make sure it crosses the plot area rather than the chart title or margins.
The bands are hidden after overlaying. Recheck No fill on both the foreground chart area and plot area. If the bar is still too wide, increase its Gap Width. Also check that the background chart is behind the actual chart rather than covering it.

Frequently Asked Questions

Can Excel create a bullet chart with one click?
No. Excel for Microsoft 365 does not include a native Bullet Chart type. You can assemble one from standard charts, as above, or create a simpler visual with worksheet shapes.

Why use two charts instead of one?
The stacked column creates full-height qualitative bands, while the separate column shows actual performance from zero. Layering lets the narrower actual bar sit in front without adding its value to the bands and distorting their total.

Can I make the target line update automatically?
Not when it is an inserted shape. You must move it when the target, axis range, or plot-area size changes. A target represented as a chart series can update from cells, but combining and aligning that series with stacked bands takes more chart setup.

Can I build a bullet chart for values above 100?
Yes. Choose a maximum scale that covers the full intended range, change the stacked band sizes so they add to that maximum, and apply the same minimum and maximum to both charts. Reposition the target line for the new scale.

Will the chart update when I change the actual value?
The foreground column updates if its chart source refers to the cell you edit. The background bands update if their source values change. The manually drawn target line does not update or reposition automatically, so check it after changing the scale or target.

Can I print or export the chart?
Yes. Keep the layered objects together, check the chart in Print Preview, and confirm that the target line remains visible. If the objects shift or print separately, adjust the page layout and group the chart components before printing or exporting.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.