How to Use FORECAST in Excel

Key Insights

Excel offers three forecasting functions: legacy FORECAST (still works but deprecated), FORECAST.LINEAR for simple linear trends, and FORECAST.ETS for seasonal data with exponential smoothing
FORECAST.LINEAR requires equal-length arrays of known x and y values, fails with non-numeric data, and works best with clear linear relationships in your historical data
For seasonal patterns like retail sales or website traffic, FORECAST.ETS significantly outperforms linear forecasting by accounting for repeating cycles and can provide confidence intervals

Understanding Excel’s Forecasting Functions

Excel provides powerful built-in forecasting capabilities that most users overlook. Whether you’re predicting next quarter’s revenue, estimating future inventory needs, or projecting customer growth, Excel’s FORECAST functions can save you from manual trend analysis.

The landscape changed with Excel 2016. Microsoft deprecated the original FORECAST function in favor of FORECAST.LINEAR and introduced the FORECAST.ETS family for seasonal data. Here’s what you need to know:

// Legacy syntax (still works, but avoid in new workbooks)
=FORECAST(x, known_y's, known_x's)

// Modern equivalent - identical functionality
=FORECAST.LINEAR(x, known_y's, known_x's)

Both formulas produce identical results. The rename clarifies that this function performs linear regression forecasting. If you’re maintaining old spreadsheets, don’t panic—FORECAST still works. But for new projects, use FORECAST.LINEAR for clarity and future compatibility.

Breaking Down FORECAST.LINEAR Syntax

The FORECAST.LINEAR function requires three parameters, and understanding each is critical for accurate predictions:

=FORECAST.LINEAR(target_x, known_y_values, known_x_values)

target_x: The future point you want to predict. If you’re forecasting month 13 based on 12 months of data, this would be 13.

known_y_values: Your historical dependent variable (what you’re measuring). This could be revenue, units sold, or website visitors.

known_x_values: Your historical independent variable (typically time periods). Must be the same length as known_y_values.

Here’s a practical example with monthly sales data:

// Data structure:
// A2:A7 contains months 1-6
// B2:B7 contains sales: 15000, 18000, 16500, 21000, 19500, 23000

// Predict month 7 sales
=FORECAST.LINEAR(7, B2:B7, A2:A7)
// Returns: 23,250

Common mistakes that break FORECAST.LINEAR:

Unequal array lengths: If A2:A7 has 6 values but B2:B8 has 7, you’ll get a #N/A error
Non-numeric values: Text in either array returns #VALUE
Empty cells: Treated as zeros, which skews your forecast
Reversed parameters: Swapping y and x arrays produces nonsensical results

Step-by-Step Forecast Implementation

Let’s build a revenue forecasting model from scratch. Start with clean historical data:

// Month | Revenue
// 1     | 45000
// 2     | 47500
// 3     | 46800
// 4     | 51200
// 5     | 49900
// 6     | 53400
// 7     | 52100
// 8     | 56800
// 9     | 55300
// 10    | 59200
// 11    | 58400
// 12    | 62100

// Forecast Q1 of next year (months 13, 14, 15)
// In cell B14:
=FORECAST.LINEAR(13, $B$2:$B$13, $A$2:$A$13)

// In cell B15:
=FORECAST.LINEAR(14, $B$2:$B$13, $A$2:$A$13)

// In cell B16:
=FORECAST.LINEAR(15, $B$2:$B$13, $A$2:$A$13)

Always wrap forecasts in error handling for production spreadsheets:

=IFERROR(
    FORECAST.LINEAR(13, $B$2:$B$13, $A$2:$A$13),
    "Insufficient data"
)

This prevents #N/A errors from breaking downstream calculations or dashboards. The IFERROR wrapper catches problems with incomplete data, non-numeric values, or array length mismatches.

For better data validation, add a helper column that checks your data quality:

// In column C, check for valid numeric data
=IF(AND(ISNUMBER(A2), ISNUMBER(B2)), "Valid", "Check data")

Advanced Forecasting with FORECAST.ETS

Linear forecasting fails spectacularly with seasonal data. If you’re analyzing retail sales, hotel bookings, or quarterly website traffic, you need FORECAST.ETS. This function uses Exponential Triple Smoothing to handle seasonal patterns.

