How to / Percentages and rounding
How to calculate percentage change in Excel
Use =(C2-B2)/B2, with the old value in B2 and the new one in C2, and format the cell as a percentage.
Growth from last year to this year
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Last year | This year | ||
| 2 | Jan | 400 | 500 | 25% | |
| 3 | Feb | 500 | 450 | ||
| 4 | Mar | 0 | 120 |
In E2: =(C2-B2)/B2 → 25%
From 400 to 500 is an increase of 100, a quarter of 400.
How it works
- The change is the new value minus the old one.
- Dividing by the old value turns the change into a share of where you started.
- A positive result is an increase and a negative one a decrease. The brackets matter: without them Excel divides first.
Variations
A decrease
A B C D E 1 Month Last year This year 2 Jan 400 500 -10% 3 Feb 500 450 4 Mar 0 120 In E2:
=(C3-B3)/B3→ -10%From 500 down to 450.
The same thing, written shorter
A B C D E 1 Month Last year This year 2 Jan 400 500 25% 3 Feb 500 450 4 Mar 0 120 In E2:
=C2/B2-1→ 25%When the old value is zero
A B C D E 1 Month Last year This year 2 Jan 400 500 n/a 3 Feb 500 450 4 Mar 0 120 In E2:
=IF(B4=0, "n/a", (C4-B4)/B4)→ n/aA change from nothing has no percentage. Without the test, the formula shows #DIV/0!.
Raise a number by a percentage
A B C D E 1 Month Last year This year 2 Jan 400 500 440 3 Feb 500 450 4 Mar 0 120 In E2:
=B2*(1+10%)→ 440400 plus 10%.
Watch out for
- Dividing by the new value. The change is measured against where you started, so the old value goes underneath. Dividing by the new one gives 20% here, which answers a different question.
- Percentage points. Going from 10% to 12% is an increase of 2 percentage points, but a 20% increase. Say which one you mean.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- IF: Returns one value if a condition is TRUE and another if it is FALSE.
- ROUND: Rounds a number to a given number of digits.
- IFERROR: Returns a fallback value if a formula results in an error.
- ABS: The absolute value: the number without its sign.