Excel FORECAST Function – Extrapolate Data Easily

The FORECAST function in Excel makes it super easy to predict upcoming values when presented with a series of existing inputs.

When conducting a forecast, Excel does so via a linear regression model. All we need to do is provide x values, y values, and the x value that we want to test and Excel will do the rest for us.

The FORECAST Function Formula

To start with, let’s take a look at the formula itself that Excel uses to conduct a forecast. The syntax is as follows:

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

The three required fields are:

  • x: The point of data that we want to forecast a value against
  • known_y’s: The dependant range of data
  • known_x’s: The independant range of data

Depending on what version of Excel you are using you may only see the =FORECAST formaula. This was replaced with FORECAST.LINEAR in the 2016 version, with the previous formula set to eventually be phased out.

Example of Extrapolating by Forecasting in Excel

Our example data set here is quite a simple one. We have week commencing periods in the left column, and total sales in the right column. We have also left 5 future weeks blank which we will be looking to forecast the values of:

Using the above syntax, our formula will now be as follows:

=FORECAST.LINEAR(B13, C3:C12,B3:B12)

When we will this in the forecasted value for the week of August 27th appears as below:

Calculating the rest of the values in theory is relatively straight forward, but we do have two ways of doing so. We could simply drag down the formula in C13 down to C17 which works fine, but as you will see from the below screenshot, this also drags down the reference cells:

This technically still works fine, and also means that we are always consistent in using 10 weeks worth of data for every single forecast calculation.

The alternative would be to manually correct the formula each time by either dragging up the purple and red highlights, or just typing in the exact cells we want to reference.

A third approach is making sure we only ever use ‘real’ data so every single time we do this formula we use the new X value, but make sure to use rows 3 through to 12 only every time.

There is no one correct way to do this, and will largely depend on your own preferences based on your knowledge of the data and trends found, but as we can see below each of the three results in a slightly different value as the weeks go on:

In this example, the left column is using our first formula where we just drag down the formula and let it use the previous 10 weeks regardless of if the contents of the cells are real or forecasted. The second column locks the known x and known y values at cells B3 – B12 and C3 – C12. While the third formula uses a ‘full’ dataset each time – we start at row 3 and go all the way to the bottom even if the most recent values are also forecasts.

How do we Visualize the Forecast?

Next up let’s take a quick look at how we would go about visualizing our forecasted data.

If we simply highlight our data and create a line chart normally we are presented with the below output:

This is fine, but its not exactly clear which is real data and which values are the forecasted ones. Luckily this is very easy to change.

All we need to do is move our forecasted values into a new column like below:

Now to create a chart highlight the entire table, and head over to recommended charts under the insert tab and select the type of chart you are after. In our example we are going for a scatter chart with lines:

Now when we press OK and add a little bit of formatting to the headers and date ranges we are presented with the below:

Much easier to work with thanks to the break between data points and the two distinct colours!

That is all there is to cover on the basics of using the Excel FORECAST function. The power of this function does go further with other variations of the formula that were introduced in Excel 2016, however those will be covered in separate guides which will be found here.

Similar Posts