Ever tried to look at two numbers in a spreadsheet and figure out how much things actually moved? You stare at last month's sales, this month's sales, and your brain just stalls. That's where the formula for percent change in excel saves you — it turns a confusing gap into a number you can actually read.
I've watched people do this math by hand and get it wrong more times than I can count. Excel handles it in seconds. Also, 14" instead of "14%". But here's the thing — most folks type the wrong formula, or they format the cell like a plain number and wonder why it says "0.Let's fix that.
What Is the Formula for Percent Change in Excel
Look, at its core, percent change is just measuring the difference between an old value and a new value, then scaling that difference against the old one. In real terms, in Excel, you're not using some special function called PERCENTCHANGE. You build it from basic math.
The plain-English version: take the new number, subtract the old number, divide by the old number. That's it. In a cell, it looks like this:
=(New - Old) / Old
So if your old value is in cell B2 and your new value is in C2, you'd write:
=(C2-B2)/B2
And then — this part gets skipped constantly — you format the cell as a percentage. Without that, Excel shows a decimal and you'll think your business shrank by 0.Right-click, Format Cells, choose Percentage. Now, or hit the % button on the Home ribbon. 2 when it actually dropped 20%.
Absolute vs Relative References
Here's what most people miss. If you write =(C2-B2)/B2 and drag it down a column, Excel shifts those references automatically. That's usually what you want. But sometimes you're comparing everything to one fixed base number — say, a target in cell $B$2. Throw dollar signs in there: =(C2-B2)/$B$2. Now when you drag, the old value stays locked. Real talk, this small detail breaks more dashboards than anyone admits.
Percent Change vs Percent Difference
People mix these up. In real terms, percent difference* is just comparing two unrelated things. Plus, different formula, different story. Worth adding: percent change* means one value came before the other — time moved. Worth adding: for percent difference you'd divide by the average instead of the old value. For now we're talking about change over time, which is what 90% of spreadsheet users actually need.
Why It Matters
Why does this matter? Because most people skip it and then make decisions on vibes.
I once sat in a meeting where someone said "revenue's way up" because the raw number went from 10,400 to 11,900. Sounds good. In practice, same room, two stories, and only one of them was the real winner. Here's the thing — meanwhile another product went from 2,000 to 2,600 — that's 30%. But the percent change was only about 14%. The formula for percent change in excel shows you the truth behind the size of the numbers.
And when you get it wrong, bad things happen. You report a 200% gain when it was 2%. Still, you compare this year to last year but forget a negative base value and Excel spits out a nonsense result. On the flip side, or you show decimals to a boss who reads "0. 05" as "five" and panics. Formatting and formula together keep you credible.
In practice, this shows up everywhere: tracking website traffic, measuring weight loss, comparing monthly expenses, calculating investment returns, even grading student score improvements. The short version is — if two numbers represent a before and after, you want percent change, not just the delta. That's the whole idea.
How It Works
Let's build it properly. No fluff, just the steps that actually matter.
Step 1: Lay Out Your Data
Put your old values in one column, new values in the next. Because of that, column B = January sales. Column C = February sales. Now, row 2 is your first item. Simple.
Step 2: Write the Core Formula
Click D2. Type:
=(C2-B2)/B2
Hit Enter. You'll see a decimal. Don't panic. That's Excel doing exactly what you asked.
Step 3: Format as Percent
Select D2. So click the % icon on the ribbon, or press Ctrl+Shift+%. Boom — 0.14 becomes 14%. Now drag the formula down column D and every row calculates its own change.
Step 4: Handle Negative or Zero Old Values
It's where it gets tricky. If your old value is zero, the formula divides by zero and returns #DIV/0!.
=IF(B2=0,"n/a",(C2-B2)/B2)
If the old value is negative and new is positive, the percent change can look huge and misleading. Which means turns out there's no perfect fix — sometimes you note it manually. But at least the error doesn't crash your sheet.
Step 5: Use It Inside Bigger Formulas
Once you're comfortable, the formula for percent change in excel becomes a building block. Now, or use conditional formatting to turn big drops red. You can wrap it in AVERAGE to get mean change across rows. I know it sounds simple — but it's easy to miss how flexible this little equation is.
Alternative: The Quick Percent Style Trick
Some people skip the divide entirely and just use Excel's built-in "Show Values As > % Difference From" inside a PivotTable. But understanding the raw formula means you're not trapped when the pivot won't cooperate. That's legit. Learn the manual way first. Then cheat with pivots later.
If you found this helpful, you might also enjoy what is text structure in an analytical text or albert io ap lang score calculator.
Common Mistakes
Honestly, this is the part most guides get wrong — they list the formula and bounce. But the errors are where the learning lives.
Mistake 1: Forgetting to format as percent. You get 0.2 and read it as 0.2%. It's 20%. Huge gap.
Mistake 2: Reversing old and new. Write (B2-C2)/C2 and you've measured backward. Your sign flips. A gain looks like a loss.
Mistake 3: Dividing by the new value. Some folks do (C2-B2)/C2 because "that's the current number." Nope. Percent change is always relative to the starting point. Otherwise your math describes something else entirely.
Mistake 4: Dragging without locking when you should. If every row should compare to a single baseline, and you didn't use $B$2, your data lies by row three.
Mistake 5: Ignoring negatives. A change from -50 to 50 isn't a 200% gain in any useful sense for most reports. Know your context.
Practical Tips
What actually works when you do this week after week?
- Build a template. One sheet, columns for old, new, and % change, with the IF error trap already in place. Copy it every month. Worth knowing — future you will thank present you.
- Label everything. Put "Old (Jan)" and "New (Feb)" in row 1. A year from now you won't remember which was which.
- Use named ranges. Define
OldValandNewValif you're doing a one-off model. Makes the formula read like English. - Round for reports. Wrap in ROUND:
=ROUND((C2-B2)/B2,4)then format percent. Keeps the sheet clean. - Check the sign. Positive means up. Negative means down. Obvious, but under pressure people misread their own columns.
And look — don't overthink the "correct" number of decimal places. For a monthly report, whole percents are fine. For financial models, two decimals. Use your judgment.
FAQ
How do I calculate percent change in Excel between two columns?
Use =(NewCell-OldCell)/OldCell and format the result as a percentage. Drag down to apply to all rows.
What if the old value is zero in Excel percent change?
You'll get a divide-by-zero error. Use =IF(Old=0,"n/a",(New-Old)/Old) to show a
How do I calculate percent change in Excel between two columns?
Use =(NewCell-OldCell)/OldCell and format the result as a percentage. Drag down to apply to all rows.
What if the old value is zero in Excel percent change?
You'll get a divide‑by‑zero error. Use =IF(Old=0,"n/a",(New-Old)/Old) to show a clear placeholder instead of an error.
Can I calculate percent change for a whole column at once?
Yes. Enter the formula in the first cell (e.g., = (B2-C2)/C2) and then drag the fill handle down. To keep the reference to the baseline column fixed, use absolute references like = (B2-$D$2)/$D$2 if the baseline lives in column D.
How do I handle negative numbers correctly?
The formula works the same way; just remember that a negative old value flips the sign of the result. To give you an idea, moving from –10 to –5 is a +50 % change because (-5 - (-10))/(-10) = 0.5.
Is there a way to automatically flag large changes?
Absolutely. Combine the percent‑change formula with conditional formatting. Here's a good example: apply a green fill when the result > 0.10 and a red fill when < ‑0.10. This visual cue helps spot trends without digging through numbers.
Can I use named ranges to make the formula more readable?
Definitely. Define OldVal for the baseline column and NewVal for the current column. Then the formula reads =(NewVal‑OldVal)/OldVal, which is easier to audit and modify.
What about rounding for presentation?
Wrap the core calculation in ROUND before formatting as a percentage: =ROUND((NewVal‑OldVal)/OldVal,4). Four decimal places usually give enough precision for most reports while keeping the sheet tidy.
How do I export the percent‑change column for a dashboard?
Once the column is calculated, you can copy it as values (Paste Special > Values) into a new sheet or directly link it to a pivot table. The pivot’s “Show Values As > % Difference From” will then reflect your manually computed changes.
Final Thought
Mastering the simple (New‑Old)/Old formula gives you a reliable foundation for any analysis that involves growth, decline, or variance. Whether you’re building a quick monthly tracker, feeding data into a sophisticated financial model, or just trying to spot a trend before your teammates do, the ability to calculate percent change accurately—and avoid the common pitfalls—turns raw numbers into actionable insight. Keep the template handy, label your columns, and let the formula do the heavy lifting. Your future self will thank you for the clarity and the time saved.
