To calculate the percentage difference in Google Sheets, we can take the help of the functions Abs, Divide, Minus, and Average.
Because there is no built-in function in Sheets for this.
Before going to that, you should know the difference between percentage difference and percentage change.
We can use the latter one to compare an old value, such as the total production in the last month, with a new value, such as the total production in the current month.
But the former one is something different. Here we give equal importance to both the values.
Let’s go to an example.
Assume I have published two Google Sheets tutorials on 01-10-2021.
The number of visitors (called site traffic) to those tutorials/articles is as follows.
Tutorial 1 = 500
Tutorial 2 = 450
We can manually calculate the % difference of the number of visitors as follows.
Syntax: absolute_difference_of_the_two_values/average_of_the_two_values
Note:- If the difference is negative, ignore the minus sign. That means we require to take the absolute difference.
Formula:
=(500-450)/((500+450)/2)Result: 0.1053 which is equal to 10.53%
Formula to Calculate Percentage Difference In Google Sheets
Assume the above values are in range A1:A2. Then in cell B1, we can insert the below formula.
=to_percent(divide(abs(minus(A1,A2)),average(A1:A2)))I have used To_Percent to format/convert the output to a percentage value.
We can shorten the above formula a little bit with the help of some arithmetic operators. Here is that Google Sheets formula.
=to_percent(abs(A1-A2)/average(A1:A2))The function ABS returns the absolute value of the difference.
In another example, let’s consider the height of two goalkeepers.
Height of Goalkeeper 1: 6.3 ft.
Height of Goalkeeper 2: 6.5 ft.
In cell A1, input 6.3, and 6.5 in cell A2.
In cell B1, the above formula will return 3.13% as their percentage difference of height.
Note:- If you enter their height in centimeters or any other unit, the result will be the same.
If you have any doubt, use the CONVERT function to convert their heights and use that values.
E.g.:
A1 Formula: =convert(6.3,"ft","cm")
A2 Formula: =convert(6.5,"ft","cm")
Conditionally Highlighting Percentage Difference in Google Sheets
You can follow the above tips/formulas to calculate the percentage difference in Google Sheets.
Now here is an additional tip!
You want to highlight cell B1 if the % difference is greater than 2%.
Go to Format > Conditional formatting and insert the following custom rule.
=B1>2%
If you don’t want the formula in cell B1, then include the calculation within the custom formula rule as below.
=to_percent(abs($A$1-$A$2)/average($A$1:$A$2))>2%This rule is for the cell range A1:A2. It will highlight both the values if the % difference is greater than 2%.
That’s all. Thanks for the stay. Enjoy!
Resources
- How to Use Percentage Value in Logical IF in Google Sheets.
- Calculating the Percentage of Total in Google Sheets [How To].
- How to Round Percentage Values in Google Sheets.
- How to Limit a Percentage Value Between 0 and 100 in Google Sheets.
