How to Calculate Percentage Difference in Excel (Formulas)

The term ‘percentage difference’ is often misunderstood. 

A lot of people get mixed up between the terms ‘percentage difference’ and ‘percentage change’.

Both are completely different concepts and have completely different formulae.

In this tutorial, we will understand the difference between the two concepts, and find out how to calculate the percentage difference in Excel.

What does Percentage Difference Mean?

The percentage difference is a measure of how much two quantities of the same type differ from one another.

Usually when percentage difference is calculated the direction of difference and relationship between the quantities does not matter, or is unavailable.

For example, you can use percentage difference to compare the prices of oil from two different locations.

You can also use it to compare the difference in weight or size of two different items of the same type.

For instance, if you want to know how much heavier one bag of sugar is compared to another.

How to Calculate Percentage Difference

To calculate the percentage difference between two quantities, we need to find the absolute difference between the two numbers, divide this difference by the average of the two numbers and then multiply this value by 100.

In other words, if the first value is value1 and the second value is value2, then the formula to calculate the percentage difference is:

(value1 - value2) / ( ( value1 + value2 ) /2 ) * 100

We are using the term ‘absolute difference’ because we only want the difference between the two values, irrespective of which value is larger.

Now let us look at how we can apply this to a problem in Excel.

How to Calculate the Percentage Difference in Excel

Consider the following dataset:

Dataset to calculate percentage difference

The above screenshot displays data comparing diesel fuel and regular gasoline prices in Los Angeles over a period of 5 days.

To calculate the percentage difference in prices of the two fuels, follow the steps below:

  1. Select the first cell in the “Percentage Difference” column.
  2. Type in the following formula and press the return key: =ABS(B2-C2)/AVERAGE(B2,C2).
  3. You should see the result in your selected cell, but it is not a percentage value yet.
Formula to calculate percentage difference in Excel
  1. To convert the value into a percentage, click on the “%” button, which you will find under the Home tab, in the “Number” group, as shown below:
Click on the Percent icon to format the results
  1. To adjust the number of decimal places displayed, click on the “.00->.0” or “.00<-.0” button as many times as needed. We needed the value in just one decimal place, so we pressed the “.0<-.00” button once.
Increase or decrease the decimal numbers
  1. Copy the formula down to the rest of the values in the column to compute the percentage differences for all the rows.

Here’s how the final worksheet looks:

Percentage difference result in Excel

Note: To find the absolute value of the differences, we used the ABS function. If you prefer to know which instances of the diesel fuel values are greater than gasoline, you can skip the ABS function in the above formula. Your formula would then be: =(B2-C2)/AVERAGE(B2,C2). This will display those results where the diesel fuel values are less than gasoline as a negative number.

What does Percentage Change Mean?

The percentage change is a measure of how much a given quantity has changed over a period of time.

For example, you can find the percentage of change in the value of gasoline over a year, or how much production of an item has increased or decreased in a year.

The comparison here is between values of the same quantity at different times.

As such, the frame of reference, in this case, is the original value of the quantity, and not the average of the two values (as was in the case of ‘percentage difference’).

How to Calculate Percentage Change

To calculate the percentage change in a quantity, we need to find the difference between the two values of the quantity, divide this difference by the original value and then multiply this value by 100.

In other words, if the original value is value1 and the new value is value2, then the formula to calculate the percentage change in value is:

( ( value2 - value1 ) / value1 ) * 100

If the change is positive (there is an increase in value), the result will be a positive number.

If the change is negative (there is a decrease in value), the result will be a negative number.

Now let us look at how we can apply this to a problem in Excel.

How to Calculate the Percentage Change in Excel

Consider the following dataset:

The above screenshot displays data comparing diesel fuel prices in 3 different areas over a week.

To calculate the percentage change in prices for each area, follow the steps below:

  1. Select the first cell in the “Percentage Change” column.
  2. Type in the following formula and press the return key: =(C2-B2)/B2
  3. You should see the result in your selected cell.
  1. To convert the value into a percentage, click on the “%” button.
  1. To adjust the number of decimal places displayed, click on the “.00->.0” or “.00<-.0” button as many times as needed. 
  1. Copy the formula down to the rest of the values in the column to compute the percentage differences for all the rows.

Here’s how the final worksheet looks:

Also read: How to Add a Percentage to a Number in Excel?

Percentage Difference vs Percentage Change – Comparison

If the difference between the terms “Percentage difference” and “Percentage change” is still not clear for you, here’s a quick recap.

Percentage ChangePercentage Difference
The percentage difference compares values of two different quantities, but of the same type.The percentage change compares values of the same quantity over a given time period.
For example, the prices of two different fuels at a given time.For example, the price of a particular fuel at the beginning and at the end of the month.
The frame of reference here is the average value of the two quantities. The frame of reference here is the original value or starting value of the given quantity. 
The formula for the percentage difference is: (value1value2)/((value1+value2)/2) * 100 The formula for percentage change is:((value2value1)/value1)* 100 

While researching for this article, I came across many different articles that show the wrong calculation for percentage difference in Excel. In many cases, the formula people use to calculate percentage difference is actually the formula for percentage change.

In this tutorial, we discussed what you mean by the term Percentage Difference and how this differs from Percentage Change.

We also discussed the formulae to calculate both, as well as how to find them using Excel.

Other Excel tutorials you may also like: