Create a Bullet Chart in Excel with Python

Build a bullet chart — qualitative bands, an actual-value bar and a target marker — as a native Excel chart from Python: normalise KPIs with different units, draw the bands as stacked columns, draw the actual as a thick error bar and the target as a dash marker on the same axis, tidy the legend and axis, and check the rendered result. Includes why the common secondary-axis recipe is fragile.

Charts & images openpyxl

A dashboard with four gauges takes up a lot of space to say little. The bullet chart, designed as a compact replacement, shows the same information in a strip: grey bands for "poor", "satisfactory" and "good", a dark bar for the actual value, and a short line for the target. Several KPIs sit side by side, and a reader sees at a glance which ones fall short. Excel has no bullet chart type, but it can be assembled from parts a Python script controls: a stacked column chart for the bands, plus a second series for the actual and one for the target. The construction details decide whether the chart renders correctly everywhere, so this page builds it step by step with XlsxWriter and checks the rendered output. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

Bash

pip install xlsxwriter pandas pillow

LibreOffice (soffice) and pdftoppm render the chart in Step 6 for a visual check; they are optional. The example tracks four KPIs with different units — revenue in thousands, margin in percent, a Net Promoter Score and on-time delivery — each with an actual value, a target and the upper limits of its "poor" and "satisfactory" bands.

Step 1 — Put the KPIs on one scale

Revenue of 270 and a margin of 22 cannot share an axis. Express everything as a percentage of the KPI's "good" ceiling, so every column runs from 0 to 100:

Python

import pandas as pd

kpis = pd.DataFrame({
    "KPI": ["Revenue", "Margin", "NPS", "On-time"],
    "Actual": [270, 22, 48, 91],
    "Target": [250, 25, 50, 95],
    "PoorUpTo": [200, 15, 30, 80],
    "OkUpTo": [260, 20, 45, 90],
    "Max": [300, 30, 60, 100],
})
scale = 100 / kpis["Max"]
chart_data = pd.DataFrame({
    "KPI": kpis["KPI"],
    "Actual %": (kpis["Actual"] * scale).round(1),
    "Target %": (kpis["Target"] * scale).round(1),
    "Poor": (kpis["PoorUpTo"] * scale).round(1),
    "Satisfactory": ((kpis["OkUpTo"] - kpis["PoorUpTo"]) * scale).round(1),
    "Good": ((kpis["Max"] - kpis["OkUpTo"]) * scale).round(1),
})
print(chart_data.to_string(index=False))

The band columns are the heights of each band, not their upper limits, because a stacked chart adds them up: Poor 66.7 + Satisfactory 20 + Good 13.3 = 100 for revenue. Keeping the original values in their own columns lets a table next to the chart show real numbers while the chart uses the normalised ones.

Step 2 — Draw the bands as stacked columns

Python

import xlsxwriter

book = xlsxwriter.Workbook("kpi_bullets.xlsx")
sheet = book.add_worksheet("KPIs")
sheet.write_row(0, 0, list(chart_data.columns))
for r, row in enumerate(chart_data.itertuples(index=False), start=1):
    sheet.write_row(r, 0, row)
n = len(chart_data)
cats = ["KPIs", 1, 0, n, 0]

bands = book.add_chart({"type": "column", "subtype": "stacked"})
for col, colour in ((3, "#BFC6D4"), (4, "#DDE2EA"), (5, "#EEF1F5")):
    bands.add_series({"name": ["KPIs", 0, col], "categories": cats,
                      "values": ["KPIs", 1, col, n, col], "fill": {"color": colour}, "gap": 40})

Three stacked series make the bands, darkest for "poor" at the bottom and lightest for "good" at the top — the conventional order, which makes the bands readable in greyscale print. A small gap ("gap": 40) makes each KPI's band wide, so the actual bar has room inside it.

Step 3 — Draw the actual value as a thick error bar

The obvious recipe — a second, narrower column series on a secondary axis — is fragile: in testing with LibreOffice, the secondary column series was not drawn at all and the secondary axis ignored its fixed 0–100 scale. A sturdier construction keeps everything on one axis. Add the actual values as a line series with no line and no marker, and give it a vertical error bar that runs 100% down from each point to zero, drawn as a thick dark line:

Python

lines = book.add_chart({"type": "line"})
lines.add_series({
    "name": "Actual", "categories": cats, "values": ["KPIs", 1, 1, n, 1],
    "line": {"none": True}, "marker": {"type": "none"},
    "y_error_bars": {"type": "percentage", "value": 100, "direction": "minus",
                     "end_style": 0, "line": {"color": "#1E293B", "width": 14}},
})

An error bar of 100% in the minus direction always reaches exactly from the value to zero, so it behaves like a bar of the right height, centred in the category — independent of any axis settings. end_style: 0 removes the end caps, and the 14-point line width makes it a slim column inside the band. Error bars from Python are covered in Add Error Bars to an Excel Chart with openpyxl.

