Forecasting in Excel

Excel’s forecasting functions help you make predictions about linear data. They’re quick and easy to use. Let’s learn how to forecast with Microsoft Excel.

How to Forecast in Microsoft Excel

Excel’s original FORECAST function, now replaced with FORECAST.LINEAR, helps you predict values based on trends. For example, let’s consider a list of annual sales. You have data for 2018, 2019, and 2020, and you want to predict 2021 and 2022’s sales.

It’s helpful to work in a table like the one above. You’ll want to place your cursor in Cell C5 in the Forecast column. Hit = to begin a formula, then type FORECAST.LINEAR(.

Excel asks for three inputs: X,known_y’s, and known_x’s.X is the value you need a prediction for. Here, that will be 2021, so click into cell A5, then type a comma.

The known-y’s are dependent values. In this case, those are the sales numbers. Click and drag to select the range B2:B4. Type another comma.

So far, your formula is:

=FORECAST.LINEAR(A5,B2:B4,

Finally, you’ll need to find the known-x’s, which are the years. Click and drag to select the range A2:A4, then close the parentheses. Your full forecast formula reads:

=FORECAST.LINEAR(A25,B2:B4,A2:A4)

Hit Enter, and you’ll see Excel return 2021’s forecast in cell C5.

Now, of course, we also want to find a forecast value for 2022. This will go into cell C6. Now, thanks to Excel, you don’t have to retype the forecast formula or perform repetitive steps.

To run 2022’s forecast, first click into and hover over the corner of cell C5. You’ll see a black + sign appear. Next, simply click and drag downward into cell C6.

The FORECAST.LINEAR formula will copy down, adjusting to provide a 2022 forecast.

As you can see, Excel makes it easy to perform forecasts when you have data with a linear trend.