Forecast a Time Series with Python in Excel

Excel has had a forecasting function for years — FORECAST.ETS — but it is a black box with few options, and charts of its output take manual work. Python in Excel puts pandas and statsmodels inside the workbook itself: a =PY() cell can read a table of monthly sales, fit a seasonal model, and return next year's forecast as a range that other formulas and charts can use. The code runs in Microsoft's cloud, so nothing needs installing, and the workbook stays a normal file that colleagues open in Excel. This page walks through a monthly sales forecast step by step. Because =PY() cells only run inside Excel, the snippets here were tested outside it with a small stand-in for the xl() function, which is shown first. It belongs to Using Python in Excel.

Prerequisites

Python in Excel is available in Microsoft 365 (Excel for Windows, Mac and the web) on supported plans. In the workbook, put the history in an Excel table named SalesHistory with two columns, Month (real dates, first day of each month) and Sales. The sample data covers 36 months from January 2023 to December 2025 with a rising trend and a clear seasonal pattern. Outside Excel, the code below runs with pandas, statsmodels and matplotlib and the stand-in xl() function defined above.

Step 1 — Read the table and set a monthly index

In a new cell, type =PY( to enter Python mode, then:

df = xl("SalesHistory[#All]", headers=True)
history = (df.assign(Month=pd.to_datetime(df["Month"]))
             .set_index("Month")["Sales"]
             .asfreq("MS"))
print(history.tail(3))
print("months:", len(history), "| gaps:", int(history.isna().sum()))

xl("SalesHistory[#All]", headers=True) returns the table, header row included, as a DataFrame — the Python in Excel equivalent of reading a sheet with pandas, described in Reference Cells, Ranges and Tables with xl() in Python in Excel. Forecasting models need a regular time index, so the month column becomes a DatetimeIndex and asfreq("MS") declares it monthly (month start). If a month were missing, asfreq would insert it as NaN — and the gap count makes that visible before the model silently misreads the seasonality.

Step 2 — Fit a seasonal model

Holt-Winters exponential smoothing handles a trend and a repeating yearly pattern, which describes most monthly business series:

from statsmodels.tsa.holtwinters import ExponentialSmoothing

model = ExponentialSmoothing(history, trend="add", seasonal="add", seasonal_periods=12).fit()
forecast = model.forecast(12).round(0)
print(forecast.head(6))

trend="add" lets the level grow by a roughly constant amount each month; seasonal="add" adds a fixed pattern that repeats every 12 months. The model needs at least two full seasons of history — 24 months — to estimate the pattern; three years, as here, is more comfortable. Use seasonal="mul" when the seasonal swing grows with the level (December being 30% above average rather than 200 units above). The fitted model is a Python object; the forecast is a pandas Series with the next twelve month-start dates as its index.

Step 3 — Add a simple uncertainty band

A forecast without any sense of its error invites false precision. A pragmatic band uses the spread of the model's in-sample errors:

residual_sd = (history - model.fittedvalues).std()
band = 1.96 * residual_sd * np.sqrt(np.arange(1, 13) / 3).clip(min=1)
result = pd.DataFrame({
    "Month": forecast.index,
    "Forecast": forecast.values,
    "Low": (forecast.values - band).round(0),
    "High": (forecast.values + band).round(0),
})
print(result.head(3).to_string(index=False))
result

The band starts at about ±2 residual standard deviations and widens for months further out, a rough but honest stand-in for a full prediction interval. For formal intervals, statsmodels' state-space ETSModel provides get_prediction(...).summary_frame(). The print output goes to the cell's diagnostics pane in Excel; the last line, a bare result, is what the =PY() cell returns: Python in Excel uses the value of the cell's final expression.

Step 4 — Return the forecast to the grid

A =PY() cell can return its result in two forms, switched with the output menu next to the formula bar (or Ctrl+Alt+Shift+M): as a Python object, shown as a card that other Python cells can reference, or as an Excel value, which spills the DataFrame into the grid as an ordinary range. Choose Excel value here: the twelve forecast rows spill below the cell with their headers, and regular formulas can refer to them — =SUM(...) over the forecast column gives next year's total, and a chart can plot actuals and forecast together. Dates come back as real Excel dates; give the column a date format such as mmm yyyy. Returning values and objects is covered in Return DataFrames and Values from Python in Excel Cells.

Step 5 — Chart actuals and forecast

A matplotlib figure returned from a =PY() cell appears as an image you can place over the grid:

import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(7, 3))
ax.plot(history.index, history.values, label="Actual", color="#5B5CF0")
ax.plot(result["Month"], result["Forecast"], label="Forecast", color="#0F9488")
ax.fill_between(result["Month"], result["Low"], result["High"], color="#0F9488", alpha=0.15, label="Band")
ax.set_title("Monthly sales and 12-month forecast")
ax.legend(loc="upper left")
fig.tight_layout()
fig