Step 4 — Add the target marker

The target is a second series in the same line chart: no line, and a long horizontal dash as its marker:

Python

lines.add_series({
    "name": "Target", "categories": cats, "values": ["KPIs", 1, 2, n, 2],
    "line": {"none": True},
    "marker": {"type": "long_dash", "size": 22,
               "fill": {"color": "#DC2626"}, "border": {"color": "#DC2626"}},
})
bands.combine(lines)

combine overlays the line chart's series on the column chart, sharing its axes. A red dash is the usual target symbol; size 22 makes it a little wider than the actual bar, so it stays visible when the actual is close to the target.

Step 5 — Axis, title and legend

Python

bands.set_y_axis({"min": 0, "max": 100, "major_unit": 20, "num_format": '0"%"',
                  "major_gridlines": {"visible": True, "line": {"color": "#E5E7EB"}}})
bands.set_title({"name": "KPIs as % of the good threshold"})
bands.set_legend({"position": "bottom", "delete_series": [3]})
bands.set_size({"width": 640, "height": 360})
sheet.insert_chart("H2", bands)
sheet.set_column("A:A", 12)
book.close()
print("chart written with", n, "KPIs")

Fixing the axis at 0–100 makes every band reach the top of the plot. The number format 0"%" shows the values as percentages without multiplying them by 100. delete_series: [3] removes the fourth legend entry — the invisible "Actual" line series, whose legend symbol would be empty — leaving Poor, Satisfactory, Good and Target. Placing a small table of the raw values beside the chart answers the inevitable "what is 73%?" question; see Add a KPI Summary Block to an Excel Dashboard.

Step 6 — Check the rendered chart

Charts built from several series are easy to break with one wrong index. Render the sheet and confirm that the actual bars and target markers really appear:

Python

import subprocess
from PIL import Image

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "kpi_bullets.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "60", "-f", "1", "-l", "1", "kpi_bullets.pdf", "kpi_bullets"], check=True)
img = Image.open("kpi_bullets-1.png").convert("RGB")
counts = {colour: n for n, colour in img.getcolors(maxcolors=1_000_000)}
for label, rgb in {"actual bars": (0x1E, 0x29, 0x3B), "target markers": (0xDC, 0x26, 0x26),
                   "poor band": (0xBF, 0xC6, 0xD4)}.items():
    print(f"{label:15} {'present' if counts.get(rgb, 0) > 50 else 'MISSING'} ({counts.get(rgb, 0)} px)")

Counting pixels of each series colour proves that every layer was drawn — the check that would have caught the missing secondary-axis series. In the render, Revenue's bar rises past its target into the good band, Margin falls short in the satisfactory band, NPS sits just below target, and On-time is just below its 95% target in the good band.

Common pitfalls

Mixing units on one axis. Normalise each KPI to its own ceiling first.
Band upper limits instead of heights. Stacked columns add values; store each band's height.
Relying on a secondary axis. It renders inconsistently across applications; the error-bar construction stays on one axis.
An empty legend entry. Delete the invisible actual series from the legend.
Colour-only meaning. Keep the bands in light-to-dark greys so the chart survives greyscale printing.

Conclusion

A bullet chart in Excel is three layers on one axis: stacked columns for the poor, satisfactory and good bands, an invisible line series whose 100% minus error bar draws the actual value as a thick bar, and a second line series with long-dash markers for the target. Normalise KPIs to a common percentage scale first, fix the axis at 0–100, remove the invisible series from the legend, and render the chart in a test to make sure every layer appears.

Frequently asked questions

Can Excel make a bullet chart? Not as a built-in chart type, but you can build one by combining a stacked column chart for the bands with series for the actual value and the target. Python libraries such as XlsxWriter can create it in one script.

How do I show the actual value as a bar inside the bands? Add the actual values as a line series with no line and no marker, and give it a y error bar of 100 percent in the minus direction with a thick line. The error bar runs from the value down to zero like a narrow column.

How do I put KPIs with different units in one bullet chart? Express each KPI as a percentage of its own maximum or "good" threshold, so every KPI runs from 0 to 100, and show the raw values in a table beside the chart.

Why does my secondary-axis bullet chart look wrong? Secondary axes on combined column charts are rendered inconsistently: in testing, LibreOffice ignored the fixed axis scale and did not draw the secondary column series. Keeping all series on the primary axis with the error-bar technique avoids the problem.

Up one level: Creating Charts in Excel with openpyxl — native charts from Python.
Create a Gauge Chart in Excel with Python — the space-hungry alternative.
Add a Combo Chart with a Secondary Axis in openpyxl — when a secondary axis is the right tool.
Build a Monthly KPI Scorecard with Targets — a report that uses bullet charts.

More on charts & images