Most Excel trainers say FORECAST.LINEAR is the ‘smart, modern way’ to predict values. They’re wrong. It’s a blunt instrument — no error checking, zero outlier detection, and it’ll happily return $1.2M for next month’s sales even if your last 3 months were $42K, $38K, and $5K. You’re not doing forecasting. You’re rolling dice with decimal places.
The Problem
You have monthly revenue data for Q1–Q3 2024. Sales dropped sharply in July after a key client left. Your manager asks: ‘What’s October looking like?’ You plug the numbers into FORECAST.LINEAR — and get $67,842. That number feels off. But you send it anyway. Because you don’t know what the function *actually* does under the hood.
| Month | Revenue | Days Since Jan 1 |
|---|---|---|
| Jan-24 | $42,100 | 0 |
| Feb-24 | $45,200 | 31 |
| Mar-24 | $47,900 | 60 |
| Apr-24 | $49,300 | 91 |
| May-24 | $50,100 | 121 |
| Jun-24 | $48,600 | 152 |
| Jul-24 | $12,400 | 182 |
| Aug-24 | $14,700 | 213 |
| Sep-24 | $16,200 | 244 |
This is your raw data in A1:C10. Column C isn’t dates — it’s serial numbers. FORECAST.LINEAR doesn’t accept dates directly. If you feed it actual dates (like 2024-07-15), it converts them to integers — but only if they’re formatted as dates. Feed it text that looks like a date? It returns #VALUE!. And yes — that July $12,400 outlier stays in the calculation. FORECAST.LINEAR has no ‘ignore this row’ button.
The Solution
Do this — not the ‘just type =FORECAST.LINEAR’ shortcut. Do it step-by-step, or you’ll misread the output.
- Prepare your x-values: In D1:D10, enter =A1-DATE(2024,1,1)+1 (then copy down). This gives clean day offsets from Jan 1, 2024. Now D1:D10 = {1,32,61,92,122,153,183,214,245} — numeric, monotonic, no gaps.
- Select target x: In D11, enter 275 (Oct 1, 2024 = 275 days since Jan 1). Don’t type ‘Oct-24’. FORECAST.LINEAR needs numbers.
- Type the formula: In E11, enter
=FORECAST.LINEAR(D11,B1:B10,D1:D10). Press Enter. Result: $19,362. - Verify linearity: Select B1:C10 → Insert → Scatter Chart. Add trendline → Format Trendline → check ‘Display Equation’. You’ll see y = -112.4x + 45210. That slope matches what FORECAST.LINEAR used — no magic, just least squares.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | In D1, enter =A1-DATE(2024,1,1)+1 |
1 | Ctrl+C / Ctrl+V |
| 2 | Select D1:D10 → Ctrl+D | Fills day offsets | Ctrl+D |
| 3 | In D11, type 275 |
275 | Enter |
| 4 | In E11, type =FORECAST.LINEAR(D11,B1:B10,D1:D10) |
$19,362 | Alt+= (to insert =) |
That $19,362 is better than $67,842 — but still flawed. Why? Because it treats July–September as part of the same trend. You need to decide: is that drop permanent? Or noise? FORECAST.LINEAR won’t tell you. You must.
Going Further
Three real-world upgrades — none require add-ins.
- Exclude outliers manually: Use =FORECAST.LINEAR(D11,IF(B1:B10>20000,B1:B10,""),IF(B1:B10>20000,D1:D10,"")) — then press Ctrl+Shift+Enter (it’s an array formula in older Excel). This drops all rows where revenue < $20K. Result: $44,103 — very different.
- Add confidence bounds: Use =TREND(B1:B10,D1:D10,D11) for the same result as FORECAST.LINEAR — but TREND accepts arrays, so you can combine it with STDEV.S and T.INV.2T to build ±95% bands.
- Switch to LOGEST if growth is exponential: For SaaS MRR or user counts, =LOGEST(B1:B10,D1:D10,TRUE,TRUE) gives you b and m for y = b*m^x. Then calculate =b*m^275. Try it on your Sep-24 value — you’ll likely get $22,800 instead of $19,362.
Surprising tip: FORECAST.LINEAR returns identical results to =TREND(y,x,new_x) — but TREND lets you pass multiple x-columns (e.g., time + marketing spend). So if you ever add a second driver, switch to TREND now. No learning curve.
When NOT to Use This
Stop typing FORECAST.LINEAR if any of these apply:
- Your x-values contain text, blanks, or errors — it returns #N/A without warning you which cell broke it.
- You have fewer than 3 data points. Excel will calculate something — but the standard error is meaningless. Don’t trust it.
- Your y-values include zeros or negatives while modeling growth (e.g., profit margin % dropping from 12% to -3%). Linear models implode there. Use =GROWTH() or manual log transforms.
- You’re forecasting more than 2–3 periods ahead. The error compounds fast. At 6 months out, ±25% is typical — even with clean data.
Also: never use it on categorical x-values (‘Q1’, ‘Q2’, ‘Q3’). Convert those to 1, 2, 3 first — or better, use INDEX/MATCH with weighted averages.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Insert function dialog | Shift+F3 |
Type ‘forecast.linear’ and double-click |
| Fill down formula | Ctrl+D |
After selecting source + destination range |
| Open Name Manager | Ctrl+F3 |
Useful for auditing named ranges feeding FORECAST.LINEAR |
| Toggle formula view | Ctrl+` (grave accent) |
See all formulas at once — critical when debugging FORECAST.LINEAR inputs |
