The percentage formula in Excel: change, and growth

Two percentage formulas cover most of what people need: how much something changed, and what it will be after it grows by a given percentage. The sheet above is the lesson; this page explains both, with an example worked out by the same formula engine.

Percentage change

=(new - old) / old

Subtract the old number from the new one, then divide by the old one, the number you started from. The answer is a fraction: 0.25 means 25%. A percentage format on the cell shows it with the % sign; the value in the cell stays 0.25. A negative answer is a fall.

In this example, set in the Plant nursery, column B is last year and column C this year.

In D2, =(C2-B2)/B2 gives 39.8%. Filled down the column, each row works out its own change.

Why the brackets matter

Excel divides and multiplies before it adds and subtracts, as in school arithmetic. Leave out the brackets and =C2-B2/B2 divides B2 by itself first, then takes that 1 from C2: it gives 31500.0%. The brackets make the subtraction happen first. The lesson names this mistake when you make it.

The other common slip is dividing by the wrong year: =(C2-B2)/C2 gives 28.5%, a different answer that looks just as believable. Always divide by where you started.

Growing a number by a percentage

=number * (1 + rate)

To add 10% (the 0.1 in G2) to a number, multiply it by 1 plus the rate: 100% of what you have, plus the growth. Multiplying by the rate alone gives only the growth itself: =ROUND(C2*$G$2,0) gives 32. For a fall, use 1 minus the rate.

Here the rate sits in G2, so the plan can be changed in one place. Because the formula is copied down, G2 gets dollar signs: $G$2 stays put while C2 moves row by row. ROUND keeps the answer to whole units: =ROUND(number, digits) rounds to that many decimals, and 0 means a whole number. Put together, =ROUND(C2*(1+$G$2),0) gives 348.

ROUND, and what a format only shows

A number format only changes how a cell looks: a cell showing 12 might hold 11.6, and a total of such cells can be one off from the sum of what you see. ROUND changes the value itself. Excel rounds halves away from zero, so 2.5 becomes 3 and -2.5 becomes -3. ROUNDUP and ROUNDDOWN always go one way.

The lesson, step by step

1. 1 Work out the change

   In D2: =(C2-B2)/B2 gives 39.8%.

   Named mistake: =C2-B2/B2 gives 31500.0%. Excel divides before it subtracts, so that sum came out wrong. Put brackets round C2-B2.

   Named mistake: =(C2-B2)/C2 gives 28.5%. Divide by last year, the number you started from, not this year.

   Named mistake: =(C2-B2)/B2*100 gives 3982.3%. Leave out *100. The column is set to show a percentage, so Excel does that part.

2. 2 Copy the change down

   Fill D2 down over D2:D8. The last one, D8, becomes =(C8-B8)/B8.

   They give -8.1% 45.2% -9.3% 29.9% -18.7% -30.0%

3. 3 Plan for 10% more

   In E2: =ROUND(C2*(1+$G$2),0) gives 348.

   Named mistake: =ROUND(C2*$G$2,0) gives 32. That's only the growth itself. Add it to what you already have: C2*(1+$G$2).

   Checked too: Right here, but G2 will move when you copy. Lock it as $G$2.

4. 4 Copy the plan down

   Fill E2 down over E2:E8. The last one, E8, becomes =ROUND(C8*(1+$G$2),0).

   They give 37 50 54 139 96 31

In real Excel

Typing 10% into a cell stores 0.1 and formats the cell as a percentage, so =C2*(1+G2) works whether G2 shows 10% or 0.1. Typing 10 instead of 10% gives growth of a thousand percent, a classic. The % button on the Home tab formats a fraction as a percentage; it doesn't multiply anything. For a share of a total, see the absolute reference lesson.

Keep going

1. 4
   Next up is Keyboard: Excel shortcutsWrite, copy and total a sheet without touching the mouse. Needs a keyboard.
2. 2
   Copying: absolute referenceCopy a formula down a column, then lock one cell with $ so it stays put.
3. 5
   Decisions: IF functionLet the sheet decide: IF for one condition, AND when both must hold.