How to Calculate Percentage Difference in Excel

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:

Product prices for two years

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.

Percent difference 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.

Drag the cell down to apply to other cells

Dragging the cell will enable auto-fill, and automatically apply and replicate the formula for other cells:

Percent difference calculated for all 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”.

Open format cells for percent difference cells

Step 3: Under Category, select Percentage. Select the number of Decimal places (default is 2), and then click on OK.

Format cells as percentage

Congratulations, your percentage differences are ready!

Percent difference using format cells

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.

Click on the percent style icon

This will change the cell styles to Percent Style, but there are no decimal places:

Cells in percent style

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.

Increase or decrease 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 :)

Leave a Comment