The first thing most people do when they need a forecast in Excel is type =FORECAST.LINEAR() into a cell. That’s usually the wrong move — especially if your data has seasonality, gaps, or uneven intervals. It ignores context, assumes perfect linearity, and fails silently on messy real-world data.
The Problem
You’re handed a quarterly sales sheet from Finance. No dates are formatted as actual dates — just text like "Q1 2023". There are blanks in column C (Revenue), and one row says "TBD" instead of a number. You try FORECAST.LINEAR on B2:B13 and C2:C13 — and get #VALUE! in three cells. You don’t know why. You paste over it with an average. Then Sales misses their target by 17%.
| Period | Revenue | Forecast (FORECAST.LINEAR) |
|---|---|---|
| Q1 2023 | $24,800 | |
| Q2 2023 | $29,100 | |
| Q3 2023 | TBD | |
| Q4 2023 | $36,400 | |
| Q1 2024 | $28,200 | |
| Q2 2024 | $31,500 | |
| Q3 2024 | $39,700 | #VALUE! |
| Q4 2024 | $42,100 | #VALUE! |
| Q1 2025 | — | #N/A |
The Solution
Do this instead. It takes 90 seconds and works even with missing values or text labels.
- Clean your time labels first. In D2, enter
=DATEVALUE("1 "&LEFT(A2,2)&" "&RIGHT(A2,4)). Drag down to D10. This converts "Q1 2023" → 1-Apr-2023. Format column D as Short Date. - Replace non-numeric entries. Select C2:C10 → Ctrl+H → Find "TBD", Replace with blank → Find "—", Replace with blank. Then press Alt+H+F+D → choose "Numbers only" to remove any hidden spaces.
- Use TREND(), not FORECAST.LINEAR(). In E2, type
=TREND($C$2:$C$9,$D$2:$D$9,$D$10). That’s your Q1 2025 forecast. Copy that formula down to E10 for all future periods. - Add error handling. Wrap it:
=IFERROR(TREND($C$2:$C$9,$D$2:$D$9,$D$10),AVERAGE($C$2:$C$9)). Now broken inputs won’t crash your sheet.
| Period | Revenue | Forecast (TREND) |
|---|---|---|
| Q1 2023 | $24,800 | |
| Q2 2023 | $29,100 | |
| Q3 2023 | $32,500 | |
| Q4 2023 | $36,400 | |
| Q1 2024 | $28,200 | |
| Q2 2024 | $31,500 | |
| Q3 2024 | $39,700 | |
| Q4 2024 | $42,100 | |
| Q1 2025 | — | $44,820 |
| Q2 2025 | — | $47,260 |
Going Further
TREND() assumes linear behavior. But revenue rarely moves in straight lines. Add curvature with LINEST().
In F2, enter =LINEST(C2:C9,D2:D9^{1,2}). Press Ctrl+Shift+Enter (not Enter). You’ll get two numbers: coefficient for x² and x. Then forecast with =F2*D10^2 + G2*D10 + INTERCEPT(C2:C9,D2:D9).
For seasonal spikes — say, holiday sales in Q4 — use =FORECAST.ETS(). But only if you have at least 2 full cycles (8+ quarters). Feed it D2:D9 and C2:C9, then give it the next date in D10. It auto-detects seasonality. Don’t force it on sparse data — it hallucinates patterns.
One counterintuitive tip: Never forecast beyond 4 periods ahead. After that, confidence drops below 60%. If leadership asks for a 2-year forecast, build it in two 6-month chunks — and show the widening error bands.
When NOT to Use This
- If your time series has fewer than 6 data points — TREND() gives false precision. Use moving averages instead.
- If you’ve got structural breaks — e.g., a new product launch in Q3 2024 — exclude earlier data. TREND() treats all points equally. Manually split the range:
=TREND(C7:C9,D7:D9,D10). - If your data includes outliers (e.g., $120,000 in Q4 2023 due to one huge deal), don’t just delete it. Use TRIMMEAN(C2:C9,0.2) to exclude top/bottom 10% before forecasting.
- Don’t use any Excel forecast function if your last 3 actuals are trending downward but leadership insists on flat growth. Build the model honestly — then layer assumptions separately in another column.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Find & Replace | Ctrl+H |
Critical for cleaning "TBD", "—", extra spaces |
| Format as Short Date | Ctrl+Shift+U |
Converts 2023-04-01 → 4/1/2023 |
| Apply Number Filter (to hide text) | Alt+D+F+F |
Select column → filter → "Number Filters" → "Is Not Blank" |
| Array-enter LINEST() | Ctrl+Shift+Enter |
Older Excel versions require this — newer ones accept Enter |
