How to Forecast in Excel Based on Historical Data (4 Suitable Methods)

Excel has awesome tools and functions to predict future values. It has a Forecast button, introduced in Excel 2016 version, FORECAST, and other functions both for linear and exponential data. In this article, we will see how to use these Excel tools to forecast future values based on historical data.


Download Practice Workbook

You can download the following workbook for your own exercise. We have used some authentic data here and cross-checked if the results produced by the methods in this article match the real values.


What Is Forecasting?

Formally, Forecasting is an approach that uses historical data as inputs to generate educated predictions about future trends. Forecasting is often used by businesses to decide how to allocate their resources or plan for anticipated expenses in the future.

Forecast Based on Historical Data

Suppose you are planning to start a business with a certain product. I am not a businessman, but I guess one of the first things you want to know about the product will be its current and future demand in the market. So, there is a matter of prediction, estimation, educated guess, or “foretelling” the future. If you have adequate data that somehow follow a trend, you can reach close enough to a perfect projection.

However, you cannot predict with 100% accuracy no matter how much past and present data you have and how perfectly you’ve identified the seasonality. So before making any final decision, you have to double-check the results and consider other factors as well.


4 Methods to Forecast in Excel Based on Historical Data

In this article, we have taken the price data of Crude Oil (Petroleum) from the World Bank’s website, for the last 10 years (April 2012 to March 2022). The following picture shows the list partially.

Crude Oil Price for the Last 10 Years


1. Use ‘Forecast Sheet’ Button in Excel 2016, 2019, 2021 and 365

The Forecast Sheet tool was first introduced in Excel 2016, which makes time series forecasting a cinch. Simply organize the source data accurately, and Excel will take care of the rest. You have to follow just two simple steps.

📌 Step 1: Arrange Data with Time Series and Corresponding Values

  • First, set the time values in the left column in ascending order. Arrange the time data at a regular interval, i.e., daily. Weekly, Monthly, or yearly basis.
  • Then set the corresponding prices in the right column.

📌 Step 2: Create Forecast Worksheet

  • Now, go to the Data tab. Then click on the Forecast Sheet button from the Forecast group.

Use ‘Forecast Sheet’ Button in Excel to Forecast Based on Historical Data

The Create Forecast Worksheet window will open up.

  • Now, choose the graph type from the window.
  • You can also choose the end date of the forecast.

Create Forecast Worksheet

  • Finally, press the Create button. You are done!

Now a new worksheet will be opened in Excel. This new sheet contains our present data together with the anticipated values. There’s a graph too that represents the original and predicted data visually.

Customizing Forecast Graph:

You can customize the Forecast graph in the following ways. Look at the following image. Excel provides us with many customizing options here.

1. Chart Type

There are two options here, Create a column chart and Create a line chart. Use either of these which you find visually more comfortable.

2. Forecast End

Set here the timing when you want to end the forecast.

3. Forecast Start

Set the forecast starting date with this.

4. Confidence Interval

Its default value is 95%. The less it is, the more confidence in predicted values it indicates. You can mark or unmark this checkbox depending on the necessity of showing the accuracy level of your forecast.

5. Seasonality

Excel tries to find out seasonality in your historical data if you select the ‘Detect automatically’ option. You can also set it manually by putting a suitable value.

6. Timeline Range

Excel automatically sets it when you select any cell within the data. Besides, you can change it from here as per your need.

7. Values Range

You can edit this range in a similar fashion.

8. Fill Missing Points Using

You can choose either interpolation, or you can set the missing points as zeros. Excel can interpolate the missing data (if you choose to) if it is less than 30% of the total data.

9. Aggregate Duplicates Using

Choose the suitable calculation method (Average, Median, Min, Max, Sum, CountA) when you have multiple values at the same timestamp.

10. Include Forecast Statistics

You can add a table with info on smoothing coefficients and error metrics by selecting this checkbox.


2. Use Excel Functions to Forecast Based on Previous Data

You can also apply Excel functions such as FORECAST, TREND, and GROWTH, to forecast based on previous records. Let’s see them one by one.

2.1 Utilize FORECAST Function

MS Excel 2016 replaces the FORECAST function with FORECAST.LINEAR function. So, we will use the more updated one (for future compatibility issues with Excel).

Syntax of FORECAST.LINEAR Function:

=FORECAST.LINEAR(x, knows_ys, known_xs)

Here, x stands for the target date, known_xs stands for the timeline, and known_ys stands for known values.

📌 Steps:

  • First off, insert the following formula in cell C17.

