Calculating the change or difference between values is a very common analysis, and has applications in economics, mathematics, and any field where you deal with numbers. Fortunately, it’s extremely easy to calculate the percentage difference in Excel.
In this tutorial, we will learn how to write the formula for percentage change, and how to display it properly.
How to Calculate Percentage Difference?
The mathematical formula to calculate the percentage difference (like between attrition rates) is as follows:
Percentage Difference = (New_value – Old_value) / Old_value
The values may not be older or newer than each other, but rather be a part of a whole. Then, the formula can be updated as:
Percentage Difference = Part / Whole
Let’s see a practical example of how we can apply the mathematical formula for percentage change in Excel.
We have the following data for some product prices, and we want to calculate the percentage difference:
The product price is available for 2022, and 2023, which means that we can think of the price for 2022 as the old value, and the price for 2023 as the new value. Now the only thing left is to apply the formula to our data.
1) Write the Formula
In a new cell, write the following formula:
=(B2-A2) / A2
This is the exact replica of the percentage difference formula:
= (New_value – Old_value) / Old_value
When you write this formula, the percentage difference will be calculated for a single cell.
2) Apply to Other Cells
To apply the formula to other cells, or the whole column, click on the small rectangle on the bottom right of the cell that contains the formula and drag it in the desired direction.
Dragging the cell will enable auto-fill, and automatically apply and replicate the formula for other cells:
The percentage difference formula is very beneficial, since you can easily see the magnitude and the percentage of both the increase and the decrease in values.
3) Change Cell Format to Percent
We successfully calculated the percentage difference of all the product prices in our spreadsheet. However, the difference is shown as a ratio in the output cells rather than a percentage, and the percentage sign is not present in the output. We could multiply these cells with 100, and insert the percentage sign manually to all cells, but this would be tedious and unnecessary, when there are two simple ways of automatically displaying these values as percentages.
Using the Formal Cells Dialog Box
Step 1: Select the cells with the percentage difference formula.
Step 2: Right-click and click on “Format Cells…”.
Step 3: Under Category, select Percentage. Select the number of Decimal places (default is 2), and then click on OK.
Congratulations, your percentage differences are ready!
You might also want to learn how to calculate differences between two numbers while you’re at it.
Using the Percent Style Icon
Step 1: Select the cells with percentage differences.
Step 2: Go to the Home tab in Excel, and then click on the Percent Style icon under the Number division.
This will change the cell styles to Percent Style, but there are no decimal places:
If you are unsatisfied with the number of decimal places, you can increase or decrease them using the “Increase Decimal” and “Decrease Decimal” buttons next to the Percent Style icon.
Simply select the cells with percentage differences, and then click on the “Increase Decimal” button to increase the number of decimal places.
You can also convert decimals to percentages using other methods not mentioned here.
That’s it! We learned how to write the formula for percentage difference, and how to apply it to multiple cells. Afterward, we also formatted the cells such that they appear with a percentage sign. Nice job :)