Create a forecast in Excel for Windows

Excel for Microsoft 365 Excel 2019 Excel 2016

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..