Percentage change in Google Sheets
Google Sheets uses the same arithmetic as Excel, with a few functions of its own that help with percentages: TO_PERCENT, ARRAYFORMULA for whole columns and GOOGLEFINANCE for share prices. Here is how to use them, and how the locale setting changes your formulas.
- Percent change for a whole column in one formula
- TO_PERCENT and the format menu
- RRI checked against Google's own examples
The basic formula
With the old value in A2 and the new value in B2, type =(B2-A2)/ABS(A2) in C2. Sheets shows a decimal such as -0.03. Format it with Format, Number, Percent, or the percent button in the toolbar, and it shows -3.00% for the calculator's example of 2,500 falling to 2,425.
ABS keeps the sign right when the old value is negative. For positive old values it changes nothing. The negative numbers guide explains the convention.
TO_PERCENT
TO_PERCENT turns a number into a percentage inside the formula, so the result shows as a percent even in an unformatted cell: =TO_PERCENT((B2-A2)/ABS(A2)). It is listed in Google's function list as TO_PERCENT(value). It changes only how the value is shown; the cell still holds -0.03, so later formulas can use it.
A whole column with ARRAYFORMULA
To fill a column without copying the formula down, put this in C2: =ARRAYFORMULA(IF(A2:A="","",IFERROR((B2:B-A2:A)/ABS(A2:A),"n/m"))) (decimal comma locales: =ARRAYFORMULA(IF(A2:A="";"";IFERROR((B2:B-A2:A)/ABS(A2:A);"n/m")))). It runs for every row that has an old value, leaves empty rows blank and writes n/m where the old value is zero. New rows added to the sheet are picked up automatically.
For change from the previous row in a single column of figures in B, put =ARRAYFORMULA(IF(B3:B1000="","",(B3:B1000-B2:B999)/ABS(B2:B999))) (decimal comma locales: =ARRAYFORMULA(IF(B3:B1000="";"";(B3:B1000-B2:B999)/ABS(B2:B999)))) in C3. The two ranges must be the same size, one row apart, so set the last row to cover your data.
RRI and compound growth
Sheets has RRI(number_of_periods, present_value, future_value), the CAGR as a decimal. Google's help page gives =RRI(3,2,4) (decimal comma locales: =RRI(3;2;4)) = 0.2599210499 and =RRI(1,10,0) (decimal comma locales: =RRI(1;10;0)) = -1, which our engine matches exactly. Google adds that the number of periods and the present value must be greater than zero. The CAGR calculator explains what to do when they are not.
For a chain of percentage changes in A2:A6: =ARRAYFORMULA(PRODUCT(1+A2:A6))-1.
Locale, decimal commas and semicolons
File, Settings, Locale sets how a spreadsheet formats numbers, dates and currency. Google's help page on locale settings confirms that changing the locale changes the default number formatting. Spreadsheets set to a locale with a decimal comma generally expect semicolons between function arguments; Google's page does not spell this out, so if a comma version shows a parse error, use the semicolon version that every calculator on this site writes for you.
Apple Numbers and LibreOffice Calc accept the same arithmetic formulas. The Excel guide covers PivotTables and the older array-formula keys.