Forecasting in Excel: TREND, FORECAST.LINEAR, Seasonality and the 51,321 the Straight Line Left Out of Christmas

The budget pack says December 2026 will do 85,313.

December 2025 did 93,800. The business grew every quarter in between. Nobody in the meeting notices, because the number came out of a trendline, and a trendline is the thing you reach for precisely so that you do not have to argue about the number.

The forecast is not wrong because the maths is wrong. FORECAST.LINEAR did exactly what it promises: it drew the best straight line through twenty-four months of revenue and read off month twenty-nine. The maths is fine. The model is wrong, and it is wrong in a way that is invisible from inside the formula — a straight line through a seasonal business splits the difference between the peaks and the troughs, then hands you that compromise as though it were a prediction of a specific month.

Over the five months from August to December it costs 51,321.

This article is about doing it properly, entirely in Excel, with no add-ins: the straight line and the three functions that draw it, why an R² of 0.24 does not mean the trend is fake, how to pull the season out of the series and put it back on the forecast, and — the part almost everybody skips — how to test a forecast against months you already know the answer to, before you bet a hiring plan on it.

What you need. SLOPE, INTERCEPT, TREND, FORECAST, GROWTH, LINEST, RSQ, CORREL and STEYX work in every version of Excel this century and in Google Sheets. FORECAST.LINEAR is the 2016 rename of FORECAST and both still work. The FORECAST.ETS family is Excel 2016 and later on Windows and Mac, and does not exist in Google Sheets. Spilled TREND output needs Microsoft 365 or Excel 2021; in older versions the same formula works as a Ctrl+Shift+Enter array.

1) Twenty-Four Months, One Question

🎯 Scenario: It is late August 2026. Planning wants revenue for the rest of the year — August through December — because the December number decides whether you take on four seasonal staff in October. All you have is monthly revenue and order counts since August 2024.

Twenty-four months, laid out with the month in A2:A25, a period number 1 to 24 in B2:B25, revenue in C2:C25 and orders in D2:D25.

That period column is not decoration. Every forecasting function in Excel wants numeric x-values, and a column of dates technically qualifies — but date serial numbers are five-digit numbers spaced 28 to 31 apart, so your slope comes out as revenue per day and every intermediate figure is awkward to sanity-check. A plain 1, 2, 3… makes the slope read as revenue per month, which is a number you can argue about in a meeting. Type 1 and 2, select both, and drag the fill handle to row 25.

Look at the revenue column before you fit anything to it. Two things are going on at once, and the whole article is about not confusing them:

• Revenue is growing — August 2024 did 57,200, August 2025 did 68,300, and the most recent twelve months average 79,317 against 68,033 for the twelve before.
• Revenue is seasonal — every November and December is a peak, every January and February is a trough, and July drops off a cliff.

A straight line can only describe the first one.

2) The Straight Line: SLOPE, INTERCEPT and FORECAST.LINEAR

The line of best fit through a set of points is defined by two numbers, and Excel will hand you each of them:

=SLOPE(C2:C25, B2:B25)        705.30
=INTERCEPT(C2:C25, B2:B25)    64,858.70

Note the argument order — y first, then x. It is the opposite of the way you say it out loud ("revenue against period"), and it is the single most common mistake with this family of functions. Swap them and you get a number that looks plausible and means nothing: the slope of period on revenue, which here is 0.000336.

So the fitted line is:

revenue = 64,858.70 + 705.30 × period

Revenue is growing by about 705 a month. December 2026 is period 29 — twenty-four months of history, then August is 25, September 26, October 27, November 28, December 29:

=64858.70 + 705.30*29        85,313

You do not have to do that by hand. FORECAST.LINEAR fits the line and evaluates it in one step:

=FORECAST.LINEAR(29, $C$2:$C$25, $B$2:$B$25)      85,312.52

