Bullet Chart (Actual vs Target)

Excel Formulas › Charts

All versionsStacked bar

A bullet chart packs actual, target, and qualitative bands into one compact bar — the dashboard-designer’s favorite. Build it from a stacked bar plus a target marker, driven by a few helper cells.

Quick formula: helper columns feed a stacked horizontal bar:
Poor | Fair | Good (bands) + Actual (overlaid bar) + Target (marker)
Stack the band segments as a faint background, overlay the actual as a thin dark bar, and add the target as a marker line.

Functions used (tap for the full reference guide):

SUM

The example

Helper layout for a bullet chart.

AB
1SeriesValue
2Poor band60
3Fair band20
4Good band20
5Actual75
6Target90

The formula

The pieces of a bullet chart:

Bands: 60 / 20 / 20 (stacked, light→dark grey) Actual: 75 (thin bar overlaid) Target: 90 (marker) // one compact KPI bar

How it works

Layer three elements on one bar:

  1. Lay out helper cells: the band sizes (poor/fair/good), the actual value, and the target.
  2. Make a stacked horizontal bar from the bands in light-to-dark grey — the qualitative background.
  3. Add the actual as a second, thinner series overlaid in a dark color.
  4. Add the target as a marker (a scatter point or error bar) to show the goal line.

Excel has no built-in bullet chart — this stacked-bar technique is the standard workaround. Build one, then copy the chart and just re-point each copy at a different row for a tidy column of bullets.

Try it: interactive demo

Live demo

Set actual and target (bands fixed 60/80/100).

Variations

Vertical bullet

Use a stacked column instead of bar.

Target as error bar

A horizontal error bar makes a clean marker line.

% of target label

Data label:

=actual/target

Pitfalls & errors

Band sizes are increments. A stacked bar adds segments, so enter each band’s width (e.g. 60, then 20, then 20), not cumulative totals.

Overlap the actual. Set series overlap to 100% so the actual bar sits on the bands, not beside them.

No native bullet type. It’s always a built-up combo — save it as a template to reuse.

Practice workbook

📊
Download the free Bullet Chart (Actual vs Target) practice workbook
A bullet-chart helper layout with a real stacked bar chart, the vertical and %-of-target variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a bullet chart in Excel?
Excel has no native bullet chart. Build a stacked horizontal bar from the qualitative bands, overlay the actual as a thin bar (100% overlap), and add the target as a marker or error bar.
Do I enter cumulative or incremental band sizes?
Incremental. A stacked bar adds segments, so enter each band's width (e.g. 60, 20, 20), not running totals.
How do I show the target line?
Add the target as a scatter point or a horizontal error bar overlaid on the bar, which renders as a clean marker line.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Percent of goal gauge · KPI card · Combo chart

Function references: SUM