Forecasting in Excel Using FORECAST Function & Auto Fill Handle Tool

This article is aimed to show you two forecasting methods that you can use to forecast in Excel.

If you own or operate a business, this is a common scenario that you are worried about the future performance of your business. In this article, I will show you two techniques that you can use to get some idea about the future performance of your business if the current trend continues.

I have two worksheets in the workbook that you are seeing in the following image. The worksheets are Trend and Forecast. In the image, you are seeing Trend worksheet is open and you see the year 2005 to the year 2024 under the Year column. Under the Sales column, you are seeing sales data for years 2005 to 2015. And you want to get an idea about the sales in the years from 2016 to 2024.

Forecasting in Excel Image 1

Example of Sales Data

Forecasting Methods in Excel

You can perform forecast job in Excel in multiple ways. I am naming some here:

  1. Using FORECAST(…) function: I will show this technique in this article.
  2. Using TREND(…) function: Read this article to learn more.
  3. Using LINEST(…) function: Read this article to learn more.
  4. Adding a Trend Line to your chart: Read this article to learn more.
  5. Using Auto Fill Handle Tool: This technique will be discussed here.
  6. Using Analysis ToolPak: Read this article to learn more.
  7. Even manually: Read this article to learn more.

There might be more methods to forecast in Excel. If you know any, please put the method in the comments box. I will add that when I will update this article.

My motto is to make your job easier. So, I am showing you two easy techniques that are easy and fast to provide you the future performance of your business.

  1. Using Auto Fill Handle Tool
  2. And Using Excel’s FORECAST(…) function.

Forecasting Using Auto Fill Handle Tool

Download the workbook to follow along me with this article.

At first, I am going to show you how you can use Excel’s Auto Fill Handle tool to forecast future results.

I hope you know how to use Excel’s Fill Handle tool.

But for them who don’t know how to use it, here is the process:

  1. At first, select the sales data for the years 2005 to 2015.
  2. When this data range is selected, you will find a little green square box at the bottom right corner of the selection. This square box is actually the fill handle tool. This tool is used to fill cells in a worksheet. That is why it is named as Fill Handle Tool.
    Forecasting in Excel Image 2

    Auto Fill Handle Tool in read circle

  3. Hover your mouse pointer over the square box and you see the mouse pointer turns into a black and sharp plus sign. Click and hold your mouse pointer and drag down.
    Forecasting in Excel Image 3

    Dragging down clicking on the Auto Fill Handle Tool

  4. I release the mouse pointer when it reaches the year 2024. The blank cells are filled with new data. This new data is actually the extended series of the old data series.
    Forecasting in Excel Image 4

    You get the forecast data.

From the overall data, it is apparent that the sales data has an upward trend. I create an XY chart using this data, you see from the chart that the data is in the uptrend. Though there are some trend breakouts in some places; but the overall trend is up.

Forecasting in Excel Image 6

Created Chart from the Above Sales Data.

Now I shall show you another technique and that is using Excel’s forecast function.

FORECAST(…) Function in Excel

Before showing you how to use the Forecast(…) function in Excel to get future results, at first get introduced to the function. In the image below, you see Forecast(…) function’s Description and Syntax.

Forecast Function in Excel

Forecast(…) function’s Description and Syntax.

Forecasting in Excel Using Excel’s Forecast(…) Function

Now open the Forecast worksheet in the workbook. You will find the data of Distance vs. Amount Spent.

Forecasting in Excel Image 7

Forecast worksheet in the workbook.

Think that you have a grocery store in one corner of your town. And your hypothesis is: when customers come from longer distance shop more. You see from the table that, the distance is going higher and the amount spent is also increasing. So my hypothesis is supported by this data.

Now you see from the table that you have survey data from people who came to your store from 50 miles away, then the data jumps to 75 miles. So you might want to guess if a customer comes from 60 or 65 miles of distance, how much he might spend in your store.

In this case, you can use Excel’s forecast function. First argument of Forecast function is x, then known y’s and then known x’s. If you create an XY chart using this data, you know that Distance values will be the independent variables, I mean the X values of the chart. And Amount Spent values will be the dependent variables, I mean the Y values of the chart.

The x argument means actually a single value from the Distance column, x can be 20, 25 or anything else; 25 is not in the Distance column, but you can use 25 as x’s value; so you can use any value as x but x will be a single value. As known y’s, values from the Amount Spent column will be used. Then as known_x’s; as this argument you will use all the values from the Distance column, as they are x values or dependent variables.

In Cell E5, I insert the forecast function, as x value, I will use the value of the cell D5, so I select it; now as known_y’s values, I select the cells from B2 to B27. As known-x’s values, I select the Distance values; the formula will be as the following image.

Forecasting in Excel Image 10

Inserted formula in Cell E5.

Now press return. The cell will be showing 22.43 (change the cell format to number).

Cell D5 has no value, so it is showing 22.43.

Input some value in cell D5. At first, I input 20, the amount the customer might spend is 45. And from the data table, you see that 20 has two values 50 and 25. At 22 miles you get the amount spent is 40. So, the average can be easily taken as 45.

Then I input 60, and I get 90.15 value. From the zone of data (50, 50, 50 and 75 values in the Distance column), it is possible that if a customer comes from 60 miles of distance, he might spend 90 dollars.

Let’s check another value. Say the customer is coming from 127 miles. 127 is between 125 and 130. I input 127 and I get result 166 approximately. From the data, 125 and 130, you can guess that if the customer is coming from 127 miles of distance, he might spend 166 dollars.

Forecasting in Excel Image 11

When the input value is 127, the forecast formula outputs 165.

So this is how using Excel’s fill handle tool, you can create a series of future values and using forecast function you can get an idea of the future value for a single data point. I hope in real life, you will able to use these techniques to analyze data.

This is a sample lecture of my course: Data Analysis in Excel with Statistics: Get Meanings of Data where you will learn Data Analysis with 52 case studies, problems, and their solutions!

Read More: 

Use Scatter Chart in Excel to Find Relationships between Two Data Series

 Excel is a superb tool to analyze data

 How Excel Handle Charts, Embedded Charts and Chart Sheets


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply