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

Create a Butterfly Chart in Excel with Python

Build a butterfly (tornado, population-pyramid) chart as a native Excel bar chart with openpyxl: plot one side as negative values, overlap the two series on the same rows, hide the minus signs with a two-part number format, move the category labels to the edge, show positive data labels, colour each side, and check the rendered result.
Charts & imagesopenpyxl

Comparing two groups across the same categories — women and men by age band, this year and last year by product, survey agreement and disagreement per question — is clearest when the bars grow outward from a shared centre line: one group to the left, the other to the right. That shape is called a butterfly, tornado or population-pyramid chart. Excel has no dedicated type, but a horizontal bar chart becomes one with three small tricks: plot one series as negative numbers, make both series share the same row, and use a number format that hides the minus signs. openpyxl can set all of it, so the chart is generated as a native, editable Excel chart. This page builds one step by step and renders it to check the details. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

Bash
pip install openpyxl pillow

LibreOffice (soffice) and pdftoppm render the chart in Step 6; they are optional. The example shows survey respondents by age band and gender.

Step 1 — Lay out the data with a negative helper column

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Survey"
ws.append(["Age band", "Women", "Men", "Men (plotted)"])
rows = [("18–24", 112, 98), ("25–34", 240, 251), ("35–44", 205, 230),
        ("45–54", 176, 190), ("55–64", 120, 108), ("65+", 64, 41)]
for r, (band, women, men) in enumerate(rows, start=2):
    ws.append([band, women, men])
    ws.cell(r, 4, f"=-C{r}")
ws.column_dimensions["A"].width = 11
print([[c.value for c in row] for row in ws.iter_rows(min_row=1, max_row=3)])

The real counts stay in columns B and C, readable and summable. Column D holds the men's values negated with a formula, purely for plotting; keeping it as a formula means a corrected count in column C flows into the chart automatically. The negative values are what push the men's bars to the left of the axis. If no one will edit the workbook, writing the negated numbers as values works equally well and lets other tools read them without recalculation.

One side of the chart is plotted as negative values On the left the survey table with women and men per age band. On the right the plotted values, women positive and men negated, so that the men's bars extend to the left of the centre line. A B C 1 Age band Women Men 2 18–24 112 98 3 25–34 240 251 4 35–44 205 230 A B C 1 Age band Women Men (plotted) 2 18–24 112 −98 3 25–34 240 −251 4 35–44 205 −230 counts plotted values =-C2

Step 2 — A horizontal bar chart with overlapping series

Python
from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.type = "bar"                 # horizontal bars
chart.grouping = "clustered"
chart.overlap = 100                # both series on the same row
chart.gapWidth = 30                # thick bars, small gaps between rows
chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=7), titles_from_data=True)
chart.add_data(Reference(ws, min_col=4, min_row=1, max_row=7), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=7))
for series in chart.series:
    series.invertIfNegative = False

type = "bar" turns the column chart sideways. With the default overlap of 0, the two series would sit on separate thin rows; overlap = 100 puts both on the same row, and because one is positive and the other negative, they extend in opposite directions from zero. invertIfNegative = False matters: without it, some applications draw negative bars in an inverted (white) fill. A small gapWidth makes the bars substantial, which suits a chart with only six rows.

Step 3 — Hide the minus signs

The men's axis labels and values are negative numbers; readers should see positive counts on both sides. A two-part number format shows negatives without the sign:

Python
chart.y_axis.number_format = "#,##0;#,##0"
chart.y_axis.title = "Respondents"
chart.x_axis.delete = False
chart.y_axis.delete = False

A number format's sections are separated by semicolons — positive; negative — and the second section here has no minus sign, so −200 displays as 200. The axis then reads 300, 200, 100, 0, 100, 200, 300 from left to right. The two delete = False lines make sure Excel shows both axes, which openpyxl otherwise leaves to the application's defaults. Custom number formats are covered in Write Custom Number Format Codes in Excel with Python.

Women and men by age band Respondents by age band. Women 112, 240, 205, 176, 120 and 64; men 98, 251, 230, 190, 108 and 41 for the bands 18–24 to 65+. In the butterfly chart the men's bars extend left and the women's right. Women Men 0 100 200 300 18–24 25–34 35–44 45–54 55–64 65+ The same counts as ordinary columns; the butterfly layout places the two series back to back.

Step 4 — Move the category labels to the edge

By default, the age-band labels sit next to the zero line — in the middle of the chart, on top of the men's bars. Moving them to the low end of the axis puts them along the left edge:

Python
chart.x_axis.tickLblPos = "low"
chart.x_axis.scaling.orientation = "minMax"

tickLblPos = "low" places the category labels at the minimum end of the value axis, which in a butterfly chart is the far left. Bar charts list categories from the bottom up, so the first row (18–24) appears at the bottom — the conventional orientation for a population pyramid, with the oldest at the top. For a ranked tornado chart where the first item should be at the top, set orientation = "maxMin" instead.

