Bullet Graph Formula: Stacked Qualitative Range Deltas, Actual Bar & Target Variance

Replace bloated radial speedometer gauges with Stephen Few’s linear Bullet Graph!

Designed by dashboard pioneer Stephen Few (2006, Information Dashboard Design) to pack five times more information into a single table-row footprint than radial gauges, a Bullet Graph layers a narrow dark Actual Performance Bar and a perpendicular Comparative Target Marker over three stacked grayscale background bands (Poor [0, Q_1], Satisfactory [Q_1, Q_2], Good [Q_2, Q_3])!

The #1 mistake when building stacked qualitative background bands in a spreadsheet chart is plotting cumulative thresholds [60, 85, 100] directly — which stacks them to 60 + 85 + 100 = 245! Instead, convert cumulative thresholds into non-negative Incremental Band Deltas: Band_1 = Q_1, Band_2 = Q_2 - Q_1, and Band_3 = Q_3 - Q_2 so their stacked sum equals Q_3 identically!

Incremental Qualitative Band Widths (Satisfactory = Q_2 - Q_1, Good = Q_3 - Q_2)

Validates strictly increasing thresholds 0 < Q_1 < Q_2 < Q_3 in B2:B4 and returns the incremental Satisfactory band width Q_2 - Q_1.

=IF(OR(B2 <= 0, B3 <= B2, B4 <= B3), "Thresholds Must Satisfy 0 < Poor < Satisfactory < Good", B3 - B2)

Actual Attainment % Against Comparative Bullet Target

Computes ROUND(100 * Actual / Target, 2) for Actual B5 and positive Target B6.

=IF(OR(B5 < 0, B6 <= 0), "Requires Actual >= 0 and Target > 0", ROUND(100 * B5 / B6, 2))

Further reading

• Microsoft Support — Combine two or more chart types in a combo chart