Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Create a Gauge Chart in Excel with Python

Build a speedometer-style KPI gauge from Python with openpyxl: a half-circle doughnut made by hiding half of each ring, coloured red, amber and green bands from your thresholds, a value arc that moves when the KPI cell changes, a helper table of formulas behind it, several gauges on one dashboard, and a render check.
Charts & imagesopenpyxl

Dashboards love gauges: a half-circle dial showing one number against its good, acceptable and bad ranges — customer satisfaction at 72 out of 100, in the green band that starts at 75? No, just short, in amber. Excel has no gauge chart type, but the classic construction needs only a doughnut chart: make it a half circle by giving the bottom half an invisible slice, colour the bands for the thresholds, and add a second ring whose coloured arc runs from the start of the dial to the current value. Built from Python with openpyxl, the gauge is driven by formulas on the sheet, so when the KPI value is updated the dial moves with it. This page is part of Creating Charts in Excel with openpyxl.

Prerequisites

Bash
pip install openpyxl

The gauge shows a customer satisfaction score from 0 to 100, with red below 50, amber from 50 to 75 and green from 75 upwards. The same construction works for any KPI with a minimum, a maximum and thresholds: on-time delivery rate, budget used, capacity utilisation. LibreOffice renders the result for checking.

A gauge is a doughnut with half of it hidden Layers explaining the construction. The outer ring holds the red, amber and green bands whose sizes are the threshold widths; the inner ring holds the value arc and the remainder; each ring has a final slice equal to the full range that is made invisible, which turns the circle into a half circle. Outer ring — bands red 0–50 · amber 50–75 · green 75–100 · hidden 100 Inner ring — value value 72 · remainder 28 · hidden 100 firstSliceAng = 270 starts at 9 o'clock, so the hidden half is the bottom

Step 1 — The helper table with live formulas

The chart reads a small table. The KPI value and the thresholds are inputs; everything the rings need is calculated from them, so the gauge follows any change to the inputs:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Gauge"
inputs = {"B1": ("KPI value", 72), "B2": ("Minimum", 0), "B3": ("Maximum", 100),
          "B4": ("Amber from", 50), "B5": ("Green from", 75)}
for ref, (label, value) in inputs.items():
    ws[f"A{ref[1:]}"], ws[ref] = label, value

ws["A7"], ws["B7"], ws["C7"] = "Slice", "Bands", "Value"
rows = [
    ("Red",    "=B4-B2",  "=MIN(MAX(B1,B2),B3)-B2"),
    ("Amber",  "=B5-B4",  "=B3-MIN(MAX(B1,B2),B3)"),
    ("Green",  "=B3-B5",  "=0"),
    ("Hidden", "=B3-B2",  "=B3-B2"),
]
for r, row in enumerate(rows, start=8):
    for c, v in enumerate(row, start=1):
        ws.cell(r, c, v)
print([[ws.cell(r, c).value for c in range(1, 4)] for r in range(7, 12)])

The Bands column holds the width of each band — red from the minimum to the amber threshold, and so on — plus a hidden slice equal to the whole range. The Value column holds the value arc (from the minimum to the value, clamped to the range with MIN and MAX so an out-of-range reading does not break the dial), the remainder up to the maximum, and the same hidden slice. Because each ring's visible slices add up to exactly the hidden one, the visible part is always a half circle.

Step 2 — Build the two-ring doughnut

Add both columns as series. The first series becomes the inner ring, the second the outer, so the value comes first and the bands second:

Python
from openpyxl.chart import DoughnutChart, Reference
from openpyxl.chart.series import DataPoint

gauge = DoughnutChart()
gauge.firstSliceAng = 270
gauge.holeSize = 45
gauge.add_data(Reference(ws, min_col=3, min_row=7, max_row=11), titles_from_data=True)   # inner: value
gauge.add_data(Reference(ws, min_col=2, min_row=7, max_row=11), titles_from_data=True)   # outer: bands
gauge.set_categories(Reference(ws, min_col=1, min_row=8, max_row=11))

