Use Excel’s Forecast Function to Turn Historical Data Into Predictions
Microsoft can’t predict the future — but it can sure try. Excel’s time series forecasting abilities take data you’ve provided and extrapolate potential future data. It’s not perfect, but it can give you an idea of what your data is really telling you. A tiny glimpse into the future, if you will.
There are two different ways to forecast in Excel. First, you can use several forecast functions directly within the cells of your spreadsheet. The second method, which uses a graphical interface, is easier to use, so we’ll be focusing on that.
If you really want to know how to use the functions, you can check out Microsoft’s guide to Excel forecast functions. But we recommend sticking with the Forecast Sheet button.
How Does Excel Forecast Data?
Before we get started, it’s important to understand what you’re getting when you use Excel to forecast data.
The forecast function uses a process called triple exponential smoothing (ETS) to take your existing data and make predictions based on the patterns present. ETS weights the previous values in your series so that more recent values are more heavily weighted and further past values are less weighted.
Gregory Trubetskoy’s explanation of exponential smoothing is one of the most non-mathematician-readable that I’ve found. It’s a complicated process, but what it comes down to is that the recent values of your series have a strong impact on the predicted values.
Excel offers a number of options for tweaking how ETS works, and we’ll go over those in a moment.
Formatting Your Data for Forecasting
Excel forecast functions require your data to be in a specific format to work correctly. You’ll need at least two columns.
The first is a timeline. This needs to contain dates, times, or a series of numbers that specify temporal change .
The important thing here is that the intervals need to be consistent. You can’t have sales from the first of the January, the first of February, the first of March, the first of April, the fifteenth of April, and the first of May. Though you could replace those dates with numbers, the forecasting might not be as accurate when done this way.
The second column is your data. This is what you’re going to be forecasting into the future.
To provide an example of data forecasting, I created a spreadsheet with fictional monthly sales numbers.
To get started on your Excel forecast, highlight your data, then go to Data > Forecast Sheet.
Once you’ve clicked the Forecast Sheet button, you’ll see a nicely formatted graph that contains your data in blue along with predictive data in orange.
Hit Create in the new window, and you’ll get a more permanent version of that graph alongside a table of predicted values. That table also includes 95% confidence intervals (which are also represented on the graph).
This is already very valuable. But by tweaking some settings, you can customize the data that you get for even better predictions.
Customizing the Excel Forecast
In the Create Forecast Sheet window, you see only a single dropdown menu by default: Forecast End.
The Forecast End date lets you choose how far into the future to forecast. Keep in mind that the further you go, the more error you’re likely to see.
In the image above, you can see an extended forecast, with the confidence interval expanding over time.
To access the other forecasting options, click Options at the bottom of the window.
We’ll go over each option individually.
Forecast Start lets you choose when Excel starts forecasting the data. In almost every case, you’ll want this to be from the very last point in your timeline.
You may want to set it earlier to see if Excel’s predictions match the real data, just to check. But in general, you can safely ignore this option.
Confidence Interval lets you turn the confidence interval lines on or off and choose whether to calculate it. Most of the time, 95 percent is the standard value for the confidence interval.
This means that Excel is 95 percent confident that the predicted value will fall between those two lines.
Seasonality defines the repeating nature of your timeline. Most of the time, Excel will calculate this automatically. In other cases, you’ll want to change it yourself.
In our sample spreadsheet, for example, changing the seasonality to 4 results in much better data (we used 4 because there are four points in each repeating cycle). Here’s what the forecast looked like with automatic seasonality detection:
And here’s what it looks like after I changed the seasonality to 4:
This better reflects the typical downturn in sales around October. It’s worth trying in your own spreadsheet to make sure you’re getting the best data possible.
Just be sure that your seasonality number is accurate. If you’re using monthly data, the seasonality is 12. Weekly data over the course of a year will be 52. If it’s weekly over half a year, use 26.
If you check Include forecast statistics, Excel will give you additional basic statistics in your final sheet. Here are the statistics you’ll get:
If you don’t know what these statistics mean, don’t worry about them.
Timeline Range and Values Range are the columns where you’ve entered your data. You can adjust these if Excel is picking up the wrong data, but you probably won’t need to.
Before we talk about Fill Missing Points Using, we have to talk about missing data in forecasting. The forecast function will work even if you don’t have all of the data points in the timeline. Even if you’re missing some information, Excel will still run the forecast.
The standard method for filling the missing points is interpolation, which uses weighted averages of the missing point’s neighboring values. This is a good option, especially considering it’s your only one.
If you don’t want Excel to interpolate, you can choose Zeroes in the dropdown to insert zeroes in the missing data spaces. But if those missing values aren’t likely to actually be zeroes, you’ll throw off your forecast.
The Aggregate Duplicate Values dropdown concerns a specific case in your data. When there are two entries for a single time point in your data, Excel averages the two to determine the final value. You can change that to count, median, sum, or a variety of other operations.
Once you’ve set all the options the way you want them, hit Create and you’ll get Excel’s best guess at how your data will look in the future, with a graph and a table full of predicted values.
Predict the Future With Excel’s Forecast Function
Now that you know how to forecast in excel based on historical data, you can start using the venerable spreadsheet program to predict the future! Or at least to get an idea of what it might look like based on the past.
Excel’s forecasting isn’t perfect, but it’s a great tool to have in your Excel arsenal . Building a forecast model in Excel is easy and will be a big help when you’re running business statistics.
Do you use Excel’s forecast? What other tips do you have for newcomers to the function? Share your thoughts in the comments below!