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

Create a Box and Whisker Chart in Excel with Python

Excel's built-in box-and-whisker chart cannot be written by openpyxl or XlsxWriter, but a native, editable box plot can be built from a stock chart: compute quartiles and whisker ends with pandas, map them to open, high, low and close, let up-down bars draw the boxes and high-low lines the whiskers, add a median marker, and list outliers beside the chart.
Charts & imagesopenpyxl

Averages hide the shape of a distribution; a box plot shows it. For delivery times, call durations or order values, the box (the middle half of the data), the line inside it (the median) and the whiskers (the spread) tell a reader in one glance which region is consistently slow and which one is usually fine but has a long tail. Excel 2016 added a box-and-whisker chart type, but it uses a newer chart format that neither openpyxl nor XlsxWriter can write. The good news is that a classic chart type already draws exactly those shapes: a stock chart. Its up-down bars become the boxes and its high-low lines the whiskers. This page builds an editable box plot that way, with the statistics computed in pandas. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

Bash
pip install pandas xlsxwriter pillow

delivery_days.xlsx has 160 deliveries — 40 per region — with the number of days each took. LibreOffice (soffice) and pdftoppm render the chart in Step 6; they are optional.

Step 1 — Compute the five numbers per group

Python
import pandas as pd

deliveries = pd.read_excel("delivery_days.xlsx")
order = ["North", "South", "East", "West"]

def box_stats(s):
    q1, med, q3 = s.quantile([0.25, 0.5, 0.75])
    iqr = q3 - q1
    low_fence, high_fence = q1 - 1.5 * iqr, q3 + 1.5 * iqr
    inside = s[(s >= low_fence) & (s <= high_fence)]
    return pd.Series({"Q1": q1, "Median": med, "Q3": q3,
                      "LowWhisker": inside.min(), "HighWhisker": inside.max(),
                      "Outliers": int(((s < low_fence) | (s > high_fence)).sum())})

stats = deliveries.groupby("Region")["Days"].apply(box_stats).unstack().loc[order]
print(stats.round(2))

The quartiles use pandas' default method, which matches Excel's QUARTILE.INC — the difference from the "exclusive" method is explained in MEDIAN, MODE and QUARTILE Equivalents in pandas. The whiskers follow the usual Tukey rule: they reach the most extreme data point within 1.5 times the interquartile range of the box; anything beyond is an outlier, counted here and listed in Step 5. If your audience expects whiskers from the minimum to the maximum, use s.min() and s.max() instead and say so in the chart title.

What each part of the box plot shows From top to bottom, the high whisker ends at the largest value within 1.5 times the IQR above Q3, the box spans Q1 to Q3 with the median line inside, and the low whisker ends at the smallest value within 1.5 times the IQR below Q1. Points beyond the whiskers are outliers. High whisker largest value ≤ Q3 + 1.5 × IQR Box: Q1 to Q3, median inside the middle half of the data Low whisker smallest value ≥ Q1 − 1.5 × IQR Outliers beyond the whiskers — listed separately

Step 2 — Map the numbers onto a stock chart

A stock chart expects four series per category in a fixed order — open, high, low, close — and draws a vertical line from low to high plus a bar between open and close. Feed it the box plot's numbers in that order:

Python
import xlsxwriter

book = xlsxwriter.Workbook("delivery_boxplot.xlsx")
sheet = book.add_worksheet("Box plot")
headers = ["Region", "Q1 (open)", "High whisker (high)", "Low whisker (low)", "Q3 (close)", "Median", "Outliers"]
sheet.write_row(0, 0, headers)
for r, (region, row) in enumerate(stats.iterrows(), start=1):
    sheet.write_row(r, 0, [region, row["Q1"], row["HighWhisker"], row["LowWhisker"], row["Q3"],
                           row["Median"], row["Outliers"]])
n = len(stats)
cats = ["Box plot", 1, 0, n, 0]

Mapping Q1 to "open" and Q3 to "close" makes the up-down bar span exactly the box, and the whisker ends as "high" and "low" make the high-low line span the whiskers. Writing the numbers to the sheet — rather than embedding them in the chart — keeps the chart linked to visible, checkable cells.

Box plot numbers mapped to stock-chart series On the left the statistics for North — Q1 4.55, median 6.05, Q3 7.78 and whiskers at 1.7 and 12.5. On the right the same numbers as the stock chart's open, high, low and close series, with the median as an extra series. statistic North Q1 4.55 High whisker max ≤ fence Low whisker min ≥ fence Q3 7.78 series draws open = Q1 box bottom high = whisker line top low = whisker line bottom close = Q3 box top box statistics stock series O · H · L · C

Step 3 — Boxes from up-down bars, whiskers from high-low lines

Python
box = book.add_chart({"type": "stock"})
for col in range(1, 5):
    box.add_series({"name": ["Box plot", 0, col], "categories": cats,
                    "values": ["Box plot", 1, col, n, col],
                    "line": {"none": True}, "marker": {"type": "none"}})
box.set_high_low_lines({"line": {"color": "#334155", "width": 1.25}})
same = {"fill": {"color": "#E0E7FF"}, "border": {"color": "#4338CA"}}
box.set_up_down_bars({"up": same, "down": same})

The four series themselves are invisible — no line, no marker — because only their derived shapes matter. set_high_low_lines draws the whiskers. set_up_down_bars draws a bar from open to close; normally "up" and "down" bars are coloured differently (rising and falling prices), but Q3 is always above Q1, so giving both the same style keeps every box identical.

Step 4 — Add the median and finish the axes

