Use Excel’s Forecast Function to Turn Historical Data Into Predictions
Pinterest Whatsapp
Advertisement

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 How to Do Basic Data Analysis in Excel How to Do Basic Data Analysis in Excel Excel isn't meant for data analysis, but it can still handle statistics. We'll show you how to use the Data Analysis Toolpak add-in to run Excel statistics. Read More . 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 How to Use Excel to Calculate Between Dates How to Use Excel to Calculate Between Dates How many days are there between January 9, 2015 and June 12, 2017? Stop struggling when Excel can do the hard work for you! Read More .

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.

excel forecast function historical data predictions

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.

Simple Forecasting

To get started on your Excel forecast, highlight your data, then go to Data > Forecast Sheet.

excel forecast function historical data predictions

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.

excel forecast function historical data predictions

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

excel forecast function historical data predictions

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.

excel forecast function historical data predictions

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.

excel forecast function historical data predictions

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.

excel forecast function historical data predictions

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.

excel forecast function historical data predictions

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:

excel forecast function historical data predictions

And here’s what it looks like after I changed the seasonality to 4:

excel forecast function historical data predictions

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.

excel forecast function historical data predictions

If you check Include forecast statistics, Excel will give you additional basic statistics in your final sheet. Here are the statistics you’ll get:

excel forecast function historical data predictions

If you don’t know what these statistics mean, don’t worry about them.

excel forecast function historical data predictions

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.

excel forecast function historical data predictions

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.

excel forecast function historical data predictions

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 Power Up Excel with 10 Add-Ins to Process, Analyze & Visualize Data Like a Pro Power Up Excel with 10 Add-Ins to Process, Analyze & Visualize Data Like a Pro Vanilla Excel is amazing, but you can make it even more powerful with add-ins. Whatever data you need to process, chances are someone created an Excel app for it. Here's a selection. Read More . 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!

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Parker
    February 19, 2018 at 7:33 pm

    Do you know if or when this will be available for mac excel?