How to Use Forecast Sheet in Excel to Predict Future Values

Step-by-step

Forecast Sheet predicts future values, such as sales, inventory needs or demand, from your historical data in a few clicks. Excel builds a new sheet with a table of past and predicted values and a chart. The example uses US housing data from the St. Louis Federal Reserve, 2000 to 2024.

1. Lay out the data

Put the dates in one column and the values directly to their right. The dates should be evenly spaced, for example monthly.

2. Open Forecast Sheet

Click any cell in the data and go to Data > Forecast Sheet.

3. Choose a line or column chart

A line chart (the default) shows the trend as a continuous flow. A column chart puts more emphasis on individual values.

4. Set the options

Click Options to fine-tune the forecast:

• Forecast End: how far into the future to predict.
• Forecast Start: usually after the last real data point. Starting it earlier lets you compare the forecast with actual values and judge its accuracy.
• Confidence Interval: the range in which 95% of future values are expected to fall.
• Seasonality: the length of the repeating pattern, detected automatically. For monthly data with a yearly cycle, it’s 12.

Behind the scenes Excel uses exponential triple smoothing (ETS), which works best when the data has a clear trend and seasonal pattern.

5. Create the forecast

Click Create. Excel adds a new sheet: your data becomes a table with a Forecast column plus Lower and Upper Confidence Bound columns, and the chart shows all three. The real outcome is expected to land between the bounds; the wider they are, the more uncertain the prediction.

Go further

My Excel Statistics & Data Analysis: Zero to Analyst course goes much further into forecasting and data analysis.