A line or scatter chart shows how the data moves, but the direction is left for the reader to judge: will next month keep climbing or fall back? Is the swing across two quarters one trend or two? A trendline draws that direction as a mathematical curve, and the regression equation and R² turn the judgement into a number.

In Excel, adding a trendline usually means right-clicking the series, choosing Add Trendline, picking a type, and ticking the box for the equation. When a chart is generated by a script, all of that can be written into the code. This article uses Spire.XLS for Python to add trendlines of different types to chart series, display the regression equation and fit quality, and read the equation back after saving.

Setting Up the Environment

Install Spire.XLS and import the modules:

pip install Spire.XLS 
from spire.xls import * from spire.xls.common import *

Creating the Base Chart

Start with six months of sales data and a line chart with data markers. A trendline attaches to an existing series, so the chart has to exist first:

from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.CreateEmptySheets(1) sheet = workbook.Worksheets[0] months = ["Jan.", "Feb.", "Mar.", "Apr.", "May.", "Jun."] planned = [3.3, 2.5, 2.0, 3.7, 4.5, 4.0] sheet.Range["A1"].Value = "Month" sheet.Range["B1"].Value = "Planned" for i in range(len(months)): sheet.Range[i + 2, 1].Value = months[i] sheet.Range[i + 2, 2].NumberValue = planned[i] # Create a line chart from column B chart = sheet.Charts.Add(ExcelChartType.LineMarkers) chart.DataRange = sheet.Range["B1:B7"] chart.SeriesDataFromRange = False chart.ChartTitle = "Planned Sales" # Assign the category axis labels chart.Series[0].CategoryLabels = sheet.Range["A2:A7"] workbook.SaveToFile("BaseChart.xlsx", ExcelVersion.Version2013) workbook.Dispose()

CreateEmptySheets(1) creates one blank worksheet, and Charts.Add(ExcelChartType.LineMarkers) adds a chart to it that reads from the range given by DataRange. Setting SeriesDataFromRange = False means the series name and category labels are supplied separately instead of being inferred from the first row and column of the range.

Adding a Linear Trendline with Its Equation

Trendlines are added through a series' TrendLines collection; the argument to Add() is the trendline type. The code below continues on the BaseChart.xlsx created above:

from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("BaseChart.xlsx") chart = workbook.Worksheets[0].Charts[0] # Add a linear trendline to the first series trendline = chart.Series[0].TrendLines.Add(TrendLineType.Linear) # Show the regression equation and R² on the chart trendline.DisplayEquation = True trendline.DisplayRSquared = True workbook.SaveToFile("LinearTrendline.xlsx", ExcelVersion.Version2013) workbook.Dispose()

With DisplayEquation on, a label such as y = 0.32x + 2.2133 appears on the chart; DisplayRSquared adds the R² value, which measures how closely the points follow the line. Both are off by default and must be enabled explicitly.

Other Trendline Types

TrendLineType offers five types: Linear, Logarithmic, Exponential, Polynomial, and Moving_Average. A single series can carry several trendlines, which makes it possible to compare how different models fit the same data:

from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("BaseChart.xlsx") series = workbook.Worksheets[0].Charts[0].Series[0] # Add one of each type series.TrendLines.Add(TrendLineType.Linear) series.TrendLines.Add(TrendLineType.Logarithmic) series.TrendLines.Add(TrendLineType.Exponential) series.TrendLines.Add(TrendLineType.Polynomial) series.TrendLines.Add(TrendLineType.Moving_Average) workbook.SaveToFile("TrendlineTypes.xlsx", ExcelVersion.Version2013) workbook.Dispose()

Each type assumes something about the data: a logarithmic trendline needs positive x values, an exponential trendline needs all y values positive, and a moving average is unsuitable for forecasting beyond the data. A polynomial trendline also has a degree, controlled by the Order property, which defaults to 2:

from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("BaseChart.xlsx") series = workbook.Worksheets[0].Charts[0].Series[0] polynomial = series.TrendLines.Add(TrendLineType.Polynomial) polynomial.Order = 3 workbook.SaveToFile("PolynomialTrendline.xlsx", ExcelVersion.Version2013) workbook.Dispose()

