Python Excel AutomationPractical guides for developers
Fix
Formatting And Charting Excel Reports With PythonDeep dive

Add Error Bars to an Excel Chart with openpyxl

Show uncertainty and ranges on Excel charts written from Python: compute confidence intervals from raw data with pandas and SciPy, attach custom error bars that read their lengths from cells, draw asymmetric min/max ranges, use fixed and percentage error bars, style the bars, and check the result by rendering the workbook.
Charts & imagesopenpyxl

A column chart of average satisfaction by region says the East scores 7.8 and the West 6.9. It does not say that the West's average comes from 30 responses and could be more than half a point higher or lower, or that the East's is based on 400 and is solid. Error bars add that: a line through the top of each column showing the range the true value probably lies in. They also show ranges that are not about statistics at all — the lowest and highest daily value around a monthly average, or the spread of a forecast. openpyxl writes them as part of the chart, reading their lengths from cells so the workbook stays traceable. This page is part of Creating Charts in Excel with openpyxl.

Prerequisites

Bash
pip install openpyxl pandas scipy

survey_responses.xlsx holds individual satisfaction scores from 0 to 10, one row per response, for four regions with very different numbers of responses — from 400 in the East to 30 in the West. daily_load.xlsx has a daily electricity load figure for eight months, used for the min/max example.

What an error bar can mean Three panels. A confidence interval shows where the true average probably lies. A min–max range shows the spread of the underlying values. A fixed or percentage bar shows a tolerance that applies to every point. Confidence interval ± t × standard error depends on n survey and sample data Min–max range asymmetric lowest and highest value daily values around a month Fixed or percentage same for every point e.g. ±5% tolerance measurement precision

Step 1 — Compute the intervals from the raw data

A 95% confidence interval for a mean is the mean plus or minus a margin: the t critical value for the sample size times the standard error, which is the standard deviation divided by the square root of n. Compute it per region:

Python
import pandas as pd
from scipy import stats

responses = pd.read_excel("survey_responses.xlsx")
summary = responses.groupby("Region", sort=False)["Score"].agg(n="count", mean="mean", sd="std")
summary["t"] = stats.t.ppf(0.975, summary["n"] - 1)
summary["margin"] = summary["t"] * summary["sd"] / summary["n"] ** 0.5
summary = summary.round({"mean": 2, "sd": 2, "t": 3, "margin": 2})
print(summary)

The margin shrinks with the square root of the number of responses: the East, with 400 answers, has a margin of under two tenths of a point, while the West, with 30, has more than half a point. stats.t.ppf(0.975, n - 1) is the two-sided 95% critical value — close to 1.96 for large samples and noticeably larger for small ones, which is why using 1.96 everywhere understates the uncertainty of small groups. STDEV and VAR Equivalents in pandas covers the sample standard deviation std uses.

Step 2 — Write the summary and a chart with custom error bars

Write the summary to a sheet and build a column chart of the means. Custom error bars read their plus and minus lengths from cell ranges — here the margin column, for both directions:

Python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.data_source import NumDataSource, NumRef
from openpyxl.chart.error_bar import ErrorBars

wb = Workbook()
ws = wb.active
ws.title = "Survey"
ws.append(["Region", "Responses", "Mean score", "Margin (95%)"])
for region, row in summary.iterrows():
    ws.append([region, int(row["n"]), row["mean"], row["margin"]])

chart = BarChart()
chart.type = "col"
chart.title = "Satisfaction by region, mean with 95% interval"
chart.add_data(Reference(ws, min_col=3, min_row=1, max_row=5), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))
margin = NumDataSource(numRef=NumRef(f="'Survey'!$D$2:$D$5"))
chart.series[0].errBars = ErrorBars(errDir="y", errBarType="both", errValType="cust",
                                    plus=margin, minus=margin, noEndCap=False)
chart.series[0].graphicalProperties.solidFill = "4338CA"
chart.y_axis.scaling.min, chart.y_axis.scaling.max = 0, 10
chart.x_axis.delete = chart.y_axis.delete = False
chart.legend = None
chart.width, chart.height = 15, 8
ws.add_chart(chart, "A8")
wb.save("survey_chart.xlsx")
print("error bars read from", margin.numRef.f)

errValType="cust" means custom values from cells; errBarType="both" draws above and below the point; errDir="y" makes them vertical. Because the lengths are cell references, anyone who opens the workbook can see exactly what each bar represents, and changing the margin cell changes the bar. The value axis is fixed to the 0–10 scoring range so the bars are drawn in proportion. As with every openpyxl chart, delete = False on the axes makes Excel show them.

Step 3 — Check the rendering

Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf survey_chart.xlsx > /dev/null 2>&1
pdftoppm -png -r 60 -f 1 -l 1 survey_chart.pdf preview && ls preview*

In the rendered chart, the East's bar is a short tick above its column and the West's a long whisker — the picture now says what the table of margins said. The West's interval, from about 6.3 to 7.5, overlaps both the North's and the South's, so this survey cannot say where the West really ranks between them. The North and the South, on the other hand, are separated by more than their intervals combined: that difference is real.