=FORECAST.LINEAR(B17,C5:C16,B5:B16)

  • Then hit ENTER.

Use Excel Functions to Forecast Based on Previous Data

Read More: FORECAST Function in Excel (with other Forecasting Functions)


2.2 Apply TREND Function

MS Excel also helps with the TREND function to forecast based on historical data. This function applies the Least Square method to foretell future values.

Syntax of TREND Function:

=TREND(known_ys, [known_xs], [new_xs], [const])

In the case of the following dataset, enter the formula below in cell C125 to get forecast values for April, May, and June 2022. Then hit ENTER.

=TREND(C5:C124,B5:B124,B125:B127)

Use Excel Functions to Forecast Based on Previous Data


2.3 Utilize GROWTH Function

The GROWTH function works using exponential relation while the TREND function (used in the previous method) works with linear relation. Other than that, both are identical with respect to arguments and application.

So, to get forecast values for April, May, and June 2022, insert the following formula in cell C125. Then hit ENTER.

=GROWTH(C5:C124, B5:B124,B125:B127)

Use Excel Functions to Forecast Based on Previous Data

Read More: How to Forecast Growth Rate in Excel (2 Methods)


Similar Readings


3. Use Moving Average and Exponential Smoothing Tools

There are two types of Moving Averages used in statistics. Simple and Exponential Moving Averages. We can use them also to foretell future values.

3.1 Use Simple Moving Average

To apply the Moving Average technique, let’s add a new column named ‘Moving Average’.

Now apply the following steps.

📌 Steps:

  • First of all, go to the Data tab and click on the Data Analysis button. If you don’t have it, you can enable it from here.
  • Then select the Moving Average option from the list and press OK.

Use Moving Average Methods to Forecast Based on Previous Records

  • The Moving Average window appears.
  • Now, select Input Range as C5:C20, put Interval as 3, Output Range as D5:D20 and mark Chart Output checkbox.
  • After that, Press OK.

Use Moving Average Methods to Forecast Based on Previous Records

You can see the forecast value for April 2022 in cell D20.

Besides, the following image is a graphical representation of forecast results.


3.2 Apply Exponential Smoothing

For more accurate results, you can use the Exponential Smoothing Technique. The procedure of applying this in Excel is quite similar to that of Simple Moving Average. Let’s see.

📌 Steps:

  • First, go to the Data tab >> click on the Data Analysis button >> select Exponential Smoothing from the list.
  • Then press OK.

Use Exponential Smoothing

The Exponential Smoothing window will pop up.

  • Now Set Input Range as C5:C20 (or as per your data), Damping factor as 0.3, and Output Range as D5:D20; mark the Chart Output checkbox.
  • Then press the OK button.

Inputs of Exponential Smoothing Window

After pressing OK, you will get the result in cell D20.

And look at the following graph to understand the forecast visually.


4. Apply Fill Handle Tool to Forecast Based on Historical Data

If your data follow a linear trend (increasing or decreasing), you can apply the Fill Handle tool to get a quick forecast. Here is our data and corresponding graph.

Apply Fill Handle Tool to Forecast Based on Historical Data

These suggest that the data has a linear trend.

Assuming that we want to forecast for April, May, and June 2022. Follow the following quick steps.

📌 Steps:

  • First, select the values C5:C16 and hover your mouse cursor in the bottom right corner of cell C16. The fill handle tool will appear.
  •  Now drag it till cell C19.

Apply Fill Handle Tool to Forecast Based on Historical Data

The following graph shows the results visually. It’s noticeable that Excel ignores the irregular values (e.g. sudden hike in Mar-22), and considers the values that are more regular.

Apply Fill Handle Tool to Forecast Based on Historical Data


How Accurately Can Excel Forecast?

A question may arise in mind, “How Much Accurate Are Excel Forecast Techniques?” The answer is not simple. Because forecasting depends on many factors. For example, when we were working on this article, the Ukraine-Russia war began, and the Crude oil prices rose suddenly much higher than it was expected.

So, it depends on you, i.e., how perfectly you are setting up the data for forecasting. However, we can suggest you a way to check the perfectness of the methods.

Here, we have data for April 2012 to March 2022. If we forecast for the last several months and compare the results with the known values, we will know how firmly we can depend on it.

How Accurately Can Excel Forecast?

Read More: How to Calculate Forecast Accuracy Percentage in Excel (4 Easy Methods)


Conclusion

In this article, we have discussed 4 methods to forecast in excel based on historical data. If you have any questions regarding them, please let us know in the comment box. For more such articles, visit our blog ExcelDemy.


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo