How to Calculate Percent Change in Excel (and Avoid #DIV/0!)

"Sales are up 25 percent" is one of the most common sentences in any office report, and one of the easiest to get wrong in a spreadsheet. The formula itself is short. The trouble comes from three places: dividing by the wrong number, a starting value of zero, and confusing percent change with percentage points. This post walks through each one with a small table you can rebuild in a minute.

The percent change formula

Microsoft's guide to calculating percentages describes the method in one sentence: subtract the original value from the new value, then divide the result by the original value. In cell terms, with the old number in B2 and the new number in C2:

=(C2-B2)/B2

Microsoft's own example uses earnings of $2,342 in November and $2,500 in December. The formula =(2500-2342)/2342 returns 0.06746, which displays as 6.75% once you apply the Percent Style button on the Home tab. The same page then goes from $2,500 in December to $2,425 in January: =(2425-2500)/2500 returns -0.03, or -3.00%. A negative result simply means a decrease.

The part people get wrong is the denominator. Always divide by the old value, the one you are measuring change from. Dividing by the new value answers a different question and gives a different number.

A worked example

Here is a small regional sales table. Column D uses the formula above, filled down from D2 to D5.

Region	Last year (B)	This year (C)	Change (D)
North	4,000	5,000	25.00%
South	5,000	4,000	-20.00%
East	2,500	2,500	0.00%
West	0	1,200	#DIV/0!

Two things stand out.

First, North and South moved by the same 1,000 units in opposite directions, but the percentages are not mirror images. Going from 4,000 to 5,000 is a 25 percent increase, while going from 5,000 to 4,000 is a 20 percent decrease, because each one divides by a different starting value. This is also why a 25 percent rise followed by a 20 percent fall brings you back exactly where you started: 4,000 times 1.25 is 5,000, and 5,000 times 0.80 is 4,000.

Second, West shows an error. That region had no sales last year, so the formula tries to divide by zero.

A shorter version of the same formula, =C2/B2-1, returns identical results for the first three rows and the same error for West. Use whichever one you find easier to read.

Handling a zero or blank starting value

Microsoft's documentation on the #DIV/0! error explains that Excel shows it when a number is divided by zero, including when a formula refers to a cell that contains 0 or is blank. A percent change from zero is not a meaningful number, so the right fix is to show something more useful than an error.

Microsoft's suggested approach is to test the denominator with IF first. Applied to this table:

=IF(B2,(C2-B2)/B2,"New")

When B2 holds any nonzero number, IF treats it as true and calculates the change. When B2 is 0 or empty, the formula returns the word "New" instead. In the table above, North still shows 25 percent and West now shows "New."

You can also wrap the formula in IFERROR:

=IFERROR((C2-B2)/B2,"New")

This gives the same results here. Microsoft adds a warning worth taking seriously: IFERROR is a blanket error handler that hides every error, not just division by zero. If someone types "5,000 units" into C2 by mistake, IFERROR quietly shows "New" for that row too, while the IF version shows #VALUE! and flags the problem. The IF version only reacts to the denominator, so it is the safer choice when you want other mistakes to stay visible.

Percent of a total

The other common percentage question is "what share of the total is this?" Put the total in its own cell, for example =SUM(C2:C5) in C6, which gives 12,700 for this year's column. Then divide each region by it:

=C2/$C$6

The dollar signs lock the reference to C6, so the formula keeps pointing at the total when you fill it down. The results are 39.37% for North, 31.50% for South, 19.69% for East and 9.45% for West. Those rounded figures add up to 100.01%, which is normal rounding noise. If a report must total exactly 100, round each share deliberately and adjust the largest one, rather than assuming displayed percentages will sum cleanly.

Microsoft's page shows the same idea with a simpler example: 42 correct answers out of 50 is =42/50, or 84 percent.

Percent change versus percentage points

When the values you compare are already percentages, be careful with wording. If a profit margin goes from 10 percent to 12 percent, it rose by 2 percentage points. Its percent change, using the same formula, is =(0.12-0.10)/0.10, which is 0.2, or a 20 percent increase. Both statements are correct, but they describe different things, and mixing them up can make a small change sound large or a large one sound small. Say which one you mean in the label.

Quick checklist before you share the numbers

1. Divide by the old value, not the new one.
2. Apply Percent Style instead of multiplying by 100, so the cell still holds a true fraction that other formulas can use.
3. Decide what a zero starting value should display, and use IF to handle it.
4. Use dollar signs on the total cell when calculating shares.
5. Label percentage points as points.

Key takeaways

• Percent change is (new minus old) divided by old; in Excel that is =(C2-B2)/B2, formatted with Percent Style.
• Equal changes up and down do not give equal percentages, because each divides by its own starting value.
• A zero or blank starting value causes #DIV/0!; =IF(B2,(C2-B2)/B2,"New") handles it without hiding other errors.
• IFERROR hides all errors, so use it only once you know the formula is otherwise correct.
• A change from 10 percent to 12 percent is 2 percentage points, or a 20 percent increase.