def paint(series, colours):
    series.dPt = []
    for idx, colour in enumerate(colours):
        point = DataPoint(idx=idx)
        if colour is None:
            point.graphicalProperties.noFill = True
            point.graphicalProperties.line.noFill = True
        else:
            point.graphicalProperties.solidFill = colour
            point.graphicalProperties.line.solidFill = "FFFFFF"
        series.dPt.append(point)

value_ring, band_ring = gauge.series
paint(value_ring, ["4338CA", "E5E7EB", None, None])
paint(band_ring, ["DC2626", "F59E0B", "0F9488", None])
print(gauge.firstSliceAng, [len(s.dPt) for s in gauge.series])

firstSliceAng = 270 starts the first slice at nine o'clock and runs clockwise, so the visible slices sweep across the top and the hidden slice fills the bottom half. The hidden slices have no fill and no outline — leaving the outline in draws a faint circle around the invisible half. On the inner ring, the value arc is indigo and the remainder light grey; its third slice is zero-width and its fourth hidden. On the outer ring, the band colours come from the thresholds.

Step 3 — Title, size and placement

The number itself belongs in the title, where it is always readable, since a doughnut has no text in its hole:

Python
gauge.legend = None
gauge.title = "Customer satisfaction"
gauge.width, gauge.height = 11, 6.5
ws["E1"] = '="Score: "&B1&" of "&B3'
ws.add_chart(gauge, "A14")
wb.save("kpi_gauge.xlsx")
print(ws["E1"].value)

The formula in E1 gives a live text label — "Score: 72 of 100" — that can sit above or beside the chart in a dashboard layout; the chart title stays generic so it does not go stale. A height a little over half the width suits a half circle and leaves less empty space under it.

The helper table the rings read A worksheet with the inputs and the calculated slice table. The Bands column has 50, 25, 25 and 100; the Value column has 72, 28, 0 and 100. kpi_gauge.xlsx A B C 7 Slice Bands Value 8 Red 50 72 9 Amber 25 28 10 Green 25 0 11 Hidden 100 100 Formulas follow B1 and the thresholds

Step 4 — Check the rendering and the formulas

Render the workbook with LibreOffice, which also calculates the helper formulas, and read the calculated values back to confirm the rings add up:

Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf kpi_gauge.xlsx > /dev/null 2>&1
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir calc kpi_gauge.xlsx > /dev/null 2>&1
pdftoppm -png -r 60 -f 1 -l 1 kpi_gauge.pdf preview && ls preview* calc
Python
from openpyxl import load_workbook

calc = load_workbook("calc/kpi_gauge.xlsx", data_only=True)["Gauge"]
bands = [calc.cell(r, 2).value for r in range(8, 12)]
values = [calc.cell(r, 3).value for r in range(8, 12)]
print("bands", bands, "visible", sum(bands[:3]), "= hidden", bands[3])
print("value", values, "visible", sum(values[:3]), "= hidden", values[3], "|", calc["E1"].value)

Both rings' visible slices add up to the hidden slice, so each is exactly a half circle, and the value arc ends at 72 — inside the amber band, just short of green. The rendered page shows the dial with the indigo arc three-quarters of the way round the inner ring, under the red, amber and green bands.

Step 5 — Several gauges on one dashboard

Dashboards usually show a few KPIs side by side. Wrap the construction in a function that writes its helper table in its own block of columns and anchors its chart:

