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

Add a Trendline to an Excel Chart with Python

Add linear, moving-average, polynomial and exponential trendlines to Excel line and column charts with openpyxl — forecast periods ahead, show the equation and R², style the line, and put the same fitted numbers into cells with SLOPE, INTERCEPT and RSQ so the trend can be read by people and by code.
Charts & imagesopenpyxl

A monthly orders chart with a noisy line leaves readers guessing whether things are improving. A trendline answers it: a straight line fitted through the points, optionally extended a few periods ahead, with its equation and R² on the chart. Excel computes trendlines itself when it draws the chart, so a trendline added from Python stays correct as the data changes. This guide, part of Creating Charts in Excel with openpyxl, adds and styles trendlines on a line chart, chooses between trend types, and puts the fitted numbers in cells where both people and code can use them. Trendlines on scatter charts are covered in Create a Scatter Chart in Excel with openpyxl.

Monthly orders with a linear trend Twelve months of orders rising from 120 to 188 with small ups and downs. The linear trend rises about 6.5 orders a month from 121 in the first month to 192 in the twelfth. Orders Linear trend 0 50 100 150 200 M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12

Prerequisites

Bash
pip install openpyxl numpy

Twelve months of orders and a line chart:

Python
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.trendline import Trendline

orders = [120, 132, 128, 141, 150, 149, 162, 171, 168, 180, 191, 188]
wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["Month", "Orders"])
for i, value in enumerate(orders, start=1):
    ws.append([f"M{i:02d}", value])

chart = LineChart()
chart.x_axis.delete = False  # Excel hides axes unless this is set
chart.y_axis.delete = False
chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=13), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=13))
series = chart.series[0]
series.smooth = False

series.smooth = False is worth setting explicitly on every line series: openpyxl leaves the setting out of the file unless you do, and LibreOffice then drew the line smoothed in our test — curves that suggest values between points that were never measured.

Step 1 — A linear trendline with equation and R²

Assign a Trendline to the series. dispEq and dispRSqr put the fitted equation and the R² value on the chart; forward extends the line a number of periods beyond the data:

Python
series.trendline = Trendline(trendlineType="linear", dispEq=True, dispRSqr=True, forward=3)
chart.title = "Orders with trend"
ws.add_chart(chart, "D2")
wb.save("trend.xlsx")

Rendering the workbook shows the equation 6.4825x + 114.53 and R² 0.9707: about six and a half more orders each month, and a line that explains 97% of the month-to-month variation. The three forward periods continue the line past M12. The label shows every decimal Excel computed; openpyxl can attach a number format to it through trendlineLbl, but LibreOffice ignored it in our test, which is one more reason to put the figures in cells (Step 4).

Choosing a trendline type A linear trend suits steady growth by a similar amount each period. A moving average smooths noise without assuming a shape. A polynomial of order 2 follows one bend, such as growth that slows. An exponential trend suits growth by a similar percentage each period and needs strictly positive values. Assumes Use for linear same amount per period steady growth movingAvg (period) no shape smoothing noise poly (order 2) one bend growth that slows exp same % per period compounding; values > 0

Step 2 — Style the trendline

A trendline's default style is a thin line in the series colour, easily mistaken for data. Give it its own colour and a dash through spPr:

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

series.graphicalProperties.line.solidFill = "4338CA"
series.trendline.spPr = GraphicalProperties(ln=LineProperties(solidFill="B91C1C", prstDash="dash", w=19050))
series.trendline.name = "Linear trend"
wb.save("trend.xlsx")

w is the width in EMUs — 19,050 is 1.5 points. Naming the trendline sets the legend entry; without a name, Excel and LibreOffice show "Linear (Orders)". Dashed and coloured differently from the series, the trend reads as an annotation rather than a second measurement. More chart styling is covered in Style Chart Colours and Data Labels with openpyxl.

Other types need one extra argument. A moving average needs a period, a polynomial an order; an exponential trend needs every value to be positive:

Python
smooth_chart = LineChart()
smooth_chart.x_axis.delete = False
smooth_chart.y_axis.delete = False
smooth_chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=13), titles_from_data=True)
smooth_chart.series[0].smooth = False
smooth_chart.series[0].trendline = Trendline(trendlineType="movingAvg", period=3,
                                             dispEq=False, dispRSqr=False)
curve = Trendline(trendlineType="poly", order=2, dispEq=True)
print(curve.trendlineType, curve.order)          # poly 2
ws.add_chart(smooth_chart, "D20")
wb.save("trend.xlsx")

A three-period moving average starts at the third point and follows the data more closely than a straight line, which makes it a smoother rather than a model — there is no equation to show, so switch the labels off explicitly. A second-order polynomial bends once and suits growth that is levelling off, but it extrapolates badly: keep forward short or off. Pick the type that matches how the business actually behaves; a higher R² from a more flexible curve is not a better explanation.

Step 4 — Put the fitted numbers in cells

The equation on a chart cannot be read by code, and people cannot copy it into a calculation. Write the same statistics as formulas next to the data, and store their values — computed with numpy — so every reader sees them, as explained in Store Formula Results with xlsxwriter So Every Viewer Shows Values:

