How do I calculate percentage change in a spreadsheet?
Short answer
Subtract the old value from the new value, divide by the old value, and format the result as a percentage. With the old value in A2 and the new value in B2, enter =(B2-A2)/A2. A rise from 1,200 to 1,290 gives 0.075, which displays as 7.5 percent. A negative result is a decrease.
Percentage change measures how much a value moved relative to where it started. The formula is the same in Excel, Google Sheets, LibreOffice Calc and Numbers:
=(new - old) / old
How do I enter and format a percentage change formula?
- Put the old value in A2 and the new value in B2.
- In C2, enter
=(B2-A2)/A2. - Format C2 as a percentage. In Excel use Home > Number, then the Percent Style button, or press Ctrl+Shift+%. In Google Sheets and LibreOffice Calc, press Ctrl+Shift+5, or use Format > Number > Percent in Sheets and Format > Cells > Percent in Calc. In Numbers, select the cell and set Format > Cell > Data Format to Percentage.
- Fill the formula down for the other rows.
Format as percentage after entering the formula. The cell holds 0.075, and the format multiplies by 100 for display. Do not multiply by 100 in the formula as well, or you will get 750 percent.
What do rises and drops look like as percentage changes?
| Old | New | Formula | Result |
|---|---|---|---|
| 1,200 | 1,290 | =(B2-A2)/A2 |
7.5% |
| 80 | 60 | =(B3-A3)/A3 |
-25.0% |
The two directions are not symmetrical. A drop from 80 to 60 is minus 25 percent, but a rise from 60 back to 80 is plus 33.3 percent, because the base is smaller. A 25 percent increase from 60 reaches only 75.
To apply a known percentage, multiply by one plus the rate: a 7.5 percent increase on 1,200 is =1200*(1+7.5%), which gives 1,290.
What is the difference between percent change and percentage points?
If an interest rate moves from 4% to 5%, it rose by 1 percentage point, but the percentage change is (5% - 4%) / 4% = 25%. Report the one you mean. In a sheet, subtract the two rates for points and use the full formula for percent.
How do I calculate percentage change over several years or periods?
For growth across years, the compound annual growth rate is:
=(end/start)^(1/years)-1
A value that goes from 100 to 150 over 5 years has a CAGR of (150/100)^(1/5)-1, which is 8.45%. This is not the same as dividing the total change of 50% by 5.
For a time series in one column, put the formula in row 3 as =(B3-B2)/B2 and fill down. The first row has nothing to compare against, so leave it blank. The GDP by country dataset is a typical case, where year-over-year change shows growth, though you must be clear whether the series is in current or constant prices (see nominal GDP vs GDP at PPP).
What goes wrong with a percentage change formula?
- An old value of 0 gives
#DIV/0!. Wrap the formula:=IF(A2=0,"",(B2-A2)/A2)or useIFERROR. Percentage change from zero is undefined, so blank is more honest than 0%. - With negative values the sign misleads. From -50 to -30 the result of
(B2-A2)/A2is -40%, although the value improved. Divide by the absolute value instead:=(B2-A2)/ABS(A2)gives +40%. Say which convention you use. - Keep the order straight. Swapping old and new flips the sign and the base.
- Percentages of percentages confuse readers. Label the column "% change" and keep the units clear.
- Rounding in the display does not change the stored value. Use
ROUNDonly when the rounded figure feeds later calculations.