The median is a fifth number that a stock chart has no slot for, so add it as a separate line series with a dash marker and combine it with the stock chart:

Python
median = book.add_chart({"type": "line"})
median.add_series({"name": "Median", "categories": cats, "values": ["Box plot", 1, 5, n, 5],
                   "line": {"none": True},
                   "marker": {"type": "long_dash", "size": 16, "fill": {"color": "#4338CA"},
                              "border": {"color": "#4338CA"}}})
box.combine(median)
box.set_title({"name": "Delivery days by region (whiskers: 1.5 × IQR)"})
box.set_legend({"none": True})
box.set_y_axis({"min": 0, "name": "Days", "major_gridlines": {"visible": True, "line": {"color": "#E5E7EB"}}})
box.set_size({"width": 620, "height": 340})
sheet.insert_chart("A8", box)

The median dash sits across each box. The title states the whisker rule, because readers interpret box plots differently. A fixed axis minimum of zero keeps the boxes comparable; the maximum is left automatic so long whiskers are never cut off. Stock-chart details are in Create a Stock Chart with openpyxl.

Step 5 — List the outliers beside the chart

Points beyond the whiskers are not drawn by this construction, so list them in a small table — which is often more useful than dots anyway, because each one is a delivery someone may want to look up:

Python
outliers = []
for region, s in deliveries.groupby("Region")["Days"]:
    q1, q3 = s.quantile([0.25, 0.75])
    fence = q3 + 1.5 * (q3 - q1)
    outliers += [(region, d) for d in s[s > fence].sort_values(ascending=False)]
sheet.write_row(0, 9, ["Region", "Outlier (days)"])
for r, (region, d) in enumerate(outliers, start=1):
    sheet.write_row(r, 9, [region, d])
sheet.set_column("A:A", 10)
sheet.set_column("B:G", 12)
book.close()
print(f"{len(outliers)} outliers:", outliers)

Only high outliers occur here, because delivery times cannot go far below zero; the same loop with s < q1 - 1.5 * iqr finds low ones. Finding outliers in general is covered in Detect Outliers in Excel Data with pandas.

Step 6 — Check the rendered chart

Python
import subprocess
from PIL import Image

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "delivery_boxplot.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "60", "-f", "1", "-l", "1", "delivery_boxplot.pdf", "boxplot"], check=True)
counts = {c: n for n, c in Image.open("boxplot-1.png").convert("RGB").getcolors(maxcolors=1_000_000)}
for label, rgb in {"boxes": (0xE0, 0xE7, 0xFF), "medians": (0x43, 0x38, 0xCA), "whiskers": (0x33, 0x41, 0x55)}.items():
    print(f"{label:9} {'drawn' if counts.get(rgb, 0) > 20 else 'MISSING'}")

Counting pixels of each colour confirms that the boxes, median dashes and whiskers were all drawn. That check matters for this chart in particular: an earlier construction of the same plot, using stacked columns with custom error bars for the whiskers, rendered boxes without any whiskers in LibreOffice, which does not draw custom error-bar values from these files. The stock-chart construction uses only standard elements and renders the same in both applications.

Median and quartiles per region Q1, median and Q3 delivery days per region. North 4.55, 6.05 and 7.78; South 4.97, 6.05 and 8.93; East 1.58, 2.85 and 4.03; West 5.55, 8.6 and 12.55. West is slowest and most variable, East fastest. Q1 Median Q3 0 5 10 15 North South East West

Performance notes

The statistics are a group-by over the raw data and take milliseconds even for millions of rows; the chart itself only ever holds five numbers per group, so it stays small and fast however much data sits behind it. That is a real advantage over pasting raw data into Excel's own box-plot type, which recalculates from every point. Keep the raw data in its own sheet (or out of the workbook entirely) and write only the summary table the chart needs.

Common pitfalls

  • Expecting a native box-plot type. The Excel 2016 box-and-whisker chart cannot be written by openpyxl or XlsxWriter; build it from a stock chart.
  • Wrong series order. Stock charts need open, high, low, close in that order; anything else draws the wrong shapes.
  • Different up and down colours. Style both identically, since Q3 is always above Q1.
  • Unstated whisker rule. Say whether whiskers are min–max or 1.5 × IQR.
  • Custom error bars for whiskers. They do not render in every application; high-low lines do.

Conclusion

Compute Q1, median, Q3 and the whisker ends per group with pandas, write them to the sheet as open (Q1), high (upper whisker), low (lower whisker) and close (Q3), and build a stock chart with invisible series: up-down bars draw the boxes and high-low lines the whiskers. Add the median as a line series with dash markers, state the whisker rule in the title, list outliers in a table beside the chart, and render it once to confirm every element appears.

Frequently asked questions

Can openpyxl or XlsxWriter create Excel's box and whisker chart? No. The box-and-whisker type added in Excel 2016 uses a newer chart format that these libraries do not write. A stock chart with up-down bars and high-low lines produces an equivalent, editable box plot.

How do I make a box plot from a stock chart? Use four series in the order open, high, low, close with Q1, upper whisker, lower whisker and Q3. Up-down bars then draw the box from Q1 to Q3 and high-low lines draw the whiskers.

How do I add the median to the box plot? Add the median values as a line series with no line and a long-dash marker, and combine that line chart with the stock chart so the dash sits across each box.

How are box plot whiskers calculated? Commonly with Tukey's rule: each whisker reaches the most extreme data point within 1.5 times the interquartile range from the box. Points beyond are outliers. Some charts use the minimum and maximum instead, so state the rule.

More on charts & images

More on charts & images →