Download the workbook 👉 HERE and follow along.
Understanding Year-Over-Year (YOY) changes is crucial for tracking growth. This guide will show you what YOY means and how to calculate it using Excel.
YoY Meaning
Year-over-year (YOY), also known as year-on-year, compares a metric from one year to the same period the previous year. This method helps to see if a company’s financial health is getting better, staying the same, or getting worse.
Year-over-year growth is important because it removes seasonal effects. This makes it easier to see true growth or decline.
Real-World Example:
In the first quarter of 2024, Alphabet (Google’s parent company) reported a 15% increase in revenue YoY, reaching $80.5 billion, compared to $69.8 billion in the first quarter of 2023.
Common YOY Metrics
Some of the most common financial metrics analyzed using year-over-year growth include:
- Sales Revenue
- Cost of Goods Sold (COGS)
- Selling General & Administrative Expenses (SG&A)
- Earnings Before Interest and Taxes (EBIT)
- Net Income
- Earnings Per Share (EPS)
Featured Course
Fundamentals of Financial Analysis
How to Calculate YOY in Excel
Suppose you are given this set of financial data.
Your task is to calculate the YoY growth for each of the financial metrics.
YoY Growth Formula
Use this formula to calculate YOY change:
- New Value: The data from the current year.
- Old Value: The data from the previous year.
For a detailed explanation about the calculation of percentage change, check out this article.
Step 1: Calculate the Percentage Change in Excel
In the case of revenue, it would be this Excel formula:
=D4 / C4 - 1
Press enter and drag the formula down to the cells below it.
Step 2: Format as Percentage
Highlight the YOY Change column, right-click, select “Format Cells,” choose “Percentage,” and set the number of decimal places.
Your table should now look like this:
Visualize Year-Over-Year Changes
To better visualize the results, we’ll use conditional formatting to add data bars.
Step 1: Add Formulas
- Next to the cell with the YOY change, add the formula (e.g., F4):
=E4- Drag the formula down to the other cells.
Step 2: Apply Conditional Formatting Data Bars
- Select YOY Change Column: Highlight the cells containing the YOY changes (e.g., E4).
- Go to Conditional Formatting: Click on the “Home” tab in the Excel ribbon, then select “Conditional Formatting.”
- Choose Data Bars: Select “Data Bars” and choose “More Rules…”
- Show Bar Only: Check the box to only show the bar without the values
- Select fill color for positive deviation: Use the drop down to select a color indicating positive change
- Select fill color for negative deviation: Click on “Negative Value and Axis…” and select a color indicating negative change
- Confirm: click on OK to confirm the changes
This will highlight the percentage change. If a value had negative change, it will be highlighted in red.
Year-Over-Year analysis helps track growth and make better decisions. Using Excel to calculate YOY growth is easy and gives you clear insights into your data trends. Try it out with our practice workbook and see the difference!
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master YoY growth calculation in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
