The basic formula for percentage change

To calculate percentage change in Excel, subtract the old value from the new value, divide by the old value, and multiply by 100. The formula is: ((New Value – Old Value) / Old Value) * 100

In Excel syntax, if your old value is in cell A1 and your new value is in cell B1, you would write: =((B1-A1)/A1)*100

This gives you the percentage increase or decrease. A positive result means growth; a negative result means decline. For example, if a price rose from $50 to $75, the formula returns 50, meaning a 50% increase.

Key Takeaways

  • The percentage change formula is ((New Value – Old Value) / Old Value) * 100, entered in Excel as =((B1-A1)/A1)*100 when your old value is in A1 and new value is in B1.
  • You can format the result as a percentage by selecting the cell, right-clicking, choosing Format Cells, and selecting Percentage from the Category list.
  • Copying the formula down a column lets you calculate percentage change for multiple rows at once by selecting the cell with the formula and dragging the fill handle down.
  • If your old value is zero, Excel will show a #DIV/0! error because you cannot divide by zero; in these cases you may need to handle the calculation differently or note that the change is undefined.

Setting up your data in columns

Organize your data so the old values are in one column and the new values are in another. For example, put January sales in column A and February sales in column B. This layout makes it easy to write one formula and copy it down for all your rows.

Label your columns clearly — "Old Value" and "New Value" or "2023" and "2024" — so you remember which direction the calculation runs. The order matters: if you reverse it, you get the opposite sign.

Formatting the result as a percentage

After you enter the formula, Excel shows the result as a decimal number. To display it as a percentage, select the cell with your formula, right-click, and choose Format Cells. In the dialog box, click the Number tab, select Percentage from the Category list, and click OK.

Excel will automatically multiply your result by 100 and add the % symbol. If you used the *100 in your formula, you will see numbers like 5000% instead of 50%. To avoid this, use the formula =((B1-A1)/A1) without the *100, then format as percentage.

Copying the formula to multiple rows

Once you have entered the formula in one cell, you can apply it to all rows at once. Click the cell containing your formula, then position your cursor over the small square in the bottom-right corner of the cell (the fill handle) until it turns into a plus sign.

Click and drag the fill handle down to the last row of data. Excel automatically adjusts the cell references for each row — so row 2 uses A2 and B2, row 3 uses A3 and B3, and so on. Release the mouse button and the formula fills all selected cells.

Handling zero and negative values

If your old value is zero, Excel returns a #DIV/0! error because the formula divides by zero, which is undefined. You cannot calculate a meaningful percentage change from zero to any other number using the standard formula.

For negative values, the formula still works mathematically, but the result can be confusing. If you go from -$50 to $50, the formula shows 200% change, which is technically correct but may not match how you think about the change. Consider whether the standard formula makes sense for your data before using it.

If you need to handle zero values, you can use an IF statement to check for zero first: =IF(A1=0,"N/A",((B1-A1)/A1)*100). This displays "N/A" instead of an error when the old value is zero.

Common mistakes to avoid

The most frequent error is reversing the order of subtraction. Remember: new value minus old value, not the other way around. If you subtract new from old, you get the opposite sign.

Another mistake is forgetting to divide by the old value. Some people divide by the new value instead, which gives a different (and incorrect) result. The denominator must always be the starting point.

If you format as percentage after including *100 in your formula, you will see inflated numbers. Either remove the *100 from the formula or do not format as percentage — use one method or the other, not both.

Frequently Asked Questions

What if I want to show the change as a decimal instead of a percentage?

Use the formula without multiplying by 100: =((B1-A1)/A1). Format the cell as a number with decimal places. A result of 0.50 means 50% change.

Can I calculate percentage change for negative numbers?

Yes, the formula works with negative numbers, but the result can be hard to interpret. Going from -$100 to -$50 shows a 50% increase (the value got less negative). Test the formula with your specific numbers to make sure the result makes sense for your situation.

How do I show percentage change as positive or negative without the % symbol?

Use the formula =((B1-A1)/A1)*100 and format the cell as a number instead of percentage. This shows results like 50 or -25 instead of 50% or -25%.

What does a negative percentage change mean?

A negative result means the value decreased. For example, -20% means the new value is 20% lower than the old value. Positive percentages indicate growth or increase.