The argument order here is x, then known-ys, then known-xs, which is a different order from SLOPE. There is no logic to this, it is just history; check it every time. In Excel 2013 and earlier, and in Google Sheets, the same function is called FORECAST with identical arguments — Microsoft renamed it in 2016 when the FORECAST.ETS family arrived and needed the namespace.

Absolute references on the two ranges matter as soon as you fill the formula down a column of future periods. Without the dollar signs, row 2's formula fits the line to C2:C25 and row 3's fits it to C3:C26, so each forecast comes off a slightly different line and the column drifts in a way nobody spots.

3) TREND: The Same Line, All Five Months at Once

FORECAST.LINEAR returns one value. TREND returns as many as you ask for, from the same fit:

=TREND($C$2:$C$25, $B$2:$B$25, {25;26;27;28;29})

which spills five figures:

In Microsoft 365 or Excel 2021 that spills down five cells on its own. In an older version, select five cells first, type the formula and commit with Ctrl+Shift+Enter. If your five future period numbers are already sitting in F2:F6, use the range instead of the array constant:

=TREND($C$2:$C$25, $B$2:$B$25, F2:F6)

TREND also has a fourth argument, const, which defaults to TRUE. Set it to FALSE and Excel forces the intercept to zero — the line has to pass through the origin. There are situations where that is right (cost that is genuinely proportional to volume with no fixed component), and a revenue time series is not one of them. Leave it alone.

Look at the five numbers as a group and the problem is already visible without any statistics. The straight line says December will be 85,313 — and December 2025 was 93,800. The model is forecasting a peak month 8,487 below the same month last year, in a business that has grown all year. That is not a cautious forecast. It is a wrong one, and its wrongness has a specific cause.

4) R² Is 0.24, and the Trend Is Still Real

Excel will tell you how well the line fits:

=RSQ(C2:C25, B2:B25)          0.2368
=CORREL(C2:C25, B2:B25)       0.4866
=STEYX(C2:C25, B2:B25)        9,155.61

R² of 0.2368 says the straight line explains about 24% of the variation in monthly revenue. STEYX — the standard error of the estimate — says a typical month lands 9,156 away from the line, and the worst one, November 2025, is 18,956 above it.

The natural reading is "the trend is noise, revenue is basically random". That reading is wrong, and it is worth being precise about why, because it is the point on which the whole article turns.

R² measures how much of the variation one explanatory variable accounts for. Here there are two things moving revenue — a trend and a season — and the model only contains one of them. The 76% that the line fails to explain is not noise. It is December. It is a large, regular, completely predictable pattern that the model has no way to represent, so it gets dumped into the residuals and reported back to you as unexplained error.

The tell is in the shape of the residuals. Put the residual in E2 and fill down:

=C2 - FORECAST.LINEAR(B2, $C$2:$C$25, $B$2:$B$25)

Random noise scatters. These do not: every November and December is strongly positive, every January and February strongly negative, and the same pattern repeats in the second year. A residual column with a repeating shape in it is Excel telling you the model is missing a variable, and the variable is the calendar.

So: keep the trend, and stop asking it to describe the season.

5) The Season Is Bigger Than the Trend

Before modelling the season, it helps to see the trend underneath it, and the classic tool for that is a centred twelve-month moving average. Twelve consecutive months contain exactly one of each calendar month, so averaging them cancels the season out entirely and leaves the underlying level.

Twelve is an even number, so a plain twelve-month average sits between two months rather than on one. The standard fix is to half-weight the two ends and use thirteen values, which recentres it on the middle month. With period 7 (February 2025) in row 8:

=(0.5*C2 + SUM(C3:C13) + 0.5*C14) / 12        68,495.8

Fill that down as far as it will go — with 24 months of history you get twelve values, February 2025 through January 2026:

Perfectly smooth, climbing about 948 a month. There is nothing random about this business at all. The wobble that made R² look terrible is the season, and the season is worth roughly ±20% around a trend that is itself only moving 1% a month.

