Hands-On Excel

Hands-On Excel Challenges Percent change

Excel percent change formula

Subtract the old value from the new one, then divide by the old one: =(C2-B2)/B2. A rise comes out positive and a drop negative. Format the cell as a percent and 0.25 shows as 25%.

CoreAbout 3 minutesFree, no sign-upBy Updated

Try it yourself

07 · Percent change · Core

Up or down by how much?

How much did each product's sales go up or down? In D2:D6, work out the percent change from last year to this year, then fill down. Column D is already formatted as a percent.

Percent change is (new − old) ÷ old: the difference, measured against where you started. Excel divides before it subtracts, so the brackets matter: =(C2-B2)/B2.

sales.xlsx
Ready

Write in the green cells, then press Check. Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

The guide · Percent change

The formula

Percent change compares the difference with where you started. Sales went from 1,200 to 1,500: that's 300 more, and 300 is 25% of 1,200.

=(new - old) / old

With last year in B2 and this year in C2, that's =(C2-B2)/B2. Fill it down for every row.

Worked example

ProductLast yearThis yearFormulaChange
Coffee1,2001,500=(C2-B2)/B225.0%
Tea800720=(C3-B3)/B3-10.0%
Cocoa400450=(C4-B4)/B412.5%

Tea went down, so its change is negative. You don't need an IF for that: new minus old gives the sign by itself.

The brackets matter

Excel divides before it subtracts. =C2-B2/B2 works out B2/B2 first, which is always 1, and takes 1 off this year's sales. Coffee would show 1,499 instead of 25%. The brackets make the subtraction happen first.

Divide by the old value

A change is measured against the starting point. 300 ÷ 1,500 is 20%, which answers a different question: how much of this year's sales is new. A quick check: going from 100 to 200 is +100%, and going back from 200 to 100 is -50%, not -100%.

Another way to write it

=C2/B2-1 gives the same answer. C2/B2 is this year as a share of last year (125%), and taking away 1 leaves the change (25%). Use whichever you can read at a glance.

When the old value is 0

Dividing by 0 gives #DIV/0!, and there's no percent change from nothing. Show a blank instead: =IF(B2=0,"",(C2-B2)/B2).

Common mistakes

FAQ

How do I calculate percent change in Excel?
Use =(new-old)/old, for example =(C2-B2)/B2, then format the cell as a percent with the % button.
How do I calculate a percentage decrease in Excel?
With the same formula. When the new value is smaller, the result is negative: going from 800 to 720 gives -10%.
Why is my percent change formula wrong?
Check the brackets first: =C2-B2/B2 divides before it subtracts. Then check that you divide by the old value, not the new one.
How do I avoid #DIV/0! in a percent change formula?
When the old value is 0 there is no percent change. Show a blank instead: =IF(B2=0,"",(C2-B2)/B2).
What is the difference between percent change and percentage points?
If a rate goes from 10% to 12%, it rose by 2 percentage points, which is a 20% change. Subtract the two rates for points; use the percent change formula for the relative change.

All challenges

  1. 01 Add it upSUM Starter · 3 min
  2. 02 Find the averageAVERAGE Starter · 3 min
  3. 03 Write once, fill downRelative references Starter · 3 min
  4. 04 Lock it with $Absolute references Core · 3 min
  5. 05 Lock just the row or the columnMixed references Advanced · 3 min
  6. 06 Percent of a totalPercentages Starter · 3 min
  7. 07 Up or down by how much?Percent change Core · 3 min
  8. 08 Select to the end in one moveCtrl + Shift + arrow Core · 3 min
  9. 09 Pass or fail?IF Core · 3 min
  10. 10 Both or either?AND, OR Core · 3 min
  11. 11 Grade bands with nested IFNested IF Core · 3 min
  12. 12 Count the ones that matchCOUNTIF Core · 3 min
  13. 13 Add up the ones that matchSUMIF Core · 3 min
  14. 14 Use a cell as the conditionCriteria in a cell Core · 3 min
  15. 15 Count with two conditionsCOUNTIFS Core · 3 min
  16. 16 Add up with two conditionsSUMIFS Core · 3 min
  17. 17 Look it upVLOOKUP Core · 3 min
  18. 18 Find the right bracketVLOOKUP approximate match Core · 3 min
  19. 19 XLOOKUP, the modern wayXLOOKUP Core · 3 min
  20. 20 Where is it in the list?MATCH Core · 3 min
  21. 21 INDEX and MATCH togetherINDEX + MATCH Advanced · 3 min
  22. 22 Catch the error, not the bugIFERROR Core · 3 min
  23. 23 Split first and last namesLEFT, MID + FIND Core · 3 min
  24. 24 Mistake hunt: #N/A everywhereFixing #N/A Core · 3 min
  25. 25 Mistake hunt: hard-coded numbersReferences instead of typed numbers Core · 3 min
  26. 26 Mistake hunt: numbers stored as textNumbers stored as text Core · 3 min

Pro · coming soon

From first formula to job-ready.

100+ challenges on lookups, pivot tables, dates and text cleanup, an interview track modeled on the Excel tests employers use, and your progress saved across devices.

$29 one-time · early-bird price

You won't be charged today.

You're early!

Pro isn't open yet. We're building it right now. Leave your email and we'll hold the $29 early-bird price for you and tell you the day it launches.

We'll email you a link to confirm, then one email when Pro launches. Privacy