The 95% margin shrinks as responses grow Horizontal bars of the 95 percent margin for each region. The East, with 400 responses, has a margin of 0.16 points; the North 0.23 with 220; the South 0.35 with 90; the West 0.60 with 30. East (n = 400) ± 0.16 North (n = 220) ± 0.23 South (n = 90) ± 0.35 West (n = 30) ± 0.60 The same numbers the error bars read from the Margin column.

Step 4 — Asymmetric ranges: min and max around a monthly average

Error bars do not have to be symmetric or statistical. For a monthly average of daily values, bars from the month's lowest to highest day show the spread. The plus length is max minus mean, the minus length mean minus min — two different ranges:

Python
load = pd.read_excel("daily_load.xlsx", parse_dates=["Date"])
monthly = load.groupby(load["Date"].dt.to_period("M"))["Load_MW"].agg(["mean", "min", "max"]).round(1)
monthly["up"] = (monthly["max"] - monthly["mean"]).round(1)
monthly["down"] = (monthly["mean"] - monthly["min"]).round(1)
print(monthly)

from openpyxl.chart import LineChart
from openpyxl.chart.shapes import GraphicalProperties
from openpyxl.drawing.line import LineProperties

ws2 = wb.create_sheet("Load")
ws2.append(["Month", "Average MW", "Up to max", "Down to min"])
for month, row in monthly.iterrows():
    ws2.append([month.strftime("%b"), row["mean"], row["up"], row["down"]])
line = LineChart()
line.title = "Daily load: monthly average with min–max range"
line.add_data(Reference(ws2, min_col=2, min_row=1, max_row=len(monthly) + 1), titles_from_data=True)
line.set_categories(Reference(ws2, min_col=1, min_row=2, max_row=len(monthly) + 1))
s = line.series[0]
s.errBars = ErrorBars(errDir="y", errBarType="both", errValType="cust",
                      plus=NumDataSource(numRef=NumRef(f=f"'Load'!$C$2:$C${len(monthly) + 1}")),
                      minus=NumDataSource(numRef=NumRef(f=f"'Load'!$D$2:$D${len(monthly) + 1}")))
s.errBars.spPr = GraphicalProperties(ln=LineProperties(solidFill="0F9488", w=19050))
s.graphicalProperties.line.solidFill = "4338CA"
s.marker.symbol = "circle"
line.x_axis.delete = line.y_axis.delete = False
line.legend = None
line.width, line.height = 15, 8
ws2.add_chart(line, "A11")
wb.save("survey_chart.xlsx")
print(len(monthly), "months")

Load climbs from about 450 MW in January to nearly 780 MW in July and August — a cooling-driven pattern — and July's downward whisker is the longest, reaching 637 MW on one unusually cool day.

Monthly average load, the series the whiskers sit on Line chart of average daily load by month from January to August, rising from about 449 megawatts to about 783 in July and 777 in August. 0 200 400 600 800 Jan Feb Mar Apr May Jun Jul Aug 449 479 531 617 681 747 783 777

Step 5 — Fixed and percentage error bars

When the same tolerance applies to every point — a measurement accurate to ±5%, a target band of ±20 units — no cell ranges are needed:

Python
pct = ErrorBars(errDir="y", errBarType="both", errValType="percentage", val=5)
fixed = ErrorBars(errDir="y", errBarType="plus", errValType="fixedVal", val=20)
print(pct.errValType, pct.val, "|", fixed.errValType, fixed.errBarType, fixed.val)

errValType can also be "stdErr" or "stdDev", which make Excel compute the bars from the plotted values themselves. Those are rarely what a report means — they describe the spread of the plotted points, not the uncertainty of each one — so compute intervals from the raw data as in Step 1 and use custom bars.

Common pitfalls

  • Bars without a stated meaning. Always say in the title or a note whether bars are a confidence interval, a range or a tolerance.
  • Using 1.96 for small samples. Use the t distribution; for 30 responses the critical value is about 2.05.
  • Built-in stdErr/stdDev. They are computed from the plotted points, not from the data behind each point.
  • Axis ranges that clip bars. Fix the axis so the longest whisker fits.
  • Hidden axes in Excel. Set delete = False on both axes.

Conclusion

Compute what the bars should show from the raw data — a t-based confidence interval per group, or the min and max around an average — and write those numbers to cells. Attach ErrorBars(errValType="cust", ...) to the chart series with plus and minus pointing at those cells, symmetric or not, style them with spPr, and fix the axis so they fit. Use fixed or percentage bars for uniform tolerances, and always say what the bars mean.

Frequently asked questions

How do I add error bars to a chart with openpyxl? Create an ErrorBars object and assign it to the series' errBars attribute. For values from cells use errValType="cust" and pass a NumDataSource wrapping a NumRef to the cell range as both plus and minus.

How do I make asymmetric error bars? Point plus and minus at two different cell ranges, for example max minus mean and mean minus min. Excel draws each side with its own length.

How do I calculate a 95% confidence interval in pandas? Group the raw values, compute count, mean and standard deviation, and set the margin to scipy.stats.t.ppf(0.975, n - 1) × sd / √n. The interval is the mean plus or minus the margin.

What do Excel's standard error and standard deviation error bars show? They are computed from the plotted values themselves, not from the data behind each point, so they rarely match what a report means. Calculate the intervals from the raw data and use custom error bars instead.

More on charts & images

More on charts & images →