How to Calculate Percent Change in Excel (Step-by-Step)

Percent change is one of the most commonly needed calculations in spreadsheets — whether you're tracking sales growth, comparing monthly expenses, or analyzing survey results over time. Excel makes it straightforward once you understand the underlying formula and how to apply it correctly.

The Core Formula for Percent Change

The mathematical foundation is simple:

Percent Change = ((New Value − Old Value) / Old Value) × 100

In Excel, you don't need to multiply by 100 manually if you format the cell as a percentage. The working formula looks like this:

=(B2-A2)/A2 

Where A2 is the old (original) value and B2 is the new value. Format the result cell as a percentage and Excel handles the ×100 display automatically.

If you'd rather see the raw decimal (like 0.25 instead of 25%), leave the cell format as General or Number.

Setting Up Your Spreadsheet 📊

A clean layout makes percent change calculations easier to read and audit. A typical setup looks like this:

Once you enter the formula in C2, you can drag the fill handle down to apply it to every row automatically.

To format as a percentage:

1. Select the result cells (e.g., C2:C10)
2. Press Ctrl + Shift + % (Windows) or go to Home → Number → Percentage
3. Adjust decimal places using the increase/decrease decimal buttons

Handling Common Issues in Percent Change Formulas

Negative Values and Decreases

The same formula handles decreases correctly. If the old value is 250 and the new value is 200, the result is −0.20, which displays as −20% — indicating a 20% decrease.

Division by Zero Errors

If your old value is zero, Excel returns a #DIV/0! error because dividing by zero is mathematically undefined. You can suppress this cleanly with an IF statement:

=IF(A2=0,"N/A",(B2-A2)/A2) 

This displays "N/A" whenever the original value is zero, keeping your sheet readable.

Negative Base Values

When the old value is negative, the formula still runs without errors, but the result can be counterintuitive or misleading. For example, going from −50 to −25 produces a 50% increase mathematically — which may or may not reflect what you actually want to communicate. In these cases, it's worth considering whether a different metric (like absolute change) better serves your analysis.

Percent Change vs. Related Calculations 🔢

These terms are often confused but represent different things:

Choosing the wrong one is a common source of errors in reports. If you're comparing two time periods and one clearly came first, percent change is almost always the right choice. If neither value has a defined "original," percent difference is more appropriate.

Using Absolute References for Benchmarks

Sometimes you want to measure percent change against a fixed reference point — like a quarterly target or a baseline year — rather than a rolling previous value. In that case, use an absolute cell reference with a dollar sign:

=(B2-$A$1)/$A$1 

Here, $A$1 stays locked as the denominator no matter how far down you drag the formula. This is useful for dashboards where every row compares back to the same benchmark.

Displaying Percent Change Visually

For dashboards and reports, Excel offers a few ways to make percent change more scannable:

• Conditional formatting can color cells red for negative values and green for positive ones automatically
• Sparklines let you plot directional trend lines in a single cell
• Data bars add an inline visual bar proportional to each value's magnitude
• The TEXT function can embed percent change directly into a sentence: ="Sales changed by "&TEXT((B2-A2)/A2,"0.0%")

These options don't change the underlying calculation — they just affect how results surface in a report.

What Affects How You Apply This in Practice

The formula itself is universal, but how you implement it in a real workbook depends on several factors:

• Data structure — whether values are in rows or columns affects how you reference cells and drag formulas
• Data source — live-connected data (from Power Query or external feeds) may require different formula approaches than static tables
• Version of Excel — older versions lack dynamic array functions like XLOOKUP or LET, which can simplify more complex percent change setups
• Intended audience — a working analyst sheet and an executive summary dashboard have different formatting and error-handling needs
• Scale of the dataset — a few dozen rows versus tens of thousands changes whether manual formulas or Power Pivot calculations make more sense

The formula is the easy part. Where things get more variable is how your specific data is structured, what edge cases it contains, and what you ultimately need the output to do.