Support

Search 
Office support

Products  Devices 

What's new

Create a forecast in Excel for

Install Office

Windows
Account & billing 

Templates

More support 
Excel for Microsoft 365, Excel 2021, More...

If you have historical time-based data, you can use it to create a forecast. When you create
a forecast, Excel creates a new worksheet that contains both a table of the historical and
predicted values and a chart that expresses this data. A forecast can help you predict
things like future sales, inventory requirements, or consumer trends.

Information about how the forecast is calculated and options you can change can be found
at the bottom of this article.
Create a forecast
1. In a worksheet, enter two data series that correspond to each other:

A series with date or time entries for the timeline

 A series with corresponding values

These values will be predicted for future dates.

Note: The timeline requires consistent intervals between its data points. For example,
monthly intervals with values on the 1st of every month, yearly intervals, or numerical
intervals. It’s okay if your timeline series is missing up to 30% of the data points, or has
several numbers with the same time stamp. The forecast will still be accurate. However,
summarizing data before you create the forecast will produce more accurate forecast
results.

2. Select both data series.

Tip: If you select a cell in one of your series, Excel automatically selects the rest of the
data.

3. On the Data tab, in the Forecast group, click Forecast Sheet.

4. In the Create Forecast Worksheet box, pick either a line chart or a column chart for the
visual representation of the forecast.
5. In the Forecast End box, pick an end date, and then click Create.

Excel creates a new worksheet that contains both a table of the historical and predicted
values and a chart that expresses this data.

You'll find the new worksheet just to the left ("in front of") the sheet where you entered
the data series.

Customize your forecast

If you want to change any advanced settings for your forecast, click Options.

You'll find information about each of the options in the following table.

Forecast Options Description

Forecast Start Pick the date for the forecast to begin. When you pick a
date before the end of the historical data, only data prior
to the start date are used in the prediction (this is
sometimes referred to as "hindcasting").
 Tips:

Starting your forecast before the last historical point gives you
a sense of the prediction accuracy as you can compare the
forecasted series to the actual data. However, if you start the
forecast too early, the forecast generated won't necessarily
represent the forecast you'll get using all the historical data.
Using all of your historical data gives you a more accurate
prediction.

If your data is seasonal, then starting a forecast before the

last historical point is recommended.

Confidence Interval Check or uncheck Confidence Interval to show or hide it.

The confidence interval is the range surrounding each
predicted value, in which 95% of future points are
expected to fall, based on the forecast (with normal
distribution). Confidence interval can help you figure out
the accuracy of the prediction. A smaller interval implies
more confidence in the prediction for the specific point.
The default level of 95% confidence can be changed
using the up or down arrows.

Seasonality Seasonality is a number for the length (number of points)

of the seasonal pattern and is automatically detected.
For example, in a yearly sales cycle, with each point
representing a month, the seasonality is 12. You can
override the automatic detection by choosing Set
Manually and then picking a number.

Note: When setting seasonality manually, avoid a value for less

than 2 cycles of historical data. With less than 2 cycles, Excel
cannot identify the seasonal components. And when the
seasonality is not significant enough for the algorithm to detect,
the prediction will revert to a linear trend.

Timeline Range Change the range used for your timeline here. This range
needs to match the Values Range.

Values Range Change the range used for your value series here. This
range needs to be identical to the Timeline Range.

Fill Missing Points Using To handle missing points, Excel uses interpolation,
 meaning that a missing point will be completed as the
weighted average of its neighboring points as long as
fewer than 30% of the points are missing. To treat the
missing points as zeros instead, click Zeros in the list.

Aggregate Duplicates Using When your data contains multiple values with the same
timestamp, Excel will average the values. To use another
calculation method, such as Median or Count, pick the
calculation you want from the list.

Include Forecast Statistics Check this box if you want additional statistical
information on the forecast included in a new worksheet.
Doing this adds a table of statistics generated using the
FORECAST.ETS.STAT function and includes measures,
such as the smoothing coefficients (Alpha, Beta,
Gamma), and error metrics (MASE, SMAPE, MAE, RMSE).

Formulas used in forecasting data

When you use a formula to create a forecast, it returns a table with the historical and
predicted data, and a chart. The forecast predicts future values using your existing time-
based data and the AAA version of the Exponential Smoothing (ETS) algorithm.

The table can contain the following columns, three of which are calculated columns:

Historical time column (your time-based data series)

Historical values column (your corresponding values data series)

Forecasted values column (calculated using FORECAST.ETS)

Two columns representing the confidence interval (calculated using

FORECAST.ETS.CONFINT). These columns appear only when the Confidence Interval is
checked in the Options section of the box..

Download a sample workbook

Click this link to download a workbook with Excel FORECAST.ETS function examples