How to calculate year-over-year (YoY) growth in Excel

Year-over-year growth is the single most quoted number in any board pack — and the easiest to get subtly wrong. This guide gives you the exact Excel formula, the same-month-last-year variant that strips out seasonality, the multi-year CAGR, and the handful of pitfalls (zero bases, negative bases, partial years) that quietly corrupt the figure. Worked example throughout.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

1.What YoY growth actually measures

Year-over-year (YoY) growth compares a value to the same period one year earlier and expresses the change as a percentage. Comparing across a full year is what makes it powerful: it automatically cancels out seasonality. December is always big; comparing this December with last December tells you whether the business actually grew, rather than just that it’s the holidays again.

That’s the key distinction from month-over-month (MoM) growth, which compares consecutive months and is dominated by seasonal swings. If your data has a season — and most revenue, demand and traffic data does — YoY is usually the honest growth number and MoM is the noisy one. (If you need to model that seasonality directly rather than cancel it, see seasonal forecasting in Excel.)

2.Step 1 — Lay out the data

Open Excel. The simplest layout is one row per period, oldest at the top. For annual figures:

The first period has no prior year to compare to, so its YoY cell stays blank. Every later row compares to the row directly above it.

3.Step 2 — The YoY percentage formula

The formula is the standard percentage-change calculation:

In Excel, with 2022 revenue in B2 and 2023 in B3, put this in C3 and drag down:

C3:  =(B3-B2)/B2

Then format the cell as a percentage (Ctrl+Shift+%, or Home → %). This is the step everyone forgets: without it, 25% growth shows as 0.25 and looks wrong. An equivalent form some people prefer is =B3/B2-1 — identical result, one fewer set of brackets.

Using our worked numbers, 2023 grew (1,000,000−800,000)/800,000 = 0.25 = 25%. Notice the growth rate falls each year (25% → 15% → 10%) even though revenue keeps rising — that’s the law of larger numbers, and it’s exactly the kind of thing a single YoY figure hides and CAGR reveals.

4.Step 3 — Same month, a year earlier

With monthly data, “year over year” means comparing each month with the same month last year — i.e. the value 12 rows back, not the row above. If months run down column B starting in row 2, the first comparable month (the 13th, row 14) is:

C14:  =(B14-B2)/B2        ' this month vs same month last year

Drag that down and every month is compared to its counterpart a year earlier. This is the version analysts actually mean when they say “we’re up 12% YoY” on a monthly metric. Because it compares like-for-like seasons, a single seasonal spike doesn’t masquerade as growth. If you instead want the cumulative position within the year, that’s a running total in Excel — a different question (year-to-date) from YoY (vs last year).

5.Step 4 — Multi-year CAGR

A single YoY number only describes one step. To summarise growth across several years with one figure, use the compound annual growth rate (CAGR) — the constant annual rate that would carry the starting value to the ending value:

In Excel, from 2022 (B2 = 800,000) to 2025 (B5 = 1,265,000), that’s 3 years of growth (count the gaps, not the data points):

=(B5/B2)^(1/3)-1            ' explicit
=(B5/B2)^(1/(A5-A2))-1      ' years derived from the year labels
=RRI(A5-A2, B2, B5)         ' Excel's built-in CAGR function

All three return the same answer: (1,265,000/800,000)^(1/3)−1 ≈ 16.5%. So although the business grew 25%, then 15%, then 10%, its smoothed rate over the three years was about 16.5% a year. The handy RRI function (Rate of Return on Investment) does it in one call — arguments are periods, present value, future value.

The classic error here is using the wrong number of years: four annual data points span three years of growth, not four. Deriving it from the year labels with A5-A2 removes the temptation to miscount.

6.Step 5 — Zeros, negatives & partial years

Three situations break the simple formula. Handle them explicitly:

Zero prior-year base. Dividing by zero gives #DIV/0!, and conceptually, growth from nothing is infinite. Guard it:

=IF(B2=0, "n/a", (B3-B2)/B2)

Negative prior-year base. If last year was a loss (say profit of −50 and this year +50), the formula returns (50−(−50))/−50 = −200% — which says you shrank when you clearly improved. Percentage growth is only meaningful from a positive base; for swings through zero, report the absolute change instead. This is the natural moment to switch to a variance analysis in Excel, which handles favourable/unfavourable signed differences properly.

Partial year. Comparing a year-to-date figure (say 9 months of 2025) against a full 2024 will always look like a collapse. Either annualise the partial year, or compare it to the same 9 months of the prior year — never partial-against-full.

YoY vs MoM vs variance

Three related comparisons, three different jobs:

They’re complementary. A board pack typically shows YoY for the growth story and variance against budget for the accountability story. Use the right one for the question being asked.

Common mistakes

1. Forgetting percentage formatting. 0.25 in a cell that should read 25% is the most common “my formula is broken” that isn’t.

2. Miscounting CAGR years. n annual data points span n−1 years of growth. Derive the exponent from the year labels to be safe.

3. Growth off a negative or zero base. Both make the percentage meaningless or undefined. Switch to absolute change or annotate it.

4. Comparing the wrong row for monthly data. True YoY is 12 rows back, not the row above — that latter is month-over-month.

5. Mixing partial and full periods. Year-to-date vs full prior year always understates. Compare like windows.

The 30-second shortcut

If you’d rather not babysit YoY and CAGR formulas across dozens of metrics, our free forecasting calculator computes growth rates and projects them forward on any pasted column of values, in your browser, no signup.

Frequently asked questions