// Seasonal sales data (24 months of quarterly patterns)
// A2:A25 contains dates
// B2:B25 contains sales with clear Q4 peaks

// Predict next 3 months
=FORECAST.ETS(A26, $B$2:$B$25, $A$2:$A$25)
=FORECAST.ETS(A27, $B$2:$B$25, $A$2:$A$25)
=FORECAST.ETS(A28, $B$2:$B$25, $A$2:$A$25)

FORECAST.ETS automatically detects seasonality, but you can specify it:

// Force quarterly seasonality (4 periods)
=FORECAST.ETS(A26, $B$2:$B$25, $A$2:$A$25, 4)

For confidence intervals (critical for business planning), use FORECAST.ETS.CONFINT:

// Calculate 95% confidence interval
=FORECAST.ETS.CONFINT(A26, $B$2:$B$25, $A$2:$A$25, 0.95)
// Returns the margin of error

// Build upper and lower bounds
// Forecast: 125,000
// Confidence Interval: 8,500
// Upper bound: 133,500
// Lower bound: 116,500

This tells stakeholders: “We predict 125,000 units, but the actual value will likely fall between 116,500 and 133,500.”

To verify detected seasonality:

=FORECAST.ETS.SEASONALITY($B$2:$B$25, $A$2:$A$25)
// Returns number of periods in seasonal cycle
// Returns 1 if no seasonality detected

Visualizing Forecast Results

Numbers alone don’t convince stakeholders. Create compelling visualizations that combine historical and forecasted data.

Set up your data structure:

// Column A: Month (1-15)
// Column B: Actual Revenue (months 1-12, blank for 13-15)
// Column C: Forecasted Revenue (blank for 1-12, formulas for 13-15)

// In C13:
=FORECAST.LINEAR(A13, $B$2:$B$12, $A$2:$A$12)

Create a line chart with both series:

Select A1:C15
Insert > Line Chart
Format actual data as solid line, forecasted as dashed
Use different colors to distinguish historical from predicted

For presentations, add a combined series that shows actuals where available and forecasts where not:

// Column D: Combined view
=IF(ISBLANK(B2), C2, B2)

This creates a single continuous line that transitions from actual to forecasted data, making trends clearer.

Troubleshooting and Best Practices

#N/A Errors typically mean array length mismatches. Verify with:

=COUNTA(A2:A13) = COUNTA(B2:B13)
// Should return TRUE

#VALUE Errors indicate non-numeric data. Find the culprit:

=SUMPRODUCT(--ISNUMBER(A2:A13))
// Should equal total row count

Accuracy Considerations: FORECAST.LINEAR assumes linear relationships. If your data shows exponential growth, logarithmic decay, or cyclical patterns, linear forecasting will be wildly inaccurate.

Calculate Mean Absolute Percentage Error (MAPE) to measure forecast accuracy:

// Compare forecasted vs actual for historical data
// Column D: Forecast for historical periods
// Column E: Absolute Percentage Error
=ABS((B2-D2)/B2)

// Column F: MAPE
=AVERAGE(E2:E12)
// Under 10% = excellent
// 10-20% = good
// 20-50% = reasonable
// Over 50% = reconsider your approach

When NOT to use forecasting:

Less than 6-8 data points (insufficient for reliable trends)
Highly volatile data with no discernible pattern
When external factors will dramatically change (new product launch, market disruption)
When you need predictive modeling with multiple variables (use regression analysis instead)

Best practices:

Always visualize your data before forecasting—eyeball the trend
Use absolute cell references ($B$2:$B$13) for known values
Document your assumptions in comments
Update forecasts regularly as new actual data arrives
Compare forecasts to actuals and adjust your models
For seasonal data, ensure you have at least two complete cycles
Consider using FORECAST.ETS.STAT to evaluate model quality

Excel’s forecasting functions provide quick, reliable predictions for linear and seasonal data. FORECAST.LINEAR handles straightforward trends, while FORECAST.ETS tackles complex seasonal patterns. Master both, understand their limitations, and you’ll make data-driven predictions that actually hold up in the real world.