Shade a Target Band Behind a Line Chart

A line chart of a weekly measure answers "how is it moving?" but not "is it where it should be?". When the measure has a target range rather than a single target — on-time delivery should be between 95% and 98%, stock cover between two and four weeks, call answer time under a ceiling but above a floor — a shaded band behind the line answers the second question at a glance. Points inside the band are fine; the weeks where the line leaves it are the ones to talk about. Excel has no "band" option on a line chart, but it can draw one by combining a stacked area chart with a line chart on the same axes, and openpyxl can build that combination. This page does so for a weekly on-time delivery figure, drives the band from two input cells, tidies the legend and lists the weeks outside the band beside the chart. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

pip install openpyxl pandas

LibreOffice is used at the end to render the chart and check it; it is optional.

How the band is drawn

A stacked area chart draws its second series on top of its first. If the first series is the band's lower limit and is made invisible, and the second is the band's width — upper limit minus lower — then the visible part is exactly the strip between the two limits. A line chart with the actual values is then combined with the area chart so both share one set of axes. Because everything is an ordinary Excel chart, readers can restyle it, and it follows the data when the sheet changes.

Step 1 — Data, band limits and helper columns

from openpyxl import Workbook

weeks = [f"W{n}" for n in range(32, 44)]
on_time = [94.1, 95.3, 96.8, 93.2, 91.5, 95.9, 97.4, 98.6, 96.2, 94.8, 95.5, 97.9]

wb = Workbook()
ws = wb.active
ws.title = "Delivery"
for ref, label, value in [("F", "Target low", 0.95), ("G", "Target high", 0.98)]:
    ws[f"{ref}1"], ws[f"{ref}2"] = label, value
    ws[f"{ref}2"].number_format = "0%"

ws["A1"], ws["B1"], ws["C1"], ws["D1"] = "Week", "On time", "Band low", "Target band"
for i, (week, pct) in enumerate(zip(weeks, on_time), start=2):
    ws.cell(i, 1, week)
    ws.cell(i, 2, pct / 100).number_format = "0.0%"
    ws.cell(i, 3, "=$F$2")                    # lower limit on every row
    ws.cell(i, 4, "=$G$2-$F$2")               # band width on every row
last = len(weeks) + 1
print(f"{len(weeks)} weeks, band {ws['F2'].value:.0%}–{ws['G2'].value:.0%}")

The two limits live in their own cells, Target low and Target high, and the helper columns refer to them with formulas. Changing the target range for next quarter is then a matter of typing two numbers; the band in the chart moves with them. The helper columns repeat the same value on every row because a chart series needs one value per category. If the target changes partway through the period — a tighter target from week 40, say — the helper columns can hold different values on different rows, and the band will step accordingly, which is a neat way to show a target change on the chart itself.

Step 2 — The area chart for the band, the line chart for the values

from openpyxl.chart import AreaChart, LineChart, Reference
from openpyxl.chart.legend import LegendEntry

band_chart = AreaChart()
band_chart.grouping = "stacked"
band_chart.add_data(Reference(ws, min_col=3, max_col=4, min_row=1, max_row=last), titles_from_data=True)
band_chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=last))
base, band = band_chart.series
base.graphicalProperties.noFill = True                 # invisible lower part
base.graphicalProperties.line.noFill = True
band.graphicalProperties.solidFill = "D9EAD3"          # light green band
band.graphicalProperties.line.noFill = True

line_chart = LineChart()
line_chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=last), titles_from_data=True)
actual = line_chart.series[0]
actual.graphicalProperties.line.solidFill = "1F5FA8"
actual.graphicalProperties.line.width = 28575          # 2.25 pt
actual.marker.symbol = "circle"
actual.marker.size = 6
actual.smooth = False

band_chart += line_chart                               # same axes, line drawn on top

The area chart is created first and the line chart is added to it with +=, which puts both on the same category and value axes; the series added last is drawn on top, so the line is never hidden by the band. The lower series has both its fill and its outline switched off, so it takes up space in the stack without being seen. The band uses a pale fill so it reads as background — the line is the data, the band only context. Green suits a "good range"; if the band marks a danger zone, a pale red or grey says so instead.

