The Basic Percent Change Formula in Excel

To calculate percent change in Excel, use this formula: =(New Value – Old Value) / Old Value. Then format the result as a percentage. If your old value is in cell A1 and your new value is in cell B1, type =((B1-A1)/A1)*100 into an empty cell and press Enter. Excel will show you the percent change as a number; if you want it displayed with a % symbol, select the cell and click the percentage button in the toolbar, or right-click and choose Format Cells.

The formula works because percent change measures how much something grew or shrank relative to where it started. Dividing the difference by the starting value gives you the rate of change. Multiplying by 100 converts that decimal into a percentage point.

If your data spans multiple rows, you do not need to type the formula over and over. Enter it once, then copy the cell and paste it down the column. Excel automatically adjusts the cell references for each row.

Key Takeaways

  • The percent change formula is (New Value – Old Value) / Old Value, multiplied by 100 if you want the result as a whole number rather than a decimal.
  • Negative results mean the value decreased; positive results mean it increased.
  • You can copy a formula down a column to calculate percent change for multiple rows at once.
  • Format cells as percentage to display results with a % symbol automatically.
  • Avoid dividing by zero by using an IF statement to check whether the old value is empty or zero before calculating.

Setting Up Your Data in Excel

Arrange your data so the starting value (old value) is in one column and the ending value (new value) is in another. For example, put January sales in column A and February sales in column B. Each row should represent one item or time period you want to measure.

Label your columns clearly so you know which is which. A third column for the percent change result keeps your spreadsheet readable. If you have headers in row 1, start your formula in row 2.

Make sure both values are actual numbers, not text. If a cell looks like a number but Excel treats it as text, the formula will not work. You can check by clicking the cell; if it is text, it usually appears left-aligned instead of right-aligned.

Entering the Formula Step by Step

Click the cell where you want the result to appear. Type an equals sign to start the formula. Then type the opening parenthesis, the cell reference for your new value, a minus sign, the cell reference for your old value, and a closing parenthesis. Next, type a forward slash and the cell reference for your old value again. If you want the result as a percentage number rather than a decimal, add *100 at the end.

For example, if old value is A2 and new value is B2, type: =((B2-A2)/A2)*100

Press Enter. The result appears in the cell. If you see #DIV/0!, it means the old value is zero, which makes division impossible. If you see a very small decimal like 0.15, you forgot the *100 or did not format as percentage.

Copying the Formula to Multiple Rows

After you enter the formula in one cell, you can explore it to all rows at once instead of retyping it. Click the cell containing your formula. Copy it using Ctrl+C (or Cmd+C on Mac). Then select the range of cells below where you want the formula to go. Paste using Ctrl+V (or Cmd+V on Mac).

Excel automatically adjusts the cell references for each row. If your formula was =((B2-A2)/A2)*100 in row 2, it becomes =((B3-A3)/A3)*100 in row 3, and so on. This saves time and reduces errors when you have dozens or hundreds of rows.

You can also click the cell with the formula, then drag the small square at the bottom-right corner of the cell down to the last row you need. This is called the fill handle, and it copies the formula as you drag.

Formatting Results as Percentages

If you used the *100 in your formula, your result is already a number like 15 or -8. You can leave it as is, or add the % symbol by formatting. Select the cells with your results. Click the percentage button (%) in the toolbar, or right-click and choose Format Cells.

If you did not multiply by 100 in your formula, your result is a decimal like 0.15. Format these cells as percentage, and Excel automatically multiplies by 100 and adds the % symbol, showing 15% instead.

You can also control decimal places. Right-click the formatted cells, choose Format Cells, and set the number of decimal places you want. For sales data, one decimal place is usually enough. For scientific or financial work, you might want two or three.

Handling Negative Values and Zero

A negative percent change means the value decreased. If January sales were 100 and February sales were 80, the percent change is -20%. The formula handles this automatically; you do not need to do anything special.

A zero in the old value column breaks the formula because you cannot divide by zero. If this happens, use an IF statement to check for zero first: =IF(A2=0,"N/A",((B2-A2)/A2)*100). This tells Excel to display "N/A" if the old value is zero, and calculate normally otherwise.

If both the old and new values are zero, the percent change is technically undefined. The IF statement above treats this as "N/A" as well, which is the clearest way to handle it in a spreadsheet.

Common Mistakes to Avoid

The most common error is reversing the order: using (Old – New) instead of (New – Old). This flips the sign of your result, making increases look like decreases. Double-check your formula before copying it down.

Another mistake is forgetting to divide by the old value. If you calculate (New – Old) without dividing, you get the absolute change, not the percent change. These are different things: a $10 increase on a $100 base is 10%, but on a $1,000 base it is 1%.

Mixing up which column is which also happens easily. Label your columns clearly and check the first result by hand before copying the formula down. If you expect a 20% increase and see -20%, you likely swapped the columns.

Frequently Asked Questions

What is the difference between percent change and absolute change?

Absolute change is the straightforward difference: New minus Old. Percent change expresses that difference as a rate relative to the starting value. A $10 increase is the same absolute change whether you started at $100 or $1,000, but the percent change is 10% in the first case and 1% in the second.

Can I calculate percent change if the old value is negative?

Yes, the formula works the same way. If the old value is -50 and the new value is -30, the percent change is (-30 - (-50)) / (-50) = 20 / (-50) = -0.4 or -40%. The result tells you the direction and magnitude of change, though interpreting negative-to-negative changes can be confusing in real-world terms.

How do I show percent change with a plus sign for increases?

Use a custom number format. Right-click the cells, choose Format Cells, go to the Number tab, and select Custom. Enter the format code +0.0%;-0.0%. This displays positive results with a plus sign and negative results with a minus sign.

What if I want to calculate the average percent change across multiple items?

Calculate the percent change for each item first, then use the AVERAGE function on those results. Type =AVERAGE(C2:C10) if your percent change values are in cells C2 through C10. This gives you the mean percent change across all items.

Can I use this formula to calculate percent change over multiple years?

Yes. Put the value from the first year in one column and the value from the final year in another, then use the same formula. The result shows the total percent change across all years combined. If you want the average annual change, you need a different formula involving compound growth rates, which is more complex.