Python
def add_gauge(ws, col, title, value, lo, hi, amber, green, anchor, bands=("DC2626", "F59E0B", "0F9488")):
    c = col
    ws.cell(1, c, title)
    for i, v in enumerate([value, lo, hi, amber, green], start=2):
        ws.cell(i, c + 1, v)
    x = ws.cell(1, c + 1).column_letter
    for k, head in enumerate(["Slice", "Bands", "Value"]):
        ws.cell(8, c + k, head)
    table = [("Red", f"={x}5-{x}3", f"=MIN(MAX({x}2,{x}3),{x}4)-{x}3"),
             ("Amber", f"={x}6-{x}5", f"={x}4-MIN(MAX({x}2,{x}3),{x}4)"),
             ("Green", f"={x}4-{x}6", "=0"), ("Hidden", f"={x}4-{x}3", f"={x}4-{x}3")]
    for r, row in enumerate(table, start=9):
        for k, v in enumerate(row):
            ws.cell(r, c + k, v)
    g = DoughnutChart()
    g.firstSliceAng, g.holeSize, g.legend, g.title = 270, 45, None, title
    g.add_data(Reference(ws, min_col=c + 2, min_row=8, max_row=12), titles_from_data=True)
    g.add_data(Reference(ws, min_col=c + 1, min_row=8, max_row=12), titles_from_data=True)
    paint(g.series[0], ["4338CA", "E5E7EB", None, None])
    paint(g.series[1], [*bands, None])
    g.width, g.height = 8, 5
    ws.add_chart(g, anchor)

dash = wb.create_sheet("Dashboard")
add_gauge(dash, 1, "On-time delivery %", 91, 0, 100, 85, 95, "A15")
add_gauge(dash, 5, "Budget used %", 64, 0, 100, 80, 95, "F15", bands=("0F9488", "F59E0B", "DC2626"))
wb.save("kpi_gauge.xlsx")
print(len(dash._charts), "gauges on the dashboard")

Each gauge owns a block of columns for its inputs and slices, so they cannot interfere. For a budget, where higher is worse, the bands argument reverses the colours — green first, red last — while the maths stays the same: 64% used sits in the green band, with amber from 80% and red from 95%. Add a KPI Summary Block to an Excel Dashboard shows how to lay out a row of KPIs around charts like these.

When a gauge helps and when it does not Two panels. A gauge helps for a single KPI with well-known thresholds shown on a dashboard. It does not help when comparing several values or showing change over time, where a bar or line chart is clearer and takes less space. Gauge works one KPI, one moment thresholds everyone knows dashboard headline Use something else comparing many values trends over time precise reading of the number

Common pitfalls

  • Visible hidden slices. Set both noFill and line.noFill, or an outline shows the bottom half.
  • Rings in the wrong order. The first series is the inner ring; add the value before the bands if the bands should be outside.
  • Out-of-range values. Clamp with MIN and MAX, or a value above the maximum distorts the half circle.
  • Numbers only in the dial. Put the value in a label or title; the arc alone is hard to read precisely.
  • Gauges everywhere. They take a lot of space for one number; use them for headline KPIs only.

Conclusion

Build the gauge as a two-ring DoughnutChart over a helper table: bands for the thresholds on the outer ring, the value arc and remainder on the inner ring, and on each ring a hidden slice equal to the full range, with firstSliceAng = 270 so the hidden half is at the bottom. Drive the table with formulas from the KPI and threshold cells so the dial moves when they change, show the number in a label, check the rendering and the ring sums, and wrap it all in a function for a dashboard of several gauges.

Frequently asked questions

Does Excel have a gauge chart? No built-in type. The usual construction is a doughnut chart with a hidden half: slices for the bands and the value, plus a slice equal to the whole range that is made invisible and placed at the bottom with a first slice angle of 270 degrees.

How do I make half of a doughnut chart invisible with openpyxl? Give the ring a final slice equal to the sum of the visible ones and set that data point's graphicalProperties.noFill and line.noFill to True. Set chart.firstSliceAng = 270 so the visible half is at the top.

How do I make the gauge update when the value changes? Point the chart at a helper table of formulas that compute the slice sizes from the KPI and threshold cells. Changing the KPI cell recalculates the table and the chart redraws.

Can I add a needle to the gauge? A needle needs a pie chart overlaid on the doughnut, which openpyxl cannot reliably combine. The value arc used here shows the same information and is simpler to build and maintain.

More on charts & images

More on charts & images →