YOY Percentage Calculation Formula in Excel

This article explains how to calculate Year-over-Year (YOY) percentage change in Excel, showing the exact formula, how to apply it, and common pitfalls to avoid. You will learn the standard formula, see a worked example, and get answers to frequently asked questions.

Formula

The YOY percentage change formula is:

YOY % Change = ((Current Year Value – Previous Year Value) / Previous Year Value) × 100

Where:

• Current Year Value – the value for the current period (e.g., this year’s sales)
• Previous Year Value – the value for the same period one year earlier (e.g., last year’s sales)
• YOY % Change – the percentage increase or decrease from the previous year

In Excel, if your current year value is in cell A1 and the previous year value is in cell B1, the formula is:

=(A1-B1)/B1

To display it as a percentage, either format the cell as a percentage or multiply by 100. The formula above returns a decimal (e.g., 0.20 for 20%). If you prefer to see the number with a percent sign, apply percentage formatting to the cell.

Worked Example

Let’s use the default values from the calculator: current year value = 120, previous year value = 100.

1. Subtract the previous year value from the current year value: 120 – 100 = 20
2. Divide the difference by the previous year value: 20 ÷ 100 = 0.20
3. Multiply by 100 to get the percentage: 0.20 × 100 = 20%

So the YOY change is +20%. The calculator above gives the same result (20.00%).

When to Use YOY Percentage Change

YOY (Year-over-Year) is used to compare the same period in two consecutive years. It is common in finance, business, and economics to measure growth or decline in revenue, profit, user count, or any metric that varies seasonally. For example, comparing December 2024 sales to December 2023 sales gives a YOY figure that removes the effect of the holiday season, unlike comparing December to November.

YOY is also used for annual data, such as comparing full-year 2024 to full-year 2023. The formula remains the same; you just use the total values for each year.

How to Read the Result

A positive YOY percentage indicates growth compared to the previous year. A negative percentage indicates a decline. For example:

• If current year = 150 and previous year = 100, YOY = +50% (growth)
• If current year = 80 and previous year = 100, YOY = -20% (decline)
• If current year = 100 and previous year = 100, YOY = 0% (no change)

When the previous year value is zero or negative, the formula becomes undefined or misleading. For instance, if previous year = 0 and current year = 10, you cannot divide by zero. If previous year = -10 and current year = 10, the formula gives -200%, which is not a meaningful growth rate. In such cases, avoid using YOY percentage and instead present the absolute change.

Common Mistakes and How to Avoid Them

• Not formatting as percentage: If you forget to format the cell as a percentage, you might see 0.20 instead of 20%. Use the percent style button or multiply by 100.
• Mixing up the order: The formula is (current – previous)/previous, not (previous – current)/previous. Reversing the order gives the opposite sign.
• Using the wrong denominator: Always divide by the previous year value, not the current year value.
• Ignoring missing data: If previous year value is blank or zero, the formula will return an error or a misleading result. Use IFERROR to handle such cases.
• Copying formulas with relative references: When dragging the formula down a column, ensure the cell references are correct. Use absolute references (e.g., $B$1) if you want to lock a specific cell.

Comparing YOY with Other Metrics

YOY percentage change is often confused with:

• Month-over-Month (MoM): compares consecutive months, more volatile and influenced by seasonality.
• Quarter-over-Quarter (QoQ): compares consecutive quarters, also seasonal.
• Compound Annual Growth Rate (CAGR): gives the average annual growth rate over multiple years, smoothing out fluctuations.

YOY is preferred when you want to eliminate seasonal effects. For example, a retail store might see a 30% drop in sales from December to January, but a YOY comparison with the same month last year would be more meaningful.

Limitations of the YOY Formula

The YOY percentage change is a simple ratio, but it has limitations:

• Base effect: A large percentage increase can occur simply because the previous year was very low. For instance, going from 1 to 2 is a 100% increase, but the absolute change is only 1 unit.
• Negative values: As mentioned, the formula is not meaningful when the previous year value is zero or negative.
• Does not indicate magnitude: A 10% increase on a base of $1 million is $100,000, while a 10% increase on $100 is only $10. Always consider the absolute change alongside the percentage.
• Only compares two points: It ignores trends within the year or multiple years. For a longer view, use CAGR or trend analysis.

Frequently Asked Questions

How do I calculate YOY in Excel with a formula?

Use =(current - previous)/previous and format the cell as a percentage. For example, if current year is in A1 and previous year in B1, enter =(A1-B1)/B1 in C1.

What if my previous year value is zero?

You cannot divide by zero. Excel will show a #DIV/0! error. You can use =IFERROR((A1-B1)/B1, "N/A") to display a message instead of an error.

Should I multiply by 100?

If you format the cell as a percentage, Excel automatically multiplies by 100 and adds the % sign. If you leave the cell as a number, you need to multiply by 100 to get the percentage value.

Can I use YOY for monthly data?

Yes, YOY compares the same month in two different years. For example, compare March 2025 to March 2024. The formula is the same.

What is the difference between YOY and CAGR?

YOY compares two specific periods, while CAGR calculates the average annual growth rate over multiple periods. CAGR smooths out volatility and is useful for long-term trends.

Why is my YOY result negative?

A negative result means the current year value is lower than the previous year value. For example, if current = 80 and previous = 100, the YOY is -20%.

Related

• 180 Minutes to Hours: Quick Conversion
• Decimal Time to Hours and Minutes
• Minutes to Hours: Divide by 60? Yes, Here’s How

in current | Current Year Value | - | 120 | 0
in previous | Previous Year Value | - | 100 | 0
out yoy | YOY % Change | % | ((current - previous) / previous) * 100 | 2