How do you forecast demand in Excel or Google Sheets?

To forecast demand in a spreadsheet, total each item's units by month, then average the most recent months for a moving-average forecast. Excel desktop can also project monthly totals with FORECAST.ETS or the Forecast Sheet button, which detect seasonality. Google Sheets has no FORECAST.ETS, so use a moving average, FORECAST.LINEAR or TREND, and adjust seasonal items by hand.

How do you turn sales lines into monthly totals?

Forecasts need one number for each item and month, with every month present, including months with no sales.

1. On the sales tab, add a Month column: =DATE(YEAR(B2), MONTH(B2), 1) turns each sale date into the first of its month.
2. Build a pivot table with items as rows, months as columns and the sum of quantity as values. In Excel, select the data and choose Insert > PivotTable. In Google Sheets, choose Insert > Pivot table.
3. Copy the result to a new tab and fill any blank months with 0, so every item has the same run of months.

Use 24 to 36 months if you have them. One year is enough for an average; two or more years show whether a peak repeats.

How does a moving average forecast work?

A moving average uses the mean of the last few months as next month's forecast. With the most recent three months in columns K to M, the forecast is =AVERAGE(K2:M2).

• 3 months reacts quickly to change but jumps around.
• 6 to 12 months is steadier but slow to notice a trend.

For most stock items a 12-month average is a sound default, with a shorter one for items that are clearly growing or shrinking. Divide by the days in the month when your reorder sheet works in daily sales.

How do you use FORECAST.ETS in Excel?

FORECAST.ETS predicts a future value from history using exponential smoothing. Microsoft lists it for Excel for Microsoft 365, 2024, 2021 and 2019, and says it is not available in Excel for the web, iOS or Android.

With months in A2:A37 and units in B2:B37, the forecast for the month in A38 is =FORECAST.ETS(A38, B2:B37, A2:A37, 12). The last argument sets a 12-month seasonal pattern; leave it out, or use 1, to let Excel detect seasonality, or use 0 for no seasonality.

Microsoft's rules matter here:

• The timeline needs a consistent step, such as the first of every month, or the function returns #NUM!.
• By default, missing points are filled with the average of their neighbours, and up to 30% of points can be missing.
• The Forecast Sheet button does the same for a whole series: on the Data tab, in the Forecast group, select Forecast Sheet. It adds forecast columns built on FORECAST.ETS and confidence bounds built on FORECAST.ETS.CONFINT.

What can Google Sheets use instead?

Google's function list has FORECAST (also called FORECAST.LINEAR), TREND and GROWTH, but no FORECAST.ETS. All three fit a straight or exponential trend; none of them models seasonality.

• FORECAST: =FORECAST(A38, B2:B37, A2:A37) projects the month in A38 along the linear trend of the history.
• Seasonal adjustment by hand: for each calendar month, divide its average over past years by the overall monthly average to get a seasonal index, then multiply your moving average by next month's index.

Where does a spreadsheet forecast break down?

• Seasonality with little history. When you set seasonality by hand, Microsoft advises having at least two full cycles of history, so two years for a yearly peak.
• Stockout months. Zero sales because the shelf was empty are not zero demand. Leave those months out or replace them.
• Lumpy items. An item that sold 50 in one order and nothing else looks like steady demand. Averages and ETS both mislead here.
• Thousands of items. One formula per item is easy; checking each result by eye is not. Sort by forecast change and review the big movers.

Can a tool forecast every item for you?

ReorderOwl is one option. Upload your item list and sales history and it forecasts each item from its own sales pattern, then returns a reorder workbook of what to order and how much, showing how each number was worked out. It works in ChatGPT today; Claude setup is coming shortly. It is free to start. See ReorderOwl for spreadsheets.

Frequently asked questions

Does Google Sheets have FORECAST.ETS?

No. Google's function list includes FORECAST, FORECAST.LINEAR, TREND and GROWTH, which fit trends but not seasonality.

Does FORECAST.ETS work in Excel for the web?

No. Microsoft says it is not available in Excel for the web, iOS or Android.

How many months of history do I need?

Twelve months for an average. For seasonality, at least two full cycles, so two years for a yearly pattern.

Which moving average length should I use?

Twelve months is a steady default. Use three to six months for items with a clear trend.

How do I handle months when the item was out of stock?

Leave them out of the average, because the zero reflects missing stock rather than missing demand.

Sources

1. FORECAST.ETS function, Microsoft Support, accessed 2026-10-01
2. Create a forecast in Excel for Windows, Microsoft Support, accessed 2026-10-01
3. Create a PivotTable to analyze worksheet data, Microsoft Support, accessed 2026-10-01
4. FORECAST, Google Docs Editors Help, accessed 2026-10-01
5. Google Sheets function list, Google Docs Editors Help, accessed 2026-10-01
6. Create and use pivot tables, Google Docs Editors Help, accessed 2026-10-01