Percentage change is a core concept in data analysis, helping us see how a value shifts over time. It’s super handy in business analysis, financial reporting, and academic research. Tracking the ups and downs of key metrics is vital for making smart choices.
In this post, we’ll dive into what percentage change is, why it’s key, and how to crunch the numbers using Microsoft Excel.
Percentage change shows how much a value’s gone up or down compared to its starting point. You figure it out by subtracting the original value from the new value, dividing that difference by the original value, and then multiplying by 100 to get a percentage.
Here’s the formula:
Percentage Change = ((New Value – Original Value) / Original Value) * 100
The above calculation is essential because it lets you compare changes across different scales. For example, a $1,000 bump in sales is huge for a small business but tiny for a big one. Using percentage change, we get a clearer picture of the impact on each business.
Microsoft Excel is a powerhouse for calculations and data analysis. It offers a slew of functions and tools to help you easily calculate percentage changes and other important metrics.
Here are some key Excel features we’ll use:
Before diving into calculations, let’s set up our Excel sheet with some sample data. We’ll use a company’s sales figures for two years.
Your Excel sheet should now look like the following:
Gain access to 100+ shortcuts, formula auditing visualizations, easy Excel-to-PowerPoint linking, and productivity tools to build models and presentations faster than ever.
Now that our data is ready, let’s walk through calculating the percentage change.
In our example, the original value is the 2021 sales figure (100000), and the new value is the 2022 sales figure (120000). We already entered the cells in cells B2 and B3, respectively.
The percentage change formula is:
((New Value – Original Value) / Original Value) * 100
In Excel, we can use cell references to represent the new and original values in the formula. In our example, the new value is in cell B3, and the original value is in cell B2.
So, our Excel formula for percentage change will be:
=((B3-B2)/B2)*100
Let’s break down the above formula:
Now, let’s enter the formula into Excel:
Step 1: Click on cell B6 (or any empty cell where you want the result to appear).
Step 2: Type in the formula: =((B3-B2)/B2)*100.
Step 3: Press ‘Enter’.
To make the result easier to read, we can format it as a percentage:
Step 1: Right-click on cell B6 and select ‘Format Cells’.
Step 2: In the ‘Format Cells’ dialog box, choose the ‘Percentage’ category. Adjust the decimal places if needed, then click ‘OK’.
Now, cell B6 will display the result as a percentage.
Now that we understand how to calculate percentage change in Excel, let’s apply this knowledge to some practical examples.
Example 1: Calculating Sales Growth
Below is a dataset of a company’s sales figures from 2020 to 2023:
To calculate the year-over-year percentage changes in Revenue and Net Income:
Step 1: Set up the formula for percentage change in Revenue:
Step 2: Set up the formula for percentage change in Net Income:
Step 3: Format the results as percentages.
It will show the year-over-year growth rates for Revenue and Net Income, helping assess financial health and guide investments.
Example 2: Analyzing Survey Results
Say you conducted a customer satisfaction survey, then repeated it a year later:
To calculate the percentage change for each score:
Step 1: Set up the formula for percentage change in cell B4:
Step 2: Copy the formula across to cells C4, D4, E4, and F4.
Step 3: Format the results as percentages.
It shows how survey responses shifted, revealing changes in customer satisfaction.
Create Percentage Change Between Two Numbers
Try Macabacus for free to accelerate financial modeling in Excel.
When working with formulas in Excel, it’s common to encounter errors. Here are some tips for troubleshooting:
Once you’ve nailed the basics, try the following advanced Excel features to speed up your work:
Calculating percentage change is a valuable skill for anyone working with data in Excel. By understanding the formula and how to apply it using cell references, you can quickly analyze how values change over time and make data-driven decisions.
To streamline the process of calculating percentage changes and ensure accuracy across your spreadsheets, consider using tools like Macabacus, which offers features specifically designed to enhance productivity in Excel for finance and banking professionals.
Remember to practice with different datasets to solidify your understanding of percentage change. As you become more comfortable with the concept, challenge yourself to incorporate it into more complex analyses and explore advanced Excel features to streamline your workflow.
Gain access to 100+ shortcuts, formula auditing visualizations, easy Excel-to-PowerPoint linking, and productivity tools to build models and presentations faster than ever.