The moving average is a diagnostic, not a forecast. It stops six months short of the present at each end, and it cannot be extended into the future — that is what the trend line is for. What it establishes is that a trend exists and is close to linear, which is the licence you needed to keep fitting lines.

6) Seasonal Indices: Ratio to Trend

A seasonal index is one number per calendar month saying how that month compares with a normal month. December's is about 1.20 — December runs 20% above the underlying level. January's is 0.84 — January runs 16% below it.

Build them in three columns. Trend value in F2:

=FORECAST.LINEAR(B2, $C$2:$C$25, $B$2:$B$25)

Ratio of actual to trend in G2:

=C2/F2

Calendar month number in H2 — this is what lets you group August 2024 with August 2025:

=MONTH(A2)

Then, with the numbers 1 to 12 sitting in K2:K13, the index for each is the average of that month's ratios:

=AVERAGEIF($H$2:$H$25, K2, $G$2:$G$25)

Two sanity checks before you use these for anything.

They should average to 1. Check with =AVERAGE(L2:L13), which here gives 1.0000 to four decimals. If yours comes out at 1.04, the indices are collectively inflating every forecast by 4%, and the fix is to normalise: =L2*12/SUM($L$2:$L$13).

Each index needs at least two observations. With 24 months every calendar month appears exactly twice, which is the bare minimum for the average to mean anything. With 18 months, six of your indices rest on a single observation and inherit that month's noise permanently. Three years is where this method gets comfortable.

7) The AVERAGEIF Shortcut That Tilts the Whole Year

There is a quicker way to build indices that appears in a lot of spreadsheets, and it has a bias in it that is worth understanding because you will meet it.

=AVERAGEIF($H$2:$H$25, K2, $C$2:$C$25) / AVERAGE($C$2:$C$25)

Average that month's revenue, divide by the average of all revenue. No trend column at all. And on this data it produces something that looks close:

The distortion is systematic, and once you see the cause you cannot unsee it. Our window runs August 2024 to July 2026. August's two observations are periods 1 and 13 — both early in a growing series, so August's average revenue is dragged down by the low trend level it happened to sit on. July's two observations are periods 12 and 24 — the latest available — so July's average is flattered by the high trend level. Dividing by an overall average does nothing to correct this, because the overall average sits in the middle of the window.

The result is an index set that quietly tilts: months near the start of your history look weaker than they are, months near the end look stronger. Forecast August 2026 with it and you get 72,194 against 76,078 from the ratio-to-trend method — 3,884 short, on the month that starts in a week.

The naive version is fine when the series is flat. The moment there is a trend, divide by the trend, not by the average.

8) Deseasonalise, Refit, Reseasonalise

Now the payoff. The indices were built off a trend line that was itself distorted by the season, so the honest move is to strip the season out, refit the trend on the clean series, and put the season back on the forecast.

Deseasonalised revenue in I2:

=C2 / XLOOKUP(MONTH(A2), $K$2:$K$13, $L$2:$L$13)

or, without XLOOKUP:

=C2 / INDEX($L$2:$L$13, MONTH(A2))

Then fit the line again, on the deseasonalised column this time:

=SLOPE(I2:I25, B2:B25)        880.63
=INTERCEPT(I2:I25, B2:B25)    62,747.81
=RSQ(I2:I25, B2:B25)          0.9513
=STEYX(I2:I25, B2:B25)        1,440.12

Three things changed, and all three matter.

R² went from 0.2368 to 0.9513. The trend was always there. It was buried under a season four times its size.

The slope went from 705.30 to 880.63. The raw slope was not just noisy, it was biased low — the series starts in the strong autumn of 2024 and ends in the weak July of 2026, so the season alone drags the line down by about 175 a month. This is the real damage a seasonal series does to a naive trendline, and it compounds with every month you forecast forward.

STEYX went from 9,156 to 1,440. A typical month now lands within about 1,400 of the model instead of 9,200.