The higher the degree, the more closely the curve follows the points, but a high degree also fits the noise. Starting at 2 and raising it only when the fit is visibly insufficient is usually the safer choice.

Forecasting Forward and Backward

Beyond describing existing data, a trendline can extend along the category axis in either direction. Forward and Backward are measured in category units:

from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("LinearTrendline.xlsx") trendline = workbook.Worksheets[0].Charts[0].Series[0].TrendLines[0] # Extend 0.5 units backward and forecast 2 units forward trendline.Backward = 0.5 trendline.Forward = 2 workbook.SaveToFile("ForecastTrendline.xlsx", ExcelVersion.Version2013) workbook.Dispose()

The values are floats measured in category units. Backward accepts values from 0 to 0.5 only and throws when it exceeds that range, while Forward has no such cap. Backward extension only makes sense where historical data exists; how far forward a forecast is useful depends on how stable the data is, since the error grows with the distance from the observed range.

Naming Trendlines and Formatting Labels

By default a trendline is unnamed. When several share a series, giving them names and a consistent number format makes the labels and equations easier to tell apart:

from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("LinearTrendline.xlsx") trendline = workbook.Worksheets[0].Charts[0].Series[0].TrendLines[0] # Name the trendline and format its label to two decimal places trendline.Name = "Baseline" trendline.DataLabel.NumberFormat = "#,##0.00" workbook.SaveToFile("TrendlineLabel.xlsx", ExcelVersion.Version2013) workbook.Dispose()

Name is written into the trendline node of the chart XML and shown as part of the trendline label, while DataLabel.NumberFormat controls how the numbers in the equation and R² are displayed.

Reading the Equation Back

The equation is stored in the file as well, so a later script can read it for reporting or verification. Load the file and take the string from the Formula property:

from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("TrendlineTypes.xlsx") series = workbook.Worksheets[0].Charts[0].Series[0] for i in range(series.TrendLines.Count): trendline = series.TrendLines[i] try: print(trendline.Type, "->", trendline.Formula) except Exception: # A moving average has no regression equation  print(trendline.Type, "-> no equation") workbook.Dispose()

The output resembles:

TrendLineType.Linear -> y = 0.32x + 2.2133 TrendLineType.Logarithmic -> y = 0.7006Ln(x) + 2.5651 TrendLineType.Exponential -> y = 2.3001e^(0.0954x) TrendLineType.Polynomial -> y = 0.1196x^2 - 0.5175x + 3.33 TrendLineType.Moving_Average -> no equation

A moving average is the average of a few neighbouring points joined together, not a regression model, so it has no equation; calling Formula on it raises an exception. Check the type or guard with a try/except when reading in bulk.

Practical Tips

  • Build the series first: TrendLines hangs off Series, so the chart and its data range must exist before a trendline is added.
  • Enable the labels explicitly: DisplayEquation and DisplayRSquared are off by default; nothing appears unless they are turned on.
  • Respect each type's assumptions: logarithmic needs positive x, exponential needs positive y, or Excel draws a blank or incorrect trendline.
  • Start with a low degree: use Order = 2 for polynomial trendlines and raise it only when the fit is visibly insufficient.
  • Moving averages cannot forecast: there is no equation and no meaningful Forward value.
  • Handle exceptions when reading: only regression trendlines expose Formula; branch on the type in batch code.
  • Release resources: call Dispose() on each workbook when done.

Conclusion

This article walked through adding trendlines to Excel charts from Python: create a line chart with category labels, add linear, logarithmic, exponential, polynomial, or moving-average trendlines through Series.TrendLines.Add(), show the equation and R² with DisplayEquation and DisplayRSquared, extend the line with Forward and Backward, distinguish several trendlines with Name and DataLabel.NumberFormat, and read the equation back through Formula after loading the file. Written into a report generation script, these steps let a chart do more than display data — it states the trend in numbers.