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

Create a Dumbbell Chart in Excel with Python

Build a dumbbell chart — two dots per category joined by a line, showing a before and after value — as a native Excel chart with openpyxl: a line chart with the lines switched off and markers on, the connecting bars drawn as custom error bars from a hidden helper series, categories sorted by the size of the change, the helper hidden from the legend, and the result rendered to check it.
Charts & imagesopenpyxl

When a report compares two values for each of several categories — this year against last year, before and after a change, target against actual — a clustered bar chart is the usual choice, and it is often a poor one. The eye has to compare the heights of pairs of bars and work out which pairs moved the most. A dumbbell chart shows the same data as two dots on a line for each category, joined by a bar: the length of the bar is the change, its direction is obvious from which dot is on top, and categories that hardly moved shrink to two overlapping dots. Excel has no dumbbell chart type, but it can draw one from parts it does have, and openpyxl can write those parts. This page builds a native, editable dumbbell chart from a small table of satisfaction scores, sorts it by the size of the change, and renders it to check the result. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

Bash
pip install openpyxl pandas

LibreOffice is used at the end to render the chart to an image; it is optional.

How the chart is built

The trick is to make Excel draw three things it already knows how to draw. A line chart with two series — the earlier and the later value — gives the dots, once the lines are switched off and markers switched on. The connector between each pair of dots comes from error bars: a third, invisible series sits at the lower of the two values, and a custom error bar extends upwards from it by the gap between them. Because both values are plotted on the same category axis, the error bar runs exactly from one dot to the other. Everything stays a normal chart that people can restyle in Excel, and it updates when the numbers in the sheet change.

Three layers make a dumbbell An invisible helper series sits at the lower value of each pair and carries a custom error bar the length of the gap, which draws the connector. Two marker-only series draw the earlier value in grey and the later value in blue on top. Later value blue markers, no line Earlier value grey markers, no line Helper at the lower value invisible Error bar of the gap draws the connector

Step 1 — The data, sorted by change, with two helper columns

Python
import pandas as pd
from openpyxl import Workbook

scores = pd.DataFrame({
    "Region": ["North", "South", "East", "West", "Export", "Online"],
    "2025": [62, 58, 44, 70, 35, 66],
    "2026": [71, 54, 57, 73, 49, 64],
})
scores = scores.assign(change=scores["2026"] - scores["2025"]).sort_values("change", ascending=False)

wb = Workbook()
ws = wb.active
ws.title = "Satisfaction"
ws.append(["Region", "2025", "2026", "Low", "Gap"])
for i, row in enumerate(scores.itertuples(index=False), start=2):
    ws.append([row.Region, row._1, row._2, f"=MIN(B{i}:C{i})", f"=ABS(C{i}-B{i})"])
n = len(scores) + 1
print(scores.drop(columns="change").to_string(index=False))

The table is sorted by the change from one year to the next, largest improvement first, so the chart reads from the best story to the worst — the order in which a reader wants to discuss it. The two helper columns are formulas, not values computed in Python: Low is the smaller of the two scores and Gap the distance between them. Keeping them as formulas means that if someone corrects a score in the sheet, the connector moves with it. The helper columns can be hidden or placed out of the way; the chart needs them, the reader does not.

Step 2 — The marker series and the connector

Python
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.data_source import NumDataSource, NumRef
from openpyxl.chart.error_bar import ErrorBars
from openpyxl.chart.legend import LegendEntry
from openpyxl.chart.shapes import GraphicalProperties
from openpyxl.drawing.line import LineProperties

chart = LineChart()
chart.add_data(Reference(ws, min_col=4, min_row=1, max_row=n), titles_from_data=True)        # Low (helper)
chart.add_data(Reference(ws, min_col=2, max_col=3, min_row=1, max_row=n), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=n))

helper, earlier, later = chart.series
helper.graphicalProperties.line.noFill = True
helper.marker.symbol = "none"
helper.errBars = ErrorBars(errBarType="plus", errValType="cust", noEndCap=True,
                           plus=NumDataSource(numRef=NumRef(f=f"'{ws.title}'!$E$2:$E${n}")),
                           spPr=GraphicalProperties(ln=LineProperties(solidFill="BDBDBD", w=50800)))

for series, colour in [(earlier, "A5A5A5"), (later, "1F5FA8")]:
    series.graphicalProperties.line.noFill = True
    series.marker.symbol = "circle"
    series.marker.size = 11
    series.marker.graphicalProperties = GraphicalProperties(solidFill=colour)
    series.marker.graphicalProperties.line.solidFill = colour

The helper series is added first so it is drawn underneath, and its line and markers are both switched off — only its error bars are visible. The error bars are custom: instead of a fixed amount or a percentage, each point's bar length comes from the Gap column through a cell reference, so every category gets its own connector. errBarType="plus" draws the bar upwards only, from the lower dot to the higher one, and noEndCap removes the little crossbar Excel puts at the end of error bars by default. A wide grey line (50,800 EMUs is 4 points) reads as a bar rather than a whisker.

The two score series keep their markers and lose their lines. A muted grey for the earlier year and a strong colour for the later one puts the visual weight on the current figure, which is usually the one being discussed.

