Year-Over-Year Growth in Excel: Formula, Table and Pivot

Year-over-year growth in Excel compares a number with the same number one year earlier: (this year − last year) / last year, shown as a percentage. The formula itself is short. The hard parts are getting yearly totals out of a long list, comparing the right periods and handling zero or negative years. This guide covers all of that for analysts, finance staff and job seekers who report growth in sales, costs or customers.

The YoY growth formula#

YoY growth is a percent change where the old value is last year and the new value is this year. With last year in B2 and this year in C2:

=(C2-B2)/B2

Format the result as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%). If sales grew from 533 to 596, the result is 11.82%. The shortcut =C2/B2-1 gives exactly the same answer.

This is the basic percent change formula, and the guide to the percent change formula explains why you divide by the old value and how to read decreases. The rest of this article goes further: real data rarely sits in two neat cells.

YoY growth when the years sit side by side#

When each year has its own column, put the formula in a third column and fill it down. Here are quarterly sales:

The formula in D2 is =(C2-B2)/B2, filled down to D6. Notice that the total's growth, 11.82%, is not the average of the four quarters. Always calculate the total row from the totals, not by averaging the percentages above it.

YoY growth from a long table with SUMIFS#

Most exports are long: one row per sale, order or quarter, with a year or a date in one column. Total each year with SUMIFS, then divide. With years in A2:A9, quarters in B2:B9 and sales in C2:C9:

=SUMIFS(C2:C9,A2:A9,2025)/SUMIFS(C2:C9,A2:A9,2024)-1

The first SUMIFS adds every 2025 row (596), the second every 2024 row (533), and the result is again 11.82%. To make it reusable, put the year in a cell, such as F1, and point at it: =SUMIFS(C:C,A:A,F1)/SUMIFS(C:C,A:A,F1-1)-1. Change F1 to 2026 next year and the formula follows.

If you only have dates, add a helper column with =YEAR(A2), or use date limits inside SUMIFS:

=SUMIFS(C:C,A:A,">="&DATE(2025,1,1),A:A,"<"&DATE(2026,1,1))

The SUMIFS function guide explains how the criteria pairs work, and you can practise COUNTIFS and SUMIFS in a live sheet.

Compare like with like: year-to-date growth#

Compare the same period in both years, or the growth figure is meaningless. Say it is October and 2025 only has Q1 to Q3 so far: 428 in sales. Divide that by the full 2024 total of 533 and you get -19.70%, a fake fall. The fair comparison is Q1 to Q3 of both years:

=SUMIFS(C:C,A:A,2025,B:B,"<=3")/SUMIFS(C:C,A:A,2024,B:B,"<=3")-1

Now 2024's first three quarters total 383, and the result is 11.75%. The second condition, B:B,"<=3", keeps only quarters 1 to 3. With monthly data, use the month number in the same way, such as "<=9" for January to September.

The same thinking applies to the calendar. A month with an extra weekend or a holiday that moved can make one month's YoY swing, so say what you compared.

How to handle zero and negative years#

Growth from zero has no percentage, and Excel shows #DIV/0!. Wrap the formula in IFERROR to show a clean label instead:

=IFERROR((C2-B2)/B2,"n/a")

IFERROR returns the formula's result when it works and "n/a" when it errors. Report the change in units for those rows, such as "+50 new customers". The IFERROR function guide shows other uses.

Negative bases are trickier. If last year's profit was -20 (a loss) and this year's is 10, =(C2-B2)/B2 gives -150%, which looks like a fall even though profit improved. A common fix is to divide by the absolute value, =(C2-B2)/ABS(B2), which gives +150%. Many analysts simply write "n/m" (not meaningful) instead. Either way, say which method you used.

YoY growth in a pivot table#

A pivot table can show YoY growth without a single formula. Starting from a long table with dates and amounts:

1. Insert a pivot table and put the date field in Columns and the amount in Values.
2. Right-click a date and choose Group, then select Years (and Months if you want both).
3. Right-click a value and choose Show Values As > % Difference From.
4. Set Base field to Years and Base item to (previous), then click OK.

Each year now shows its growth over the year before. The first year is blank because it has nothing to compare with. The guides to grouping dates in a pivot table and Show Values As cover these steps in detail.

Growth rates, margins and multi-year trends#

YoY growth answers one question: how did this year compare with last year? Related measures answer others:

• Growth across several years. Averaging YoY rates overstates growth. Use the CAGR formula for one yearly rate across the whole period.
• Ratios like margin. If margin went from 20% to 25%, that is 5 percentage points, or 25% growth in the margin itself. Say which you mean. The profit margin formula shows how margin is built.
• Share of a total. "Q4 was 28% of the year" is a percentage of a total, not growth. See the percentage formula guide for the difference.

Practise it#

YoY growth combines two skills that job tests check all the time: percent change and conditional totals. Practise percentages and percent change in a live spreadsheet, then practise SUMIFS with two conditions to build year totals from a long list. Each answer is checked as you type. Then try the exercise on this page to calculate the YoY growth for the example sales.