Percent Change Formula in Excel (With Examples)
By ExcelReady TeamUpdated 6 min read
The short answer
The percent change formula in Excel is =(new-old)/old. If last year's sales are in B2 and this year's in C2, type =(C2-B2)/B2, then press Ctrl+Shift+% to show it as a percentage. A positive result is an increase and a negative result is a decrease. Always divide by the old value.
Key takeaways
- Percent change = (new - old) / old, for example =(C2-B2)/B2.
- Always divide by the old value; dividing by the new value gives the wrong answer.
- Don't multiply by 100; apply the % format with Ctrl+Shift+% instead.
- A negative result means the value fell.
- To grow a number by a percent, multiply by (1 + percent): =C7*(1+B9).
On this page+
- What is the percent change formula in Excel?
- A worked example: product sales
- How to format the result as a percentage
- Why you must divide by the old value
- How to read negative percent changes
- Percent change vs percent of total
- Grow or reduce a number by a percent
- Common problems with percent change
- Practise it
The percent change formula in Excel is =(new-old)/old: subtract the old value from the new one, then divide by the old value. Format the result as a percentage and you can see at a glance what grew and what fell. This guide walks through a sales table, the mistakes that give wrong percentages, and how to grow a number by a percent.
What is the percent change formula in Excel?#
Percent change shows how much a value grew or fell compared with where it started:
=(new - old) / oldIn a sheet, point to cells. With last year in B2 and this year in C2:
=(C2-B2)/B2The parentheses matter. Excel divides before it subtracts, so =C2-B2/B2 would calculate B2/B2 first (which is 1) and then subtract it from C2. That's the wrong answer. The rule is explained in Excel order of operations.
=C2/B2-1 gives the same result. Both are correct; the first is easier to explain in an interview.
A worked example: product sales#
Here is a café's sales by product, last year and this year:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Last year | This year | Change | % change |
| 2 | Coffee | 42,000 | 46,200 | 4,200 | 10% |
| 3 | Tea | 18,500 | 17,205 | -1,295 | -7% |
| 4 | Pastries | 26,000 | 31,200 | 5,200 | 20% |
| 5 | Sandwiches | 33,400 | 35,070 | 1,670 | 5% |
| 6 | Merch | 5,000 | 4,250 | -750 | -15% |
| 7 | Total | 124,900 | 133,925 | 9,025 | 7.2% |
You can build it in two steps or one.
Two steps: change, then percent#
- In D2, type
=C2-B2(new minus old). Fill down to D6 with Ctrl+D (Mac: ⌘D). - In E2, type
=D2/B2. Fill down to E6.
One step#
In E7, the total row, type =(C7-B7)/B7. The result is 0.0723. The Percent style shows it as 7%, and one extra decimal place shows 7.2%. The totals in B7 and C7 are ordinary SUM formulas, as covered in the beginner's guide to SUM.
How to format the result as a percentage#
Excel shows percent change as a decimal, like 0.1, until you format it. In Excel, 10% is stored as 0.1.
- Select the cells and press Ctrl+Shift+% (Mac: ⌃+Shift+%).
- Or use the % button on the Home tab, in the Number group.
Don't multiply by 100 in the formula. =D2/B2*100 gives 10, and the % format then shows 1000%. Let the format do the multiplying. The Percent style shows whole percentages; use Increase Decimal on the Home tab to show 7.23% instead of 7%. More options are in format numbers as currency, percent and dates.
Why you must divide by the old value#
Percent change is always measured against where you started. Take a price that drops from 50 to 40:
| Formula | Divides by | Result | Correct? |
|---|---|---|---|
=(40-50)/50 | Old value | -20% | Yes |
=(40-50)/40 | New value | -25% | No |
Dividing by the new value is the most common mistake in job tests, and it's easy to miss because the answer still looks reasonable. A quick check: a value that doubles, from 100 to 200, should show 100%.
How to read negative percent changes#
A negative result means the value fell. In the café table, Tea is -7%: it sold 7% less than last year. Merch fell the most, by -15%.
When you compare several rows, sort or scan for the most negative number to find the biggest drop, and the largest positive number for the biggest gain. Here Pastries grew the most, by 20%. Sales and operations teams report changes like these every week; see Excel skills for sales and operations roles.
Percent change vs percent of total#
Percent change compares a value with its own past. Percent of total compares a value with a total at the same time. Both divide one number by another, so people mix them up.
| Question | Formula | Coffee result |
|---|---|---|
| How much did Coffee grow? | =(C2-B2)/B2 | 10% |
| What share of this year's sales is Coffee? | =C2/$C$7 | 34.5% |
The second formula locks the total with $ so it stays on C7 when you fill down.
Grow or reduce a number by a percent#
To increase a number by a percent, multiply it by (1 + percent). To decrease it, multiply by (1 - percent).
| Goal | Formula | Example | Result |
|---|---|---|---|
| Grow by 8% | =C7*(1+B9) with 8% in B9 | 133,925 grown by 8% | 144,639 |
| Cut by 15% | =B2*(1-15%) | 42,000 less 15% | 35,700 |
| Find the old value | =new/(1+percent) | 108 after 8% growth | 100 |
1 + 8% is 108%: the whole amount plus 8% more. Keep the percent in its own cell, like B9, instead of typing 1.08. Then you can change the goal once and every formula updates.
If several rows use the same rate, lock that cell with $ before you fill down, like =C2*(1+$B$9). Without the $, the reference moves to B10, B11 and so on. Absolute references and the $ sign explains why.
Common problems with percent change#
- #DIV/0! error. The old value is 0 or empty, so there's nothing to divide by. Percent change from zero has no meaning, so show a label instead:
=IFERROR((C2-B2)/B2,"n/a"). See IFERROR in Excel. - Negative old values. If the old value is negative, such as a loss, the sign of the result can mislead. Some analysts divide by
ABS(B2)in that case; say which method you used. - Wrong totals feeding the formula. If the total row is wrong, its percent change is wrong too. Check the SUM ranges first with why your Excel total is wrong.
- Percent vs percentage points. A margin that moves from 10% to 12% rose by 2 percentage points, which is a 20% increase. Say which one you mean.
Practise it#
Practise percentages and percent change in a live spreadsheet: you'll build the café table above, format it with Ctrl+Shift+%, find the product that fell the most and set next year's target. Then try the website-visits exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Monthly website visits went from 8,000 to 9,200. In C2, type =(B2-A2)/A2 and press Enter.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | % change | |||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 |
Tip: New minus old goes inside parentheses, then divide by the old value in A2.
Practise this in a lesson
Frequently asked questions
What is the formula for percent change in Excel?
Use =(new-old)/old, for example =(C2-B2)/B2, then format the cell as a percentage. =C2/B2-1 gives the same result.
How do I calculate a percentage decrease in Excel?
Use the same formula. If the new value is lower, the result is negative: a drop from 5,000 to 4,250 gives -15%.
Why does my percent change show 1000% instead of 10%?
You probably multiplied by 100 and then applied the % format, which multiplies by 100 again. Remove the *100 from the formula.
How do I avoid #DIV/0! in a percent change formula?
The old value is 0 or empty, so Excel can't divide by it. Wrap the formula in IFERROR, for example =IFERROR((C2-B2)/B2,"n/a").
What is the difference between percent change and percentage points?
Going from 10% to 12% is a change of 2 percentage points, but a 20% increase. Percentage points subtract the two rates; percent change divides by the old one.