Step 3 — Axes, legend and placement

Python
chart.title = "Customer satisfaction by region, 2025 → 2026"
chart.y_axis.title = "Score"
chart.y_axis.scaling.min, chart.y_axis.scaling.max = 30, 80
chart.y_axis.majorUnit = 10
chart.x_axis.delete = chart.y_axis.delete = False
chart.legend.position = "b"
chart.legend.legendEntry = [LegendEntry(idx=0, delete=True)]       # hide "Low" from the legend
chart.width, chart.height = 18, 9
ws.add_chart(chart, "A10")
ws.column_dimensions["D"].hidden = ws.column_dimensions["E"].hidden = True
chart.visible_cells_only = False                                  # plot the hidden helper columns
wb.save("satisfaction_dumbbell.xlsx")
print("saved")

The vertical axis starts at 30 rather than zero. That is acceptable here, unlike for a bar chart: a dumbbell's message is in the distance between dots, not in the length from the axis, so a cropped axis makes the changes easier to see without misleading. The helper series would otherwise appear in the legend as Low; a LegendEntry with delete=True removes it by its index. Both axes are explicitly kept, because openpyxl writes charts whose axes Excel hides unless told otherwise. Finally the helper columns are hidden, and that needs one more setting. By default openpyxl marks a chart to plot visible cells only, which is also Excel's default, so hiding the helper columns would silently remove the connectors. Setting visible_cells_only = False is the code equivalent of ticking Show data in hidden rows and columns in Excel's chart settings. The first rendering of this chart, made before that line was added, showed the dots without a single connector — a good example of why a quick render is worth doing.

Step 4 — Render it to check

Bash
soffice --headless --convert-to pdf satisfaction_dumbbell.xlsx > /dev/null 2>&1
pdftoppm -r 70 -f 1 -l 1 -png satisfaction_dumbbell.pdf preview && ls preview*

Converting the workbook to PDF with LibreOffice and looking at the image is a quick way to check a chart built from parts, because a mistake — the helper series visible, error bars pointing the wrong way — is obvious at a glance. In the rendered chart, East and Export show the longest connectors, with the blue dot on top for an improvement; South and Online have short connectors with the grey dot on top, because their scores fell; and West is almost a single dot.

The data behind the chart, sorted by change Export and East improved most, by 14 and 13 points. North rose 9, West 3. Online fell 2 and South fell 4, so their earlier dot sits above the later one. A B C D 1 Region 2025 2026 Change 2 Export 35 49 +14 3 East 44 57 +13 4 North 62 71 +9 5 West 70 73 +3 6 Online 66 64 −2 7 South 58 54 −4

A note on high-low lines

Excel offers another way to draw the connectors: high-low lines, an option on line charts that joins the highest and lowest value in each category, which openpyxl exposes as chart.hiLowLines. In Excel the result looks the same and needs no helper columns. The catch is portability — LibreOffice ignores high-low lines on ordinary line charts, so the connectors vanish when the file is opened there or converted to PDF on a server without Excel. Error bars are drawn by both, which is why this page uses them. If the workbook will only ever be opened in Excel, high-low lines are a simpler option worth knowing.

Error bars or high-low lines for the connectors Custom error bars on a hidden helper series need two helper columns but render in both Excel and LibreOffice. High-low lines need no helpers but are only drawn by Excel. Custom error bars two helper columns rendered by Excel and LibreOffice works in server-side PDF export used on this page High-low lines no helper columns rendered by Excel only vanish in LibreOffice simpler if Excel is guaranteed

Common pitfalls

  • Lines left on. Set line.noFill on every series or the dots are joined across categories.
  • Error bars without cust. Fixed or percentage bars do not match each pair's gap.
  • The helper in the legend. Remove it with LegendEntry(idx=0, delete=True).
  • Unsorted categories. Sort by change so the chart has a reading order.
  • Hidden helper columns, invisible connectors. Set visible_cells_only = False.
  • Gap computed in Python. Use formulas so corrections in the sheet move the connectors.

Conclusion

A dumbbell chart shows two values per category and the change between them more clearly than paired bars. With openpyxl, build it from a line chart: two marker-only series for the values, and an invisible helper series at the lower value whose custom error bars, sized from a gap column, draw the connectors. Sort categories by change, hide the helper from the legend and the sheet while letting the chart plot hidden cells, keep the axes visible, and render the workbook once to check.

Frequently asked questions

Does Excel have a dumbbell chart type? No; it is built from a line or scatter chart with markers only, plus error bars or high-low lines for the connectors.

How do I add custom error bars in openpyxl? Create ErrorBars(errValType="cust", plus=NumDataSource(numRef=NumRef(f="'Sheet'!$E$2:$E$7"))) and assign it to the series' errBars.

How do I hide one series from the chart legend? Set chart.legend.legendEntry = [LegendEntry(idx=0, delete=True)], using the series' index.

Can the value axis start above zero in a dumbbell chart? Yes; the message is the distance between the dots, so starting the axis near the data makes changes easier to see without distorting them.

More on charts & images

More on charts & images →