How to Make a Bullet Chart in Excel (KPI Tracking Done Right)
Build a bullet chart in Excel step by step — bands, actual bar, and target marker — and see why bullets beat gauge charts for KPI dashboards.
How to Make a Bullet Chart in Excel (KPI Tracking Done Right)
Here’s the position up front: if you’re tracking a KPI against a target, a bullet chart beats a gauge chart, and it isn’t close. Gauges are the default because they look like dashboards are “supposed” to look — but they burn a quarter of your screen to communicate one number, and you can’t stack six of them without your report looking like a car. A bullet chart shows the same information — actual, target, and performance bands — in a strip the height of a text row, and ten of them line up in the space one gauge occupies.
The catch: Excel has no native bullet chart type. Not in Microsoft 365, not anywhere. Every bullet chart in Excel is a build — a stacked column wearing a disguise. The good news is the build is fifteen minutes the first time and five minutes forever after, and this guide walks every step. There’s a working file at the end so you can skip even that.
Anatomy of a Bullet Chart
Designed by dashboard specialist Stephen Few specifically to replace gauges, a bullet chart has three layers:
- Qualitative bands — background segments showing what counts as poor, fair, good, and excellent. These give the number context without a single word of commentary.
- The actual bar — a narrow, dark bar showing current performance, drawn over the bands.
- The target marker — a short perpendicular dash marking the goal.
One glance answers three questions: What’s the number? Is it good? Did we hit target? A gauge answers the same questions with roughly six times the ink and four times the pixels.
Step 1: Set Up the Data
We’ll build a customer-satisfaction bullet on a 0–100 scale. Lay the data out vertically:
| Customer Satisfaction | |
|---|---|
| Poor | 50 |
| Fair | 25 |
| Good | 15 |
| Excellent | 10 |
| Actual | 78 |
| Target | 85 |
Two things that most tutorials skip and most broken bullet charts get wrong:
- The bands must sum to your axis maximum. 50 + 25 + 15 + 10 = 100. The band values are segment sizes, not thresholds — “Fair” here means the zone from 50 to 75. If your bands sum to 87, your chart will quietly lie.
- Actual and Target are plotted values, not segments. They sit in the same column, but we’ll split them off the stack in the steps below.
Step 2: Insert a Stacked Column and Merge It
- Select the whole table and go to Insert > Charts > Insert Column or Bar Chart > Stacked Column.
- Excel plots six separate columns — not what we want. With the chart selected, go to Chart Design > Switch Row/Column.
Now everything stacks into one column: four band segments with Actual and Target piled on top. Ugly, but structurally correct. Next we peel Actual and Target off the stack.
Step 3: Turn the Target into a Marker
- Click the topmost segment (Target). Right-click > Change Series Chart Type.
- In the dialog, find the Target series, change its type to Stacked Line with Markers, and check Secondary Axis. Click OK. The segment becomes a floating point at 85.
- Click that point, then Format Data Series > Marker: choose the built-in dash type, set size to about 20, fill it red, remove the border.
You now have a horizontal dash floating at the target value — the classic bullet marker.
Step 4: Narrow the Actual Bar
- Click the Actual segment (currently still stacked on the bands). Right-click > Format Data Series > check Secondary Axis. It now draws from zero, overlapping the bands — correct, since 78 is a position on the scale, not a slice of it.
- Still in Format Data Series, set Gap Width to around 350%. The bar narrows so the bands stay visible behind it.
- Fill it dark gray or black. The actual bar is the star; it should be the darkest thing on the chart.
Step 5: Align the Axes and Clean Up
This step is mandatory, not cosmetic. You have two vertical axes, and if their scales differ, your chart is fiction:
- Set both axes to the same fixed bounds — minimum 0, maximum 100 (right-click each axis > Format Axis > Bounds).
- Delete the secondary axis (click it, press Delete). The series stay put; only the duplicate scale goes.
- Recolor the four bands as a light-to-dark run of gray. Not four different colors — the bands are context, not content.
- Delete gridlines and legend; add a title that states the KPI.
Done: gray context bands, a dark bar reaching 78, a red dash at 85. Anyone can read it in a second — satisfaction is in the “good” zone, seven points short of target.
One maintenance note: this build references specific cells, so it updates live when you change the data. If it ever stops doing that, the causes are the usual suspects — see Excel chart not updating? 7 fixes.
Scaling Up: Multi-KPI Bullet Charts
The build extends to multiple KPIs on one chart — just add a column per KPI in the data table before inserting the chart, and every step works the same. The one hard constraint: all KPIs must share the same scale. Satisfaction, on-time delivery, and first-call resolution can share a chart because they’re all 0–100%. Revenue in dollars cannot join them — build it its own bullet.
This is where the gauge comparison stops being a matter of taste. Three KPIs as gauges = three large widgets you scan one at a time. Three KPIs as bullets = three aligned strips your eye compares in a single pass, because the bars share a baseline. Comparison is the entire point of a dashboard, and bullets are built for it. If you’re assembling a full dashboard, our free KPI dashboard template puts twelve charts — bullets included — on one page with the wiring done.
To be fair to gauges, they have exactly one legitimate use: a single number on a wall display viewed from across a room, where the dial’s size is the feature. On a working dashboard viewed at a desk, that size is the bug.
And if you’re weighing bullets against simpler options — a plain bar with a target line, or conditional-formatting data bars in cells — the honest trade-off is that those are faster to build and fine for internal working files; the bullet earns its extra build time when the report goes to an audience that needs the good/bad context bands. Our chart decision guide covers where each option fits.
FAQ
Does Excel have a built-in bullet chart? No — no version of Excel includes a native bullet chart type. Every method is a construction from stacked columns (this guide) or scatter charts. Some paid add-ins generate them in a click, but for a chart this quick to build, that’s a solution to a problem you don’t have.
Can I make a horizontal bullet chart? Yes — start from a Stacked Bar instead of a Stacked Column. The logic is identical, but the target-marker step is fussier because line markers don’t rotate; most horizontal builds use an error bar as the target dash instead. Build the vertical version first.
How do I show performance above 100% of the scale? Extend the axis maximum (say, 120) and add a fifth band for the overage zone — remembering that all bands must now sum to 120.
What should the bands be based on? Real thresholds, not thirds of the axis. If 90% satisfaction is your industry’s “excellent,” the excellent band starts at 90. Bands drawn from actual standards are information; bands drawn by dividing the axis evenly are decoration.
Download the Working File
The free workbook contains the finished single-KPI bullet chart with live data cells — change Actual to 92 and watch the bar cross the target — plus a three-KPI version ready to relabel for your own metrics.