Returning fig as the last expression displays the chart; in Excel the image can be resized and moved like any picture, and it refreshes when the cell recalculates. The general technique is in Plot Charts with matplotlib in Python in Excel. If the people reading the workbook prefer native Excel charts, chart the spilled range from Step 4 instead.

Step 6 — Compare with FORECAST.ETS

Excel's own FORECAST.ETS(target_date, values, timeline, seasonality) also uses exponential smoothing and needs no Python at all. It is a reasonable baseline and a good cross-check:

check = pd.DataFrame({
    "Month": result["Month"].dt.strftime("%b %Y"),
    "Python forecast": result["Forecast"],
})
check["Excel formula to compare"] = [
    f"=FORECAST.ETS(DATE({d.year},{d.month},1),SalesHistory[Sales],SalesHistory[Month],12)"
    for d in result["Month"]
]
print(check.head(3).to_string(index=False))

Put those formulas next to the spilled forecast: the two will be close but not identical, because Excel's implementation (AAA exponential smoothing with its own parameter estimation) differs in detail from statsmodels' Holt-Winters fit. Large differences usually point to a data problem — a gap, an outlier, a month typed as text — that one method tolerates and the other does not.

Python earns its place when you need what the formula cannot do: choosing the model, inspecting its parameters, multiplicative seasonality, or adding explanatory variables.

Step 7 — Test the method on a year you already know

Before trusting next year's numbers, check how the same method would have done on the last year of history: fit on the first 24 months, forecast the next 12, and compare with what actually happened:

train, test = history[:24], history[24:]
backtest = ExponentialSmoothing(train, trend="add", seasonal="add", seasonal_periods=12).fit().forecast(12)
mape = ((backtest - test).abs() / test).mean() * 100
print(f"average absolute error on 2025: {mape:.1f}%")

On the sample data the method misses each 2025 month by a few percent on average — a figure worth stating next to the forecast, because it tells readers how much weight the numbers can bear. If the error is large, try multiplicative seasonality or a simpler model and keep whichever does best on the held-out year.

Common pitfalls

• Irregular dates. Months stored as text or with gaps break the seasonal model; set a monthly index and check for NaN.
• Too little history. A 12-month season needs at least 24 months of data.
• Returning a Python object. Switch the cell to Excel value output to spill the forecast into the grid.
• Recalculation surprises. =PY() cells recalculate with the workbook; a long-running fit makes editing slow, so consider manual calculation for heavy models.
• Expecting local resources. Code runs in Microsoft's cloud with a fixed set of packages and no access to local files or the internet, as listed in Fix Python in Excel Errors.

Conclusion

Python in Excel turns a sales table into a forecast without leaving the workbook: read it with xl(), set a monthly DatetimeIndex, fit a Holt-Winters model with trend and seasonality, add an uncertainty band, and return a DataFrame as an Excel value so it spills into the grid for formulas and charts. Plot actuals and forecast with matplotlib if you like, and keep FORECAST.ETS alongside as a quick cross-check.

Frequently asked questions

Can I forecast in Excel with Python? Yes. In Microsoft 365, a =PY() cell can read a table with xl(), fit a model with statsmodels such as ExponentialSmoothing, and return the forecast as a DataFrame that spills into the grid.

How do I return a forecast DataFrame to Excel cells from Python in Excel? Make the DataFrame the last expression in the =PY() cell and switch the cell's output from Python object to Excel value. The DataFrame then spills into the sheet as a range with headers.

How much history does a seasonal forecast need? At least two full seasons — 24 months for monthly data with a yearly pattern — so the model can estimate the seasonal effect; more history gives more stable estimates.

How does Python's forecast compare with FORECAST.ETS? Both use exponential smoothing, so results are usually close but not identical, because the implementations estimate parameters differently. Python lets you choose the model type, inspect parameters and use multiplicative seasonality.

Related

• Up one level: Using Python in Excel — Python inside Microsoft 365 Excel.
• Run a Regression with Python in Excel — statsmodels for explanatory models.
• FORECAST, TREND and SLOPE Equivalents in Python — linear forecasting functions.
• Calculate Moving Averages with pandas rolling — smoothing without a model.