The Basic Formula for Percentage Change

To calculate percentage change in Excel, use this formula: =(New Value – Old Value) / Old Value. Excel will return a decimal; multiply by 100 to see it as a percentage, or format the cell as a percentage and Excel does the multiplication for you.

For example, if a product cost $50 last month and costs $60 now, the formula is =(60-50)/50, which gives 0.2 or 20% when formatted as a percentage. The old value always goes in the denominator—that's what you're measuring change against.

The formula works the same way whether the change is positive (an increase) or negative (a decrease). A drop from $60 to $50 gives =(50-60)/60, which returns -0.167 or about -16.7%.

Key Takeaways

  • The percentage change formula is (New Value – Old Value) / Old Value, entered as =(B2-A2)/A2 if your old value is in A2 and new value is in B2.
  • Format the result cell as a percentage to display the decimal as a whole number with the % symbol, rather than multiplying by 100 manually.
  • Negative results mean a decrease; positive results mean an increase, and the formula works for any pair of numbers including revenue, inventory, or measurements.
  • Use absolute references ($A$2) when copying the formula down a column so the denominator stays fixed while the numerator changes for each row.

Setting Up Your Data in Columns

Arrange your data so the old value is in one column and the new value is in the next. For instance, put January sales in column A and February sales in column B. This layout makes it straightforward to write the formula once and copy it down for multiple rows.

Label your columns clearly—"Previous Month" and "Current Month" or "2023" and "2024"—so you know which direction the change flows. The order matters: if you reverse old and new, your percentage will have the opposite sign.

Writing and Copying the Formula

Click the cell where you want the result to appear. Type =, then click the cell with the new value, type a minus sign, click the cell with the old value, type a forward slash, and click the old value cell again. Your formula might look like =(B2-A2)/A2.

Press Enter. The result appears as a decimal. To copy this formula down to other rows, click the cell with the formula, then drag the small square at the bottom-right corner of the cell down as far as you need. Excel adjusts the row numbers automatically—A2 becomes A3, B2 becomes B3, and so on.

If you want the old value column to stay the same while the new value changes, use an absolute reference for the old value: =(B2-$A$2)/$A$2. The dollar signs lock that cell in place when you copy the formula down.

Formatting as a Percentage

After you enter the formula, the cell usually shows a decimal like 0.2 or -0.167. To display this as a percentage, right-click the cell and choose "Format Cells." Select "Percentage" from the Category list on the left, choose how many decimal places you want to see, and click OK.

Alternatively, select the cell and click the percentage button (%) in the toolbar. Excel multiplies the decimal by 100 and adds the % symbol. If your result shows as 2000% instead of 20%, the cell was already formatted as a percentage before you entered the formula—click Undo, format the cell as a number first, then re-enter the formula.

Handling Zero and Negative Old Values

If the old value is zero, the formula returns an error (#DIV/0!) because you cannot divide by zero. In this case, percentage change is not meaningful—you cannot compare a change from nothing. Consider using a note like "N/A" in that cell instead, or use an IF statement to check for zero: =IF(A2=0,"N/A",(B2-A2)/A2).

If the old value is negative, the formula still works mathematically, but the result can be confusing. For example, if a temperature dropped from -10 degrees to -5 degrees, the formula shows a 50% increase, even though the number got larger. In these cases, document what the percentage means so readers understand the context.

Real-World Examples in a Spreadsheet

Suppose you track quarterly revenue. Column A shows Q1 revenue ($50,000), column B shows Q2 revenue ($65,000). In column C, enter =(B2-A2)/A2 and format as a percentage. The result is 30%, meaning revenue grew by 30%.

For inventory, column A might be "Stock on Hand" (200 units), column B "Stock Today" (150 units). The formula =(B2-A2)/A2 returns -0.25 or -25%, showing inventory dropped by a quarter. If you have ten products, write the formula once in C2 and copy it down to C11—Excel updates the row numbers for each product automatically.

Common Mistakes to Avoid

The most common error is reversing old and new. Always subtract the old value from the new value, not the other way around. Reversing them flips the sign of your result, which changes the meaning entirely.

Another mistake is forgetting to format the cell as a percentage after entering the formula. The math is correct, but 0.2 looks like a tiny number until you format it as 20%. Also, watch for absolute references: if you copy a formula with $A$2 down multiple rows, it will always compare to row 2, which is usually not what you want unless you are comparing everything to a single baseline.

Frequently Asked Questions

What if I want to show percentage change as a whole number without decimals?

Right-click the cell, choose Format Cells, select Percentage, and set decimal places to 0. Excel will round 20.5% to 21% or 20.4% to 20%, depending on the rounding rule.

Can I calculate percentage change for negative numbers?

Yes, the formula works, but the result can be misleading. If you go from -10 to -5, the formula shows +50%, which is mathematically correct but may confuse readers. Always explain what the numbers represent so the context is clear.

How do I compare multiple items to a single baseline value?

Use an absolute reference for the baseline. If your baseline is in A1 and you want to compare B2, B3, and B4 to it, use =(B2-$A$1)/$A$1 in the first row, then copy down. The $A$1 stays locked while B2 changes to B3, B4, and so on.

What does a negative percentage change mean?

A negative percentage means a decrease. If sales dropped from $100 to $80, the formula returns -0.2 or -20%, showing a 20% decline. Positive percentages show increases.

Why does my formula show an error?

The most common cause is dividing by zero—your old value cell is empty or contains 0. Check that the old value cell has a number in it. If it does, make sure you are dividing by the old value, not the new value.