How to How to Use Forecast Functions in Excel in Excel - Practical Guide

Learn to use Excel's forecast functions (FORECAST, FORECAST.LINEAR, FORECAST.ETS) to predict future values based on historical data trends. This tutorial covers syntax, practical applications, and when to use each function for accurate business forecasting and data analysis.

Why This Matters

Forecast functions enable data-driven decision-making by predicting sales, inventory, and trends, reducing guesswork in business planning.

Prerequisites

•Understanding of basic Excel formulas and cell references
•Historical data organized in columns (X and Y values)
•Familiarity with the Formulas menu and function syntax

Step-by-Step Instructions

Organize Your Data

Arrange historical data in two columns: X values (independent variable, e.g., months) in one column and Y values (dependent variable, e.g., sales) in another. Ensure data is sorted chronologically and contains no gaps.

Select the Cell for Forecast Result

Click the cell where you want the forecast result to appear, typically adjacent to your data set or in a new forecasting section.

Enter FORECAST.LINEAR Formula

Type =FORECAST.LINEAR(x, known_y's, known_x's) replacing x with the new data point, known_y's with your historical Y range, and known_x's with your historical X range. Example: =FORECAST.LINEAR(13, B2:B12, A2:A12) forecasts month 13 sales.

Use FORECAST.ETS for Seasonal Data

For data with seasonal patterns, use =FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]). This function accounts for trends and seasonal variations automatically.

Press Enter and Validate Results

Press Enter to execute the formula and review the forecasted value. Compare against your known data to ensure accuracy, and copy the formula down to forecast multiple future periods.

Alternative Methods

Use FORECAST (Legacy Function)

The original FORECAST function uses linear regression: =FORECAST(x, known_y's, known_x's). It's simpler but doesn't handle seasonal data.

Create Trendline with Chart

Insert a chart (Insert > Chart), right-click the data series, and select 'Add Trendline' to visually forecast trends and display the equation on the chart.

Use Data Analysis ToolPak

Enable the Analysis ToolPak (File > Options > Add-ins > Analysis ToolPak), then access File > Options > Data > Analysis to perform regression analysis for advanced forecasting.

Tips & Tricks

✓Use at least 10 historical data points for accurate forecasts; more data improves accuracy.
✓FORECAST.ETS works best when your data spans at least two seasonal cycles (e.g., 24 months for monthly data with yearly seasonality).
✓Combine forecast results with confidence intervals to understand forecast reliability and potential variability.

Pro Tips

★Use FORECAST.ETS.CONFINT to calculate confidence intervals alongside your forecast for better decision-making.
★Test multiple forecast functions on a subset of data (hold back recent months) to validate which function performs best before deploying.
★Create a dynamic forecast that updates automatically by using named ranges and absolute references in your forecast formulas.

Troubleshooting

Formula returns #DIV/0! or #VALUE! error

Check that all cell references are correct, data contains only numbers (not text), and X and Y ranges have equal lengths. Remove any empty cells or non-numeric values.

Forecast result seems inaccurate or unrealistic

Verify you have sufficient historical data (minimum 10-24 points), check for outliers that skew the trend, and consider if seasonal patterns are present. Use FORECAST.ETS instead if data is seasonal.

FORECAST.ETS function not recognized

This function requires Excel 2016 or later. If using older versions, switch to FORECAST.LINEAR or the legacy FORECAST function instead.

Related Excel Formulas

FORECAST.LINEAR FORECAST.ETS FORECAST.ETS.CONFINT TREND LINEST RSQ

Frequently Asked Questions

What's the difference between FORECAST, FORECAST.LINEAR, and FORECAST.ETS?

FORECAST is the legacy function using simple linear regression. FORECAST.LINEAR is the modern equivalent with the same functionality. FORECAST.ETS is newer and handles seasonal patterns automatically by using exponential smoothing, making it ideal for data with cyclical trends.

How much historical data do I need for accurate forecasts?

Minimum 10 data points for basic forecasts, but 24+ points (two years of monthly data) are recommended for seasonal forecasts. More data generally improves accuracy; however, very old data may become irrelevant if market conditions change.

Can I forecast multiple periods at once?

Yes, copy your forecast formula down to adjacent cells with incremented x values (e.g., 13, 14, 15 for next three months). Alternatively, use FORECAST.ETS.SEASONALITY and FORECAST.ETS.STAT functions for more advanced multi-period analysis.

What if my data has both trends and seasonal patterns?

Use FORECAST.ETS, which automatically detects and factors in both linear trends and seasonal variations. It's specifically designed for complex data patterns that simple linear regression cannot capture.