The forecast is the clean trend, reseasonalised:

=(62747.81 + 880.63*29) * 1.1959        105,585

or, built from the sheet so it survives new data:

=($L$16 + $L$15*F2) * INDEX($L$2:$L$13, MONTH(E2))

with the intercept in L16, the slope in L15, future period numbers in F2:F6 and future month dates in E2:E6.

Note that the straight line is not uniformly pessimistic — it overstates August by 6,413 and understates November by 21,311. That is what averaging a season does. It is wrong in both directions, and the errors do not cancel where it counts, because the decision the sheet is feeding is about December capacity, not about the annual total.

9) Backtesting: Two Forecasts, One Way to Choose

🎯 Scenario: There are now two numbers for December — 85,313 and 105,585 — and the person who has to sign off four seasonal hires asks the only reasonable question in the room: how do you know which one is better?

Everything so far is an argument. Here is the test.

Hide the last six months from the model, forecast them with each method, and compare against what actually happened. Fit both models on periods 1 to 18 only — August 2024 through January 2026 — then predict February through July 2026:

MAPE is mean absolute percentage error — average the absolute errors as percentages and ignore the signs:

=AVERAGE(ABS(forecast_range - actual_range) / actual_range)

In Microsoft 365 that works as typed. In older Excel it needs Ctrl+Shift+Enter, or a helper column of =ABS(F2-C2)/C2 and a plain AVERAGE over it.

The seasonal model wins, at 2.9% against 8.2%, and the shape of the errors is as informative as the average. The straight line is tolerable in the shoulder months and disastrous at the extremes — off by 16.3% in February and 17.2% in July, the exact months where a plan built on it would be most wrong. The seasonal model's largest miss is 5.4%, and it lands July within 0.1%.

Also worth noticing: the seasonal model is not uniformly better. May goes to the straight line, 3.1% against 3.2%. If you had backtested on May alone you would have picked the wrong model. Six months is a thin test, and a single month is not a test at all.

Do this before you ship a forecast, not after. It costs one copy of the sheet with the last rows deleted, and it is the only thing in this article that produces evidence rather than an opinion.

10) GROWTH and LOGEST: When the Curve Is a Percentage

TREND fits a straight line — constant amount added per period. GROWTH fits an exponential curve — constant percentage added per period. Same arguments, same shapes:

=GROWTH($C$2:$C$25, $B$2:$B$25, {25;26;27;28;29})
=LOGEST(C2:C25, B2:B25)        1.00985

LOGEST returns the growth factor per period, so 1.00985 is 0.99% a month, or about 12.5% a year. GROWTH prices December 2026 at 85,815 against TREND's 85,313 — a difference of 502 across five years' worth of implied compounding, which tells you the two models are nearly indistinguishable on this data.

That near-identity is the useful finding. Fitting a curve instead of a line does nothing here, because the missing structure is not curvature — it is the calendar. R² on the exponential fit is 0.2484, barely different from the linear 0.2368. Swapping models when the problem is a missing variable is motion without progress.

Use GROWTH when the thing genuinely compounds — subscriber counts, viral referrals, a market in its early years. Use TREND for a mature revenue line. And check the difference over your actual horizon before agonising: over five months these two disagree by less than 1%, and over five years they disagree by a factor of two.

One trap: GROWTH and LOGEST take logs internally, so a single zero or negative value in the y-range returns #NUM!. A month with no revenue kills the whole fit.

11) FORECAST.ETS: Excel Doing All of This for You

Excel 2016 added a family that does trend and seasonality in a single function, using exponential triple smoothing (Holt-Winters):

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

With month-start dates in A2:A25, revenue in C2:C25, and December 2026 in a cell:

=FORECAST.ETS(DATE(2026,12,1), $C$2:$C$25, $A$2:$A$25, 12)

The rules that matter:

• The timeline must be real dates with a constant step. Monthly data works; a timeline with a month missing does not. Excel will interpolate small gaps if you leave data_completion at its default of 1, and refuse if you set it to 0.
• seasonality should be stated, not guessed. Left blank or set to 1, Excel detects the period automatically, and on short series it sometimes detects 6 or 4 instead of 12. Type 12 for monthly data. 0 turns seasonality off entirely.
• It weights recent periods more heavily, which is the real difference from the manual method above. The manual model treats December 2024 and December 2025 as equally informative; ETS leans on the recent one. That is usually right, and it is exactly wrong the month after a one-off spike.

Two companions are worth knowing. FORECAST.ETS.CONFINT returns the half-width of a confidence interval, which you add to and subtract from the forecast:

=FORECAST.ETS.CONFINT(DATE(2026,12,1), $C$2:$C$25, $A$2:$A$25, 12, 0.95)

And FORECAST.ETS.SEASONALITY returns the period length Excel detected — run it once to check it says 12 and not 6.

There is also the Forecast Sheet button (Data ▸ Forecast Sheet), which builds all of this plus a chart with confidence bands in about four clicks. It writes FORECAST.ETS formulas into a new sheet, so it is a generator rather than a black box; you can read what it produced.

Treat ETS as a second opinion, not a replacement. Run it alongside your manual model, and when the two disagree by more than a few percent, find out which piece of history is responsible before you pick one. If ETS is far above your figure, it has usually latched onto a recent spike. And note that none of the FORECAST.ETS family exists in Google Sheets — if the sheet has to open there, the manual method in sections 6 to 8 is your only option.

12) Forecast the Drivers, Not the Total

🎯 Scenario: The sales director reads 105,585 and asks what it assumes about the price rise planned for November. There is no answer, because the model has never heard of pricing — it knows about months and nothing else.

Revenue is orders times average order value, and those two move for completely different reasons. Orders follow the season — that is demand. Average order value follows pricing and mix — that is policy, and it drifts smoothly.

Forecasting them separately is more work and usually more accurate, and it is always more useful, because it produces a forecast you can argue with. Average order value in E2:

=C2/D2

That column climbs from 119.17 to 130.80 across the two years, almost perfectly straight — R² of 0.9760 against the period number, and no visible season at all:

=SLOPE(E2:E25, B2:B25)        0.5342
=INTERCEPT(E2:E25, B2:B25)    118.503

So average order value rises by about 53 cents a month, and December 2026 is 118.503 + 0.5342 × 29 = 133.99.

Orders get the full seasonal treatment from sections 6 to 8, giving a deseasonalised trend of 531.82 + 4.4990 × period and an order index for December of 1.1869. December 2026 comes out at 786.1 orders.

786.1 × 133.99 = 105,330

Against 105,585 from the revenue-level model — a gap of 255, or 0.2%. Two routes built from different columns landing on the same answer is the best corroboration a spreadsheet forecast ever gets.

And now the forecast can be interrogated. "786 orders in December" is a claim the sales team can react to; "105,585 of revenue" is a number they can only nod at. If somebody thinks the pricing change lands in November, you change one cell in the AOV line and watch what happens, instead of arguing about the total.

13) A Forecast Without a Range Is a Guess in a Suit

105,585 has five significant figures and deserves about two. Every model in this article has error in it, and a single number hides all of it.

The in-sample residuals say ±1,388 — but that figure is fitted to the same data it is measuring, with thirteen parameters (twelve indices and a slope) doing the fitting, so it is optimistic by construction. The backtest is the honest source: 2.9% MAPE on months the model had never seen.

105,585 × 2.9% = ±3,062

So December 2026 is 102,500 to 108,600, and that is the typical error, not the worst case. Doubling it for a plan that has to survive a bad month gives roughly 99,500 to 111,700.

Say it that way in the meeting. "December is a bit over 100,000, probably between 102 and 109, and here are the six months I tested that on" is a defensible sentence. "December is 105,585" invites somebody to build a hiring plan on the last three digits.

