There is no specific function to calculate the percentage change in Google Sheets. For this, we can write an array or non-array formula using arithmetic operators or its equivalent functions.

If you are familiar with or using MS Excel, you may already have the percentage change formula that may equally work in Google Sheets. But I am sure you don’t have an array formula (Excel version) to calculate the percentage change.

Even if you have, it won’t work in Google Sheets as ArrayFormula is a function in Sheets, not in Excel.

Must Read: Array Formula: How It Differs in Google Sheets and Excel.

Change and % Change

Change is the difference of one (new) value with another (old) value. Percentage change is the same difference in percentage.

Assume, as per my Google Analytics statistics, the traffic of my site (number of visitors) was 7720 nos. yesterday and 7304 nos. on the same day last week.

You May Like: How to Connect Google Analytics to Google Sheets.

The change in site traffic is 416 which we can calculate as below.

=7720-7304
Result: 416

The percentage change in site traffic is +5.70% which is a good sign for a webmaster. See the calculation below.

=416/7304
Result: 0.57, which is 5.70%

Let me explain how to calculate the percentage change as above in Google Sheets.

Calculating Percentage Change in Google Sheets

Non-Array Formula

=to_percent((C2-C3)/C3)

The formula =(C2-C3)/C3 calculates the percentage change in Google Sheets. But the formula would return the result as 0.0569550931 which needs to be formatted to percent. For that, I have included the to_percent function in the formula.

You can rewrite the formula as below using functions equivalent to the arithmetic operators.

=to_percent(divide(MINUS(C2,C3),C3))

Array Formula to Calculate Percentage Change of Values in a Column in Google Sheets

In a column in Google Sheets, I have month-wise (Jan to Dec) sales amount.

Here, if I want to calculate the monthly percentage changes from January to December, I can use a percentage change array formula in Google Sheets.

Sample Data

First, let’s write the non-array formula so that you can understand the array formula better.

% Change Non-Array Formula in a Column in Google Sheets

The above data is in the range A1:B13. Since the first row contains headers, leave cell C1 and use the below formula in cell C2 and drag down.

=TO_PERCENT(iferror((B2-B1)/B1,0))

In the very first cell in the formula applied cell, i.e. in cell C2, the formula (see the formula above) would return an error as the formula in that cell involve cell B1 which contains a text. To return 0.00% in that cell, I have used IFERROR.

Percentage Change Array Formula in a Column in Google Sheets

A non-array formula is OK for a small range as above. But if you have a large set of data, I mean data in many rows in column B, you can use my following array formula to calculate the percentage change in Google Sheets.

In C2:

=ArrayFormula(if(B2:B="",,IFERROR((B2:B-B1:B)/B1:B,0)))

Points to Be Noted:

• Do not copy down the formula.
• If you see #REF! error, make sure that C3:C is blank.
• Format the array C2:C to percent (Format > Number > Percent).
• Don’t leave any cell in the data range in B2:B blank. I mean no blank cells between B2 and B13 as per the example above.

That’s all. Enjoy!

Resources (Related)

• How to Use Percentage Value in Logical IF in Google Sheets.
• How to Use the Percentile Function in Google Sheets.
• The PERCENTRANK Functions in Google Sheets.
• How to Randomly Extract a Certain Percentage of the Rows in Google Sheets.
• Percentile Rank Wise Conditional Formatting in Google Sheets.
• How to Use the UNARY_PERCENT Function in Google Sheets.
• Average of Top N Percent of the Values in Google Sheets.
• Query to Filter a Column Contains Percentage Values in Google Sheets.
• Calculating the Percentage of Total in Google Sheets [How To].
• Percent Distribution of Grand Total in Google Sheets Query.