Step 5 — Labels, colours and title

Python
from openpyxl.chart.label import DataLabelList

colours = {0: "0F9488", 1: "4338CA"}
for i, series in enumerate(chart.series):
    series.graphicalProperties.solidFill = colours[i]
    series.graphicalProperties.line.solidFill = colours[i]
    series.dLbls = DataLabelList()
    series.dLbls.showVal = True
    series.dLbls.numFmt = "#,##0;#,##0"
chart.title = "Respondents by age band and gender"
chart.legend.position = "b"
chart.width, chart.height = 17, 9
ws.add_chart(chart, "A9")
wb.save("survey_butterfly.xlsx")
print("chart saved with", len(chart.series), "series")

The same sign-hiding format on the data labels shows 98 instead of −98 at the end of each men's bar. Distinct colours for the two sides, with the legend at the bottom, make the reading obvious. Placing the chart at A9, below the table, keeps it on the same printed page. Styling series in general is covered in Style Chart Colours and Data Labels with openpyxl.

Step 6 — Check the rendered chart

Python
import subprocess
from PIL import Image

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "survey_butterfly.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "60", "-f", "1", "-l", "1", "survey_butterfly.pdf", "butterfly"], check=True)
img = Image.open("butterfly-1.png").convert("RGB")
teal, indigo = (0x0F, 0x94, 0x88), (0x43, 0x38, 0xCA)
xs = {name: [x for x in range(img.width) for y in range(0, img.height, 4) if img.getpixel((x, y)) == rgb]
      for name, rgb in (("women", teal), ("men", indigo))}
print("women bars span x =", min(xs["women"]), "to", max(xs["women"]))
print("men bars span   x =", min(xs["men"]), "to", max(xs["men"]))
print("men drawn to the left of women:", max(xs["men"]) <= min(xs["women"]) + 2)

Finding where each colour appears on the rendered page confirms the defining property of the chart: the men's bars lie entirely to the left of the women's, meeting at the centre line. A quick check like this catches the most common mistake — forgetting overlap = 100, which renders the two series as thin interleaved bars instead of a butterfly.

The settings that make a bar chart a butterfly Without the settings, the chart shows thin bars side by side with negative numbers and labels in the middle. With overlap 100, a two-part number format, labels at the low position and invertIfNegative off, it becomes a back-to-back chart with positive labels along the edge. Plain bar chart two thin bars per row axis shows −300 … 300 labels sit on the centre line negative bars may be hollow Butterfly settings overlap = 100, gapWidth = 30 format #,##0;#,##0 tickLblPos = "low" invertIfNegative = False

The same chart as a sensitivity tornado

The butterfly layout has a second life in finance: the tornado chart of a sensitivity analysis, where each row is an assumption and the bars show how far profit falls (left) or rises (right) when that assumption moves to its low or high case. The construction is identical — the downside as negative values, the upside as positive, overlap = 100 and the sign-hiding format — with two changes: sort the rows by total swing so the most influential assumption is widest, and set chart.x_axis.scaling.orientation = "maxMin" so that it appears at the top, giving the chart its funnel shape. The low and high cases themselves can come from the scenario runs described in Run What-If Scenarios on an Excel Model with Python.

Common pitfalls

  • Forgetting overlap. Without overlap = 100 the series sit side by side instead of back to back.
  • Minus signs on the axis and labels. Use a two-part number format on the axis and on the data labels.
  • Labels in the middle. Set the category axis tickLblPos to "low".
  • Hollow negative bars. Set invertIfNegative = False on each series.
  • Unbalanced axes. If one side is much larger, fix the axis min and max symmetrically so the centre line stays in the middle.

Conclusion

A butterfly chart is a horizontal bar chart with one series plotted as negative values. Keep the real numbers in their own columns and negate a helper column for plotting, overlap the series fully with a small gap, hide minus signs with #,##0;#,##0 on the axis and the data labels, move category labels to the edge with tickLblPos = "low", and turn off invertIfNegative. Render it once to confirm the two sides meet at the centre.

Frequently asked questions

How do I make a butterfly or tornado chart in Excel with Python? Create a horizontal bar chart with two series, one of them negated, set overlap to 100 so they share each row, and use the number format #,##0;#,##0 so negative values display without a minus sign. openpyxl supports all of these settings.

How do I hide the minus sign on negative values in an Excel chart? Use a number format with two sections whose negative section has no minus sign, such as #,##0;#,##0, on the value axis and on the data labels.

Why are my category labels in the middle of the chart? They are drawn next to the zero line by default. Set the category axis tick label position to low (in openpyxl, chart.x_axis.tickLblPos = "low") to move them to the edge.

Why are the negative bars white or hollow? Some applications invert the fill of negative bars unless invertIfNegative is turned off. Set series.invertIfNegative = False for each series.

More on charts & images

More on charts & images →