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

ABCDE
1MonthLast yearThis year
2Jan40050025%
3Feb500450
4Mar0120

In E2: =(C2-B2)/B2 → 25%

From 400 to 500 is an increase of 100, a quarter of 400.

How it works

  1. The change is the new value minus the old one.
  2. Dividing by the old value turns the change into a share of where you started.
  3. A positive result is an increase and a negative one a decrease. The brackets matter: without them Excel divides first.

Variations

  1. A decrease

    ABCDE
    1MonthLast yearThis year
    2Jan400500-10%
    3Feb500450
    4Mar0120

    In E2: =(C3-B3)/B3 → -10%

    From 500 down to 450.

  2. The same thing, written shorter

    ABCDE
    1MonthLast yearThis year
    2Jan40050025%
    3Feb500450
    4Mar0120

    In E2: =C2/B2-1 → 25%

  3. When the old value is zero

    ABCDE
    1MonthLast yearThis year
    2Jan400500n/a
    3Feb500450
    4Mar0120

    In E2: =IF(B4=0, "n/a", (C4-B4)/B4) → n/a

    A change from nothing has no percentage. Without the test, the formula shows #DIV/0!.

  4. Raise a number by a percentage

    ABCDE
    1MonthLast yearThis year
    2Jan400500440
    3Feb500450
    4Mar0120

    In E2: =B2*(1+10%) → 440

    400 plus 10%.

Watch out for

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

Practise it

Related