Percentage change in Excel
Excel has no dedicated percent change function, so you write a formula. Here are the formulas that handle zero and negative starting values, previous-row changes, compound growth with RRI and chains of changes, with the semicolon forms for decimal comma settings.
- The basic formula and the Percentage format
- Guards for zero and negative starting values
- RRI for CAGR and PRODUCT for chains
The basic Excel percent change formula
Put the old value in A2 and the new value in B2, then in C2 type =(B2-A2)/A2. Excel shows a decimal; apply the Percentage format (Home, Number group, the percent button, or Ctrl+Shift+%) and it shows a percentage.
Microsoft's Calculate percentages page uses two monthly totals: =(2500-2342)/2342 returns 0.06746, shown as 6.75%, and =(2425-2500)/2500 returns -0.03, shown as -3.00%. The calculator above uses the first pair and writes the formula for whatever you type.
Handle zero and negative starting values
The plain formula has two weak spots. If A2 is zero, Excel shows #DIV/0!. If A2 is negative, the sign comes out backwards: from -10 to -25 the value fell, but =(B2-A2)/A2 returns +150.00%.
| Formula | Result for -10 to -25 | What it does |
|---|---|---|
=(B2-A2)/A2 | +150.00% | Plain; sign flips when A2 is negative |
=(B2-A2)/ABS(A2) | -150.00% | Divides by the size of A2, so a fall shows as negative |
=IF(A2=0,"n/m",(B2-A2)/ABS(A2)) | -150.00% | Same, and writes n/m when A2 is zero |
=IFERROR((B2-A2)/ABS(A2),"n/m") | -150.00% | Catches any error, including blank or text cells |
The ABS version follows the same convention as the calculators on this site. It is a convention, not a rule: the size of a percentage from a negative base is hard to interpret, and the negative numbers guide explains when to report the absolute change instead. IFERROR also hides mistakes such as text in a number cell, so the IF version is the safer choice for shared workbooks.
Change from the previous row
For a column of monthly figures in B2:B13, put =(B3-B2)/ABS(B2) in C3 and fill down. Each row compares itself with the row above. Leave C2 blank, because the first month has nothing to compare with.
For year-over-year change in monthly data, compare with the row twelve above: in C14, =(B14-B2)/ABS(B2). In a PivotTable you can get the same without formulas: right-click a value, choose Show Values As, then % Difference From, and pick (previous) as the base item.
Add or subtract a percentage
Microsoft's examples: =113*(1+0.25) (decimal comma locales: =113*(1+0,25)) gives 141.25 and =113*(1-0.25) (decimal comma locales: =113*(1-0,25)) gives 84.75. With the number in A2 and a percentage in B2: =A2*(1+B2) to increase, =A2*(1-B2) to decrease, and =A2/(1+B2) to find the value before an increase (a reverse percentage).
CAGR with RRI, and chains with PRODUCT
For compound annual growth, =RRI(C2,A2,B2) (decimal comma locales: =RRI(C2;A2;B2)) takes the number of years in C2, the start value in A2 and the end in B2. Microsoft's RRI example counts its periods in months but describes the result as a yearly rate; over 8 years the yearly rate is 1.20%, as the CAGR page explains. The written-out form =(B2/A2)^(1/C2)-1 gives the same answer.
For a chain of changes in A2:A6, typed as percentages, current Excel accepts =PRODUCT(1+A2:A6)-1. In older versions without dynamic arrays, confirm it with Ctrl+Shift+Enter. The successive change calculator shows why the chain is multiplied, not added.
Commas, semicolons and other languages
Excel takes its list separator from the computer's regional settings. Where the decimal mark is a comma, formulas use semicolons between arguments: =IF(A2=0,"n/m",(B2-A2)/ABS(A2)) (decimal comma locales: =IF(A2=0;"n/m";(B2-A2)/ABS(A2))). Every calculator on this site writes both forms for your numbers.
Excel in other languages also translates function names, so IF and ABS may have local names in your version. The formulas here use the English names; check your version's function list. Arithmetic with only + - * / and cell references works the same everywhere.