Understanding Time Series Forecasting in Excel

What is time series forecasting, and how does Excel make this task easy to do?

Time series forecasting is all about using existing data to make predictions about future events. Just as meteorologists can predict the path of a hurricane by its current path, you can use forecasting to spot trends in the data and make an educated guess as to where that data is headed.

Three-day cone of uncertainty forecast of Hurricane Rita path as of 10 PM CDT, September 22, 2005. (Image: public domain)

Three-day cone of uncertainty forecast of Hurricane Rita path as of 10 PM CDT, September 22, 2005. (Image: public domain)

In this article, you will learn about Excel’s built-in features that allow you to forecast (or extrapolate) from time series data to predict future values.

How to Create a Time Series

You would create a time series in pretty much the same way that you would enter data for any purpose in Excel. What makes a time series different than other data series is that the values correspond to different points in time.

For example, suppose you wanted to track the number of students in a particular class from semester to semester over a number of years. Then you would create a time series for enrollment.

Let’s open a blank Excel spreadsheet and fill in some data!

Time series data

At this point, you could make a line or bar chart for this data if you wanted to. (For a refresher on making charts, you can check out: How to Make a Column Chart in Excel or How to Create Graphs in Excel.) However, our purpose is to create a time series forecast. In other words, we want to use the historic data to predict future data.

Trends, Seasonality, and Noise

This example shows a lot of variability in the data. Enrollment jumps from hundreds of students down to only dozens and back. So how can we make any predictions at all? The key is in teasing apart the underlying trend from the seasonal variation and random noise.

The theory of time series states that the data Yt is equal to the product of the trend Tt, the seasonal variation factors St, and a random noise factor Nt.

That is, Yt = Tt × St × Nt

In order to predict future values, we need to isolate the trend. Then the trend can be extrapolated by a simple linear regression. Finally, seasonality can be factored back into the data.

Using Excel’s Forecast Sheet

Fortunately, Excel does all the heavy lifting for us!

From the Data tab, click Forecast Sheet.

time series forecasting in Excel

Time Series Forecast

If you click on Forecast Sheet without choosing any data, it will state that “Forecasting can’t be created.” Just click on the word “Options.” There, you can choose the Timeline Range and Values Range.

In our example, you will use the numerical time values found in column A as your timeline range and the enrollment data found in column D as values range.

Time series forecast

The time series forecast is computed with uncertainty based on the specified confidence interval.

Click create, and that’s it! Excel will reformat your data to include a few extra lines of forecasted values, including confidence bounds based on the confidence interval specified in Options.

Completed time series forecast