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
Whether you’re a newbie or have an MBA in Finance, you’ll FINALLY “get” the big picture.This comprehensive course will equip you with these critical skills – even if you’ve never taken a finance or accounting class.
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 Course
Black Belt Excel Package
What would your life look like if you became an Excel Black Belt?You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Published on: June 15, 2024
Last modified: June 26, 2024
Category: Accounting & Finance
Tagged as: year over year, yoy, yoy growth formula, yoy meaning
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.