Three habits make the range honest:

• Widen it with distance. A one-month-ahead forecast is far more reliable than a five-month one. If your backtest only covers one-month-ahead errors, it understates the December interval.
• Never present the total as more certain than the parts. Monthly errors partly cancel in an annual total, but only if they are independent — and seasonal-model errors usually are not, because a bad index is wrong every year in the same direction.
• Write down what the model cannot know. A price rise in October, a competitor launch, one large customer churning. The model has seen none of these. A one-line note beats a spuriously precise number.

14) Common Mistakes

1. Arguments in the wrong order. SLOPE and TREND take known-ys first; FORECAST.LINEAR takes the x-value first. Swapping y and x returns a real number that is silently meaningless.
2. Forecasting a seasonal series with a bare trendline. The whole subject of this article. It costs 51,321 over five months here and it never announces itself.
3. Reading a low R² as "no trend". R² measures one variable's contribution. 0.2368 here meant a missing variable, not an absent trend — deseasonalising took it to 0.9513 without touching a single data point.
4. Building seasonal indices with AVERAGEIF over raw revenue. Fine on a flat series, biased the moment there is a trend: months early in your window come out too weak, months late in it too strong.
5. Forgetting to normalise indices. If the twelve do not average to 1, every forecast is inflated or deflated by the same percentage, in a way no individual cell reveals.
6. Relative references in the fitted ranges. Fill =TREND(C2:C25, B2:B25, F2) down a column and each row fits a different line. Lock them: $C$2:$C$25.
7. Extrapolating far past the data. Twenty-four months of history supports a forecast a few months out. The same formula will happily price period 200 and you will happily paste it into a five-year plan.
8. Fitting to a series that changed regime. If pricing doubled in month 14, months 1 to 13 are describing a different business. Fit from the change, or model the change.
9. Judging a forecast by how well it fits history. Add enough parameters and any model fits perfectly and predicts nothing. Hold out the last six months; that is the only score that counts.
10. FORECAST.ETS on an irregular timeline. Real dates with a constant step, or a #N/A. And state seasonality as 12 rather than letting Excel detect it.
11. Google Sheets differences. TREND, FORECAST, GROWTH, SLOPE, INTERCEPT, LINEST, LOGEST, RSQ, CORREL and STEYX all exist and behave identically. FORECAST.LINEAR exists as an alias. The entire FORECAST.ETS family does not exist — there is no equivalent, and the manual method is the whole toolkit.

Conclusion

The straight line was never the problem. FORECAST.LINEAR computed exactly what it claims to compute, and if the business had no season it would have been the right tool and the meeting would have been fine.

What went wrong is that a model with one variable was asked a question about two. Revenue moves with time and with the calendar, and a fit that only knows about time has to average the calendar away — which is why it priced December at 85,313, below the December before it, in a business whose last twelve months averaged 16.6% above the twelve before. The 51,321 that goes missing between August and December is not a rounding issue or a bad month. It is the season, folded flat and divided by twelve.

The fix is four columns and no new functions: divide by the trend to get a ratio, average the ratios by calendar month to get an index, divide the actuals by their index to get a clean series, and fit the line to that. Nine hundred a month of real growth appears from under the noise, R² goes from 0.24 to 0.95, and December comes back at 105,585 with an orders-times-price model agreeing to within 0.2%.

And then do the part that most forecasts skip. Delete the last six months, forecast them, and see how you did. Everything before that is an argument about which model ought to be better; the backtest is the only place the sheet tells you which one is. Here it said 2.9% against 8.2%, and it took about ten minutes.

A forecast is not a prediction of what will happen. It is a statement of what the past implies, made carefully enough that when the future disagrees you can work out which assumption broke.

If you want practice with TREND, FORECAST and the statistical family, try the exercises in the app — every scenario runs on real business data, and a broken formula there costs nothing but another go.