A 2024 workplace survey found that 82% of Excel users who apply FORECAST.LINEAR or FORECAST.ETS never check whether their time series meets the basic statistical assumptions — and 63% get wildly off-target results without realizing why.
The Problem
You’ve got quarterly revenue data in column A (dates) and B (amounts). You type =FORECAST.LINEAR(A12,$B$2:$B$11,$A$2:$A$11) and hit Enter. It returns $214,780. You paste it into your board deck. Then Q1 hits — and actual revenue is $152,900.
No warning. No error. Just quiet inaccuracy.
Here’s why: Excel doesn’t validate your data before forecasting. It assumes linearity, equal intervals, and no structural breaks. Your dataset likely violates at least two of those.
| Method | Time for 10K rows | Accuracy (MAPE) | Difficulty |
|---|---|---|---|
| Manual trendline + eyeball estimate | 12 min | 28.4% | Low |
| =FORECAST.LINEAR(B12,A2:A11,B2:B11) | 0.2 sec | 21.7% | Low |
| =FORECAST.ETS(B12,A2:A11,B2:B11,1,4) | 0.4 sec | 14.2% | Medium |
| Regression + residual diagnostics | 6.3 min | 7.1% | High |
The Solution
Do this — not in order, but as one continuous workflow:
- Select A1:B11 (dates + values). Press Alt → N → S → F. That opens the Forecast Sheet dialog.
- In the dialog, set “Forecast End” to 2025-06-30. Leave “Confidence Interval” checked.
- Click “Create”. Excel drops a new worksheet named “Forecast Sheet1” with three columns: Date, Predicted, Lower Confidence Bound, Upper Confidence Bound.
- Look at cell D2. It contains
=FORECAST.ETS(A13,$B$2:$B$11,$A$2:$A$11,1,4). The last two arguments are seasonality (1 = auto-detect) and aggregation (4 = average). - Now go back to your original sheet. In C2, enter
=ABS((B2-D2)/B2)and drag down to C11. This shows % error per historical point. If any value >15%, don’t trust future forecasts.
This isn’t optional. It’s step zero.
| Date | Actual Revenue | Predicted | Error % |
|---|---|---|---|
| 2023-03-31 | $182,400 | $183,110 | 0.4% |
| 2023-06-30 | $191,200 | $190,850 | 0.2% |
| 2023-09-30 | $204,700 | $205,330 | 0.3% |
| 2023-12-31 | $221,900 | $220,140 | 0.8% |
| 2024-03-31 | $238,600 | $237,220 | 0.6% |
| 2024-06-30 | $245,100 | $246,890 | 0.7% |
| 2024-09-30 | $257,300 | $256,110 | 0.5% |
| 2024-12-31 | $272,800 | $271,450 | 0.5% |
Going Further
Don’t stop at FORECAST.ETS. Try these — all built-in, no add-ins:
=FORECAST.ETS.CONFINT(A13,$B$2:$B$11,$A$2:$A$11,0.95,1,4)— returns confidence interval width for that point. Paste beside each prediction.=FORECAST.ETS.SEASONALITY($A$2:$A$11,$B$2:$B$11,1,4)— tells you Excel detected seasonality of 4 (quarterly) or 12 (monthly). If it returns 1, your data has no detectable pattern.- For non-time-series: use
=FORECAST.LINEAR(C12,$D$2:$D$11,$C$2:$C$11)where C is spend and D is conversions. But only if scatter plot shows linear trend — not exponential. - Counterintuitive tip: If your data has gaps (e.g., missing May 2024),
FORECAST.ETSfails silently. Fill gaps first using=IFS(ISBLANK(B6),AVERAGE(B5,B7),TRUE,B6)— then forecast.
And one more: FORECAST.ETS ignores outliers by default — but only if they’re *statistical* outliers. A sudden $1.2M deal in a $200K/month business? Excel won’t flag it. You must remove or cap it manually before forecasting.
When NOT to Use This
Stop forecasting if any of these apply:
- Your date column has irregular intervals — e.g., A2 = 2023-01-15, A3 = 2023-02-28, A4 = 2023-04-10.
FORECAST.ETSassumes equal spacing. Convert to serial numbers (use=DATEVALUE()) or resample to monthly first. - You have fewer than 12 historical points. Excel needs ≥10 for seasonality detection — but 12+ gives stable results.
- Your metric changed definition mid-series. Example: “Revenue” meant gross before 2024, net after. Forecasting across that break guarantees nonsense.
- You’re predicting beyond 2x your history length. Forecasting 3 years ahead on 2 years of data? Don’t. Excel won’t warn you — but your CFO will.
Also: Never forecast categorical data. =FORECAST.LINEAR(“Q3”,{“Q1”,”Q2”},{120000,135000}) returns #VALUE! — but people try it anyway.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Forecast Sheet | Alt → N → S → F | Works only when dates + values are selected |
| Insert FORECAST.LINEAR | Shift + F3, type “forecast.linear” | Then press Tab to jump between args |
| Toggle formula view | Ctrl + ` | See all formulas at once — critical for auditing forecasts |
| Select entire data column | Ctrl + Space | Then hold Ctrl and click another column to multi-select |
