How to Calculate Percentage Difference In Google Sheets

Published on

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%
Percentage Difference In Google Sheets - Highlight Rule

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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.