How to Forecast in Excel:
Analyze and Predict the Future

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Forecasting is important in businesses as it helps determine possible trends in the future

In days past, it’s difficult to do forecasting by paper since forecasting involves a lot of computations especially when there are lots of variables. Buying forecasting software also cost a bit.

Fortunately, Excel makes forecasting easier and quicker. Plus, you’re better able to understand the data by visualizing them using Excel charts.

In this article, we’ll be showing you 3 methods on how to forecast in Excel. We included an exercise file so you’ll be able to practice on it.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get into it! 🧐

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

What is forecasting?

In business, forecasting is defined as a tool that helps businessmen cope with the future’s uncertainty. It heavily relies on past & current data and analysis of trends.

In a sense, forecasting allows businessmen to make educated guesses on some areas of their businesses. With forecasting, they’re able to make necessary adjustments based on forecasted situations and scenarios.

Some of the advantages of forecasting in business include:

  • Sense of direction
  • Keeps businesses up-to-date
  • Learning from past mistakes and experience
  • Improve budgeting

But despite the advanced technologies, theories, and tools we’re using today to forecast, these “educated guesses” can still go wrong.

Forecasting doesn’t tell you what the future is. Instead, it shows you possibilities that might happen.

Kasper Langmann, Co-founder of Spreadsheeto

Forecasting using Excel

Microsoft Excel is a powerful spreadsheet software you can use to store, make calculations, and visualize data.

Forecasting is just one of Excel’s features and functions. 

There are 3 commonly used quantitative forecasting methods in Excel:

  • Moving averages
  • Exponential smoothing
  • Linear regression

We’ll show you how to use each one step-by-step. 

But bear in mind, none of these methods can certainly show you the future. You’ll have to treat the results of the forecast as probabilities.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

Moving Averages

The moving average method is simply the average of a subset of numbers which is ideal in smoothing out the trend in data such as in a time-series.

By getting the average of subsets, you’re able to better understand the trend long-term.

Out of all forecasting methods, this is the easiest to understand.

Let’s say you have this sales data from October 2019 to July 2019:

dataset for forecasting via moving average

In this example, you can calculate moving averages for a 2-month, 3-month, or even 5-month period. What matters is the period is the same all throughout.

Kasper Langmann, Co-founder of Spreadsheeto

For now, let’s go with getting a 3-month moving average for this data set.

There are 3 ways to get the moving average in Excel:

  • Manual average function
  • Moving Average Trendline Option
  • Moving Average tool from the Analysis ToolPak

Manual ‘AVERAGE’ function

Since a moving average is the average of a subset of numbers, you can use the ‘AVERAGE’ function to quickly compute the average of each subset.

Here’s the syntax of the ‘AVERAGE’ function:

=AVERAGE(number1, [number2]…)

Parameters:

  • ‘number1’ – A numerical value or cell reference to numeric values
  • ‘number2’ – optional; another numerical value or cell reference to numeric values

Because we’re doing a 3-month moving average, we need to compute the averages on each of the 8 subsets:

  • Oct 2018 – Dec 2018
  • Nov 2018 – Jan 2019
  • Dec 2018 – Feb 2019
  • Jan 2019 – Mar 2019
  • Feb 2019 – Apr 2019
  • Mar 2019 – May 2019
  • Apr 2019 – Jun 2019
  • May 2019 – Jul 2019

To calculate the first subset, all you have to do is apply the formula and fill it out with the correct cell references:

=AVERAGE(B3:B5)

partial average

We place the result inline with the third month (the last value of the first subset), and not on the first one, to avoid confusion.

Kasper Langmann, Co-founder of Spreadsheeto

To fill out the rest of the subset, simply drag the cursor handle downwards of the filled-in cell to copy the formula:

copy moving average by dragging cursor handle down

If you would like to chart your moving average with your existing data (sales), the ‘Line with Markers’ is the best choice.

First, highlight your data including the labels. Then, click ‘Insert’ from the tab list.

insert option on the tab list

On the ‘Charts’ group, click the ‘Line’ icon and choose ‘Line with Markers’ from the list:

You’ll immediately see your chart. Feel free to name it and change its design:

moving average line chart

Moving Average Trendline Option

Interestingly, you can also chart your data directly and add the moving average on the chart itself.

Take note that with this method, you’re only able to see the moving average as a trendline, and not the individual moving averages themselves.

Kasper Langmann, Co-founder of Spreadsheeto

To start, make a line chart of your data:

  • Highlight your data
  • Go the the ‘Insert’ tab
  • On the ‘Charts’ group, click the ‘Line’ chart icon
  • On the dropdown list, choose ‘Line’ under the ‘2-D Line’ group

To add the Moving Average trendline:

  • Click on your line chart
  • Click the plus (+) icon found on the top-right of your chart
  • Click the right arrow beside the ‘Trendline’
  • On the dropdown list, click ‘More Options…’
more options under trendline on chart elements

After that, a sidebar will slide from the right.

Click ‘Moving Average’ from the ‘Trendline Options’. Adjust the ‘Period’ to the desired period, which in this example is ‘3’.

You’ll then notice a trendline which shape is similar to the moving average line we got from the Manual ‘AVERAGE’ function section.

adding a moving average trendline on a chart

Moving Average from the Analysis ToolPak

Moving Average is one of the tools found in Excel’s Analysis ToolPak.

Before you’re able to use it, you have to load the toolpak first.

Here’s a summary of how you can add the analysis toolpak:

  • Click ‘File’ on the tab list
  • Click ‘Options’ on the bottom of the left-hand sidebar
  • Click ‘Add-ins’ on the left sidebar of the window
  • Click ‘Go’ to manage the ‘Excel Add-ins’
  • Check the ‘Analysis ToolPak’
  • Click ‘OK’
