Percentage Change with Array Formula in Google Sheets

There’s no built-in function to calculate percentage change in Google Sheets. However, we can use an array or non-array formula with arithmetic operators or equivalent functions to do so.

If you’re familiar with or currently using Microsoft Excel, you may already know a basic percentage change formula that works equally well in Google Sheets. But chances are, you don’t have an array formula version of the percentage change calculation.

Change and % Change

“Change” is simply the difference between a new value and an old value. Percentage change expresses that difference as a percentage of the original (old) value.

Assume that, according to my Google Analytics stats, the traffic on my site was 7,720 visitors yesterday and 7,304 on the same day last week.

The change in site traffic is:

=7720 - 7304

Result: 416

The percentage change is:

=416 / 7304

Result: 0.057, or 5.70%, which is a good sign for a webmaster.

Let’s now see how to apply the percentage change formula in Google Sheets.

Calculating Percentage Change in Google Sheets

The generic formula is:

(current - previous) / previous

This is straightforward to calculate with a non-array formula. However, with an array formula, the challenge is getting access to the previous value.

Non-Array Formula

=TO_PERCENT((C2 - C3) / C3)

Example of percentage change formula in Google Sheets with values and results

The formula above calculates the percentage change between two values. By default, the result would be a decimal (e.g., 0.057), so I’ve wrapped it with TO_PERCENT to return a nicely formatted percentage.

You can also write it using functions equivalent to arithmetic operators:

=TO_PERCENT(DIVIDE(MINUS(C2, C3), C3))

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

Let’s say I have month-wise sales data (Jan to Dec) in a column. If I want to calculate monthly percentage changes, I can use a percentage change array formula in Google Sheets.

Sample Data

Example of percentage change array formula in Google Sheets with calculated results

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

Assume your data is in the range A1:B13. Since the first row contains headers, leave cell C1 and enter the following formula in cell C2. Then drag it down:

=TO_PERCENT(IFERROR((B2 - B1) / B1))

Why Use IFERROR?

The first row of your formula (C2) references B1, which contains a text header. Without IFERROR, that would return an error.

Percentage Change Array Formula in a Column in Google Sheets

A non-array formula is fine for small ranges, but if you’re working with many rows, you’ll want a dynamic approach. Here’s the percentage change with array formula in Google Sheets:

In cell C2:

=MAP(
   SEQUENCE(ROWS(B2:B13), 1, 0), B2:B13, 
   LAMBDA(pval, val, 
      TO_PERCENT(IFERROR((val-CHOOSEROWS(B2:B13, pval))/CHOOSEROWS(B2:B13, pval)))
   )
)

It calculates the percentage change between each value in column B and the value just above it.

Points to Note:

Do not copy down the formula manually — the array formula fills down automatically.
If you see a #REF! error, ensure C3:C is blank.

What it does:

SEQUENCE(ROWS(B2:B13), 1, 0) generates position indices starting from 0.
MAP(..., B2:B13, LAMBDA(pval, val, ...)) pairs each value (val) with its position (pval).
CHOOSEROWS(B2:B13, pval) fetches the previous row’s value.
It calculates (val - previous) / previous, then wraps it with TO_PERCENT to format as a percentage.
IFERROR(...) handles any errors (like dividing by zero or missing previous values).

That’s all. You now know how to use both non-array and array formulas to calculate percentage change in Google Sheets — even with large datasets.

Resources

1 COMMENT

Eeshita May 20, 2021 At 2:09 am

Thanks a ton, Prashanth for sharing the difference in excel and google sheet.