Step 3 — Axes, legend and placement

band_chart.title = "On-time delivery against the target band"
band_chart.y_axis.scaling.min, band_chart.y_axis.scaling.max = 0.88, 1.0
band_chart.y_axis.majorUnit = 0.02
band_chart.y_axis.number_format = "0%"
band_chart.x_axis.delete = band_chart.y_axis.delete = False
band_chart.legend.position = "b"
band_chart.legend.legendEntry = [LegendEntry(idx=0, delete=True)]      # hide "Band low"
band_chart.width, band_chart.height = 18, 9
ws.add_chart(band_chart, "A16")
wb.save("on_time_delivery.xlsx")
print("saved")

The value axis is cropped to 88–100% so a few points of movement are visible; on a 0–100% axis every week would look the same. The axis is set on the area chart, which owns the axes of the combined chart. The legend would list three entries — Band low, Target band and On time — and the first means nothing to a reader, so it is removed by index, leaving Target band to label the shading. Both axes are explicitly kept visible, because openpyxl otherwise writes charts whose axes Excel hides.

Step 4 — List the weeks outside the band, and render the chart

low, high = 0.95, 0.98
outside = [(w, p / 100) for w, p in zip(weeks, on_time) if not low <= p / 100 <= high]
ws["F4"] = "Weeks outside the band"
for r, (week, value) in enumerate(outside, start=5):
    ws.cell(r, 6, week)
    ws.cell(r, 7, value).number_format = "0.0%"
    ws.cell(r, 8, "below" if value < low else "above")
wb.save("on_time_delivery.xlsx")
print(outside)

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

A short table of the weeks outside the band, placed next to the chart, turns the picture into an action list: four weeks below the range — W32, W35, W36 and W41 — and one above it, W39 at 98.6%. Being above an upper limit is not always bad news, but a band with an upper limit says it is worth a look; very high on-time figures can mean deliveries are being promised too late. Rendering the workbook to an image with LibreOffice confirms the construction: the pale band sits between 95% and 98%, the lower helper is invisible, and the line passes in and out of the band where the table says it does.

Bands, single targets and tolerances

A band is the right picture when there is a genuine range of acceptable values. When there is a single target with a tolerance — 95% plus or minus one point — the same construction works with the limits set to target minus and plus the tolerance. When there is a single target and no tolerance, a horizontal target line is cleaner than a band, and when the question is "how far from target is each week?", a variance chart or conditional formatting on the table says more than a band. The cell-based version of the same idea, colouring values outside a tolerance, is covered in Highlight Values Outside a Tolerance of Target with openpyxl.

Common pitfalls

• A visible lower series. Switch off both its fill and its outline.
• Clustered instead of stacked. The band only sits on the lower limit when grouping = "stacked".
• Line drawn under the band. Add the line chart to the area chart, not the other way round.
• Band limits typed into helper rows. Drive them from two input cells with formulas.
• A 0–100% axis. Crop the value axis so movements around the band are visible.

Conclusion

To shade a target band behind a line in Excel with openpyxl, add a stacked area chart whose first series is the lower limit with no fill and whose second is the band width in a light colour, then add a line chart of the actual values to it with += so both share the axes. Drive the limits from two input cells, remove the helper from the legend, crop the value axis, and list the periods outside the band next to the chart.

Frequently asked questions

How do I add a shaded range to a line chart in Excel? Combine a stacked area chart — an invisible series at the lower limit and a coloured series of the band width — with the line chart on the same axes.

How do I combine an area chart and a line chart in openpyxl? Build both charts, then add the line chart to the area chart with area_chart += line_chart before adding it to the sheet.

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

Can the band change partway through the period? Yes; give the helper columns different values on different rows and the band steps where the target changes.

Related

• Up one level: Creating Charts in Excel with openpyxl — native Excel charts from Python.
• Add a Line Chart to an Excel Report with Python — line chart basics.
• Create an Area Chart with openpyxl — stacked areas.
• Build a Monthly KPI Scorecard with Targets — targets in a report.