loading the analysis toolpak in 5 clicks

To use the ‘Moving Average’ tool, click ‘Data’ from the tab list:

data from the tab list

On the ‘Analysis’ group, click the ‘Data Analysis’ icon.

data analysis button on analysis category

Click ‘Moving Average’ from the list and click ‘OK’.

moving average tool on analysis toolpak

There are lots of options in the tool.

  • ‘Input Range’: Range of cells to average
  • ‘Labels in First Row’: Check if data labels were included in the ‘Input Range’
  • ‘Interval’: The period of moving average
  • ‘Output Range’: Where to place the results
  • ‘New Worksheet Ply’: Place the results on a new worksheet
  • ‘New Workbook’: Place the results on a new workbook
  • ‘Chart Output’: Create a chart out of the results and input
  • ‘Standard Errors’: Include standard errors
moving average dialogue box

For our example, let’s adjust the options as shown below:

  • ‘Input Range’: $B$2:$B$12
  • ‘Labels in First Row’: Check
  • ‘Interval’: 3
  • ‘Output Range’: $C$3
  • ‘Chart Output’: Check
using the moving average tool from analysis toolpak

After clicking ‘OK’, you’ll immediately see the results plus the chart!

moving average analysis tool results plus chart

Exponential Smoothing

Like moving averages, exponential smoothing uses past data to forecast possible futures.

Exponential smoothing is another time-series forecasting method which assigns exponentially decreasing weights on older observations. 

It follows then that new or recent data are given more weight in forecasting compared to older data.

Kasper Langmann, Co-founder of Spreadsheeto

Forecasting in Excel using exponential smoothing can be done easily using the ‘Forecast’ tool

Under the hood, it uses the ‘FORECAST.ETS’ function. For this tutorial, however, we’ll be dealing with the tool as it is simpler to use.

Since we’re forecasting, it’s important that our data are summarized in consistent time periods. As example, our sales data is summarized twice a month:

forecasting via exponential smoothing dataset

To use the forecast tool, highlight your data including the labels and click ‘Data’ from the tab list:

data from the tab list

Then, under the ‘Forecast’ group, click the ‘Forecast Sheet’ icon:

forecast sheet icon on the ribbon

A window will pop up about creating a forecast worksheet.

In here, you can choose when the forecast will end by adjusting the date as well as the chart that will be used to visualize the forecast.

It’s recommended you use the line chart since you’re able to see the movement of the forecasted data including the confidence intervals.

Kasper Langmann, Co-founder of Spreadsheeto
create forecast worksheet window

If you like more options, you can click the ‘Options’ button before the ‘Forecast End’.

options on create forecast worksheet window

Here are the additional options:

  • ‘Forecast Start’ – the forecasting start date
  • ‘Confidence Interval’ – the interval in which future predictions are expected to be fall; the default is 95% which means that 95% of the predicted values are expected to fall within the range
  • ‘Seasonality’ – the seasonal pattern where fluctuations are expected to occur
  • ‘Include Forecast Statistics’ – the result will display additional statistical information about the forecast
  • ‘Timeline Range’ – the range of the timeline series
  • ‘Values Range’ – the range of the value series of your data
  • ‘Fill Missing Points Using’ – Dictates how to handle the missing points
  • ‘Duplicate Aggregate Usings’ – Determine how multiple values are calculated

If you’re satisfied with the settings, click ‘Create’ on the bottom-right of the window.

Your results will be shown on a new worksheet:

forecast results using exponential smoothing

Linear Regression

Lastly, the linear regression forecasting method relies on a past variable to predict its future equivalent.

It determines the linear relation between the timeline series and the value series like a causal model.

Kasper Langmann, Co-founder of Spreadsheeto

When doing a linear regression forecasting in Excel, you need to use the ‘FORECAST.LINEAR’ function with the syntax:

=FORECAST.LINEAR(x, known_y’s, known_x’s)

Parameters:

  • ‘x’ – the target date for which you would like to predict a value
  • ‘known_y’s’ – the timeline series range
  • ‘known_x’s’ – the value series range

Suppose you have data like shown below which constitutes this year’s sales data by month and you would like to have a forecast on possible sales next year:

dataset for forecasting using linear regression

All you have to do is use the formula and supply the appropriate references.

Let’s try this on the first one, ‘Jan-20’:

=FORECAST.LINEAR(A15,$B$3:$B$14,$A$3:$A$14)

Notice that we’re using absolute cell references on the timeline and value series ranges so we’ll have no problems copying the formula down.

initial forecast result using linear regression

Now, drag the cursor handle down to copy the formula:

copying the linear regression formula by dragging the cursor handle

If you would like to visualize your forecast, the first thing you have to do is copy the last historical sales value (Dec-19) to the forecast column to achieve a continuous uninterrupted line:

copying the value for linear regression

Then, follow these steps:

  • Highlight all your data including the labels
  • Go to the ‘Insert’ tab (from the tab list)
  • On the ‘Charts’ group, click the ‘Line’ icon
  • Click ‘Line’, the first choice under ‘2-D Line’
inserting chart for linear regression

And voila! There’s your linear regression line chart.

Wrapping things up…

Learning how to forecast using Excel is valuable especially if you have a business. And if you’re the type who likes to forecast, you’re able to save money by knowing how to do it yourself with Excel.

At first, you may find some of the methods confusing. If you’re stuck, you’re welcome to download our exercise file and follow the steps described in this tutorial step-by-step. 😊

Kasper Langmann, Co-founder of Spreadsheeto