The Basic Percent Change Formula in Excel

Percent change measures how much a value has increased or decreased as a percentage of its original amount. In Excel, the formula is: (New Value – Old Value) / Old Value, then multiply by 100 to get a percentage. You can write this in a single cell or break it across multiple columns depending on your data layout.

If your old value is in cell A1 and your new value is in cell B1, the formula looks like this: =(B1-A1)/A1*100. This gives you the percent change as a number — for example, 25 means a 25% increase. If the result is negative, the value decreased.

You do not need to multiply by 100 if you format the cell as a percentage instead. The formula =(B1-A1)/A1 will display as 0.25, and when you apply percentage formatting, Excel shows it as 25% automatically.

Key Takeaways

  • The percent change formula is (New Value – Old Value) / Old Value, multiplied by 100 for a whole number or formatted as percentage for a decimal.
  • Negative results mean the value decreased; positive results mean it increased.
  • You can copy the formula down a column to calculate percent change for many rows at once using relative cell references.
  • Dividing by zero (when the old value is zero) causes an error; use an IF statement to handle this case.
  • Formatting cells as percentage automatically converts decimals to percentages, so you do not have to multiply by 100 in the formula.

Setting Up Your Data for Percent Change Calculations

Arrange your data so the old or starting value is in one column and the new or ending value is 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 rows.

Label your columns clearly: "Starting Value," "Ending Value," and "Percent Change" help anyone reading the spreadsheet understand what the numbers mean. Leave the first row for headers so your formulas start in row 2.

If your data spans many rows, this setup saves time because you write the formula once and then copy it down instead of typing it for each row.

Copying the Formula Down Multiple Rows

Once you enter the formula in the first data row, you can copy it down to all other rows. Click the cell with your formula, then drag the small square at the bottom-right corner of the cell down to the last row with data. Excel automatically adjusts the cell references for each row.

If you have 100 rows of data, this method is much faster than typing the formula 100 times. Excel keeps the structure of the formula the same but changes A1 to A2, A3, A4, and so on as you drag down.

You can also select the cell with the formula, copy it (Ctrl+C or Cmd+C), then select the range where you want to paste and paste (Ctrl+V or Cmd+V). Both methods work the same way.

Handling Zero Values and Division Errors

If your old value is zero, the formula will show a #DIV/0! error because you cannot divide by zero. To prevent this, wrap your formula in an IF statement that checks whether the old value is zero first.

Use this formula instead: =IF(A1=0,"N/A",(B1-A1)/A1*100). This tells Excel: if the old value in A1 is zero, display "N/A" instead of trying to divide. If the old value is not zero, calculate the percent change normally.

You can replace "N/A" with any text or number you prefer, such as 0 or "undefined." The point is to avoid the error and make your spreadsheet readable.

Formatting Cells as Percentage

After you calculate percent change, you can format the cells to display as percentages for easier reading. Select the cells with your results, right-click, and choose "Format Cells." Select "Percentage" from the category list and click OK.

When you format as percentage, Excel multiplies the decimal by 100 and adds the % symbol automatically. So if your formula result is 0.25, it displays as 25%. This means you should use the formula =(B1-A1)/A1 without multiplying by 100 yourself — let the formatting do that work.

You can also set how many decimal places appear. For most business purposes, one or two decimal places is enough. Right-click, choose "Format Cells," and adjust the decimal places in the percentage category.

Common Mistakes and How to Avoid Them

The most common mistake is reversing the order: using (Old Value – New Value) instead of (New Value – Old Value). This flips the sign of your result, so increases look like decreases and vice versa. Double-check that you subtract the old value from the new value, not the other way around.

Another mistake is forgetting to divide by the old value. The formula must divide the difference by the starting point, not by the ending point or by some other number. The old value is your baseline — it is what you measure change against.

A third mistake is mixing up which column holds which value. If you put the new value in column A and the old value in column B, your formula will be backwards. Take a moment to label your columns clearly before you start.

Percent Change vs. Percentage Point Change

Percent change and percentage point change are different. If a value goes from 20% to 25%, the percentage point change is 5 (you subtract 20 from 25). The percent change is 25% (you calculate (25-20)/20*100).

For most business and financial work, percent change is what you want. It shows how much the value grew or shrank relative to where it started. Percentage point change is useful only when you are already working with percentages and want to show the raw difference.

Make sure you understand which one your audience expects before you present your numbers.

Frequently Asked Questions

What if the percent change is negative?

A negative result means the value decreased. For example, if sales went from 100 to 80, the percent change is -20%, meaning a 20% drop. The formula works the same way; the negative sign appears automatically when the new value is smaller than the old value.

Can I calculate percent change if I only have the final value and the percent change?

Yes, but you need to rearrange the formula. If you know the final value and the percent change, the original value is: Final Value / (1 + Percent Change as a decimal). For example, if the final value is 125 and the percent change is 25%, the original was 125 / 1.25 = 100.

How do I show percent change as a whole number instead of a decimal?

Multiply by 100 in your formula: =(B1-A1)/A1*100. This gives you 25 instead of 0.25. Alternatively, use the decimal formula and format the cells as percentage, which Excel handles automatically.

What if I have negative starting values?

The formula still works, but the result can be confusing. If you go from -10 to 10, the percent change is (-10-(-10))/(-10)*100 = 20/(-10)*100 = -200%. Negative starting values often make percent change less meaningful, so consider whether a different metric would be clearer.

Can I use this formula for things other than sales or money?

Yes. The percent change formula works for any two numbers: website traffic, temperature, population, test scores, or inventory levels. As long as you have a starting value and an ending value, you can measure the percent change between them.