Python
import numpy as np

x = np.arange(1, len(orders) + 1)
slope, intercept = np.polyfit(x, orders, 1)
r_squared = np.corrcoef(x, orders)[0, 1] ** 2
forecast_15 = slope * 15 + intercept
print(round(slope, 4), round(intercept, 2), round(r_squared, 4), round(forecast_15, 1))
# 6.4825 114.53 0.9707 211.8

ws["D1"], ws["E1"] = "Trend per month", "=SLOPE(B2:B13,ROW(B2:B13)-1)"
ws["D2"], ws["E2"] = "Intercept", "=INTERCEPT(B2:B13,ROW(B2:B13)-1)"
ws["D3"], ws["E3"] = "R squared", "=RSQ(B2:B13,ROW(B2:B13)-1)"
wb.save("trend.xlsx")

numpy's fit over x = 1…12 matches the chart's equation exactly, because Excel numbers the points of a category chart 1, 2, 3 and so on. ROW(B2:B13)-1 produces the same 1…12 inside the formulas; recalculating the saved file in LibreOffice gives 6.4825, 114.53 and 0.9707, the same figures as the chart. The forecast for month 15 — the end of the three forward periods — is about 212 orders. openpyxl stores formulas without results; with openpyxl the cached values are not written, so for readers that do not recalculate, write the numbers themselves in an adjacent column or build the workbook with XlsxWriter.

Trendline on the chart, numbers in the sheet Build the line chart with smoothing off, attach a trendline of the right type with forward periods, style it as a dashed annotation, then compute the same fit in Python and write slope, intercept and R squared into cells so the trend can be quoted and checked. Line chart smooth = False Trendline type, forward Style dashed red Fit in Python numpy.polyfit Numbers in cells SLOPE, RSQ

The same chart with XlsxWriter

If the report is written with XlsxWriter rather than openpyxl, the trendline is an option of the series, with the same choices under slightly different names:

Python
import xlsxwriter

book = xlsxwriter.Workbook("trend_xw.xlsx")
sheet = book.add_worksheet("Orders")
sheet.write_row("A1", ["Month", "Orders"])
for i, value in enumerate(orders, start=1):
    sheet.write_row(i, 0, [f"M{i:02d}", value])
xw_chart = book.add_chart({"type": "line"})
xw_chart.add_series({
    "name": "Orders", "categories": ["Orders", 1, 0, 12, 0], "values": ["Orders", 1, 1, 12, 1],
    "smooth": False,
    "trendline": {"type": "linear", "forward": 3, "display_equation": True,
                  "display_r_squared": True, "name": "Linear trend",
                  "line": {"color": "#B91C1C", "dash_type": "dash", "width": 1.5}},
})
sheet.insert_chart("D2", xw_chart)
book.close()
print("written")

XlsxWriter takes colours with a # and widths in points, and a moving average is {"type": "moving_average", "period": 3}. Because XlsxWriter builds new files only, use it when the whole report is generated; use openpyxl when adding a chart to an existing workbook.

Common pitfalls

  • Trendlines on stacked or percent charts. Excel does not allow them on stacked series; attach the trend to an unstacked line series instead.
  • Exponential fits with zeros. An exponential trend cannot be fitted to data containing zero or negative values — Excel does not offer it for such data. Use linear or a moving average.
  • Reading R² as proof. A high R² on a time series mostly says the data trends; it does not say why, or that the trend will continue. Present the trend as a description of the past.
  • Forecasting too far. A trend fitted on twelve points says little about month thirty. Keep forward to a few periods and say it is an extrapolation.
  • Dates as categories. If the categories are real dates, Excel may use the date serial numbers as x values, which changes the equation's intercept. Reproduce that in Python, or use period numbers.

Conclusion

Add a trendline by assigning Trendline(trendlineType=…) to a chart series: linear for steady change, movingAvg with a period for smoothing, poly with an order for one bend, exp for compounding growth. Show the equation and R² with dispEq and dispRSqr, extend with forward, style it as a dashed annotation, and set smooth=False on the series. Put the same fit into cells with SLOPE, INTERCEPT and RSQ so the trend can be quoted, checked and used.

Frequently asked questions

How do I add a trendline to an openpyxl chart? Assign a Trendline to the series, for example series.trendline = Trendline(trendlineType="linear", dispEq=True, dispRSqr=True). Excel fits the line itself when the chart is drawn, so it follows the data if values change.

How do I extend a trendline into the future? Set forward to the number of periods to project, such as forward=3. The line continues past the last data point; on a line chart with category labels the extra periods are drawn without labels.

Which x values does Excel use for a trendline on a line chart? On a line or column chart with text categories, Excel uses 1, 2, 3 and so on for the points. The equation therefore describes the value per period, which you can reproduce with numpy.polyfit over range(1, n + 1).

Can I read the trendline equation from Python? Not from the chart; Excel calculates it only when drawing. Compute the same fit in Python, or write SLOPE, INTERCEPT and RSQ formulas into cells so the figures are visible and usable in the sheet.

More on charts & images

More on charts & images →