The basic percent change formula in Excel

To calculate percent change in Excel, you subtract the old value from the new value, divide by the old value, and multiply by 100. The formula looks like this: =(New Value - Old Value) / Old Value * 100

If your old value is in cell A1 and your new value is in cell B1, you would type =((B1-A1)/A1)*100 into an empty cell. Excel will return the percent change as a number — positive if the value increased, negative if it decreased.

For example, if a product cost $50 last month (A1) and costs $60 this month (B1), the formula would show a 20% increase. If it dropped to $40, the formula would show a -20% decrease.

Key Takeaways

  • The percent change formula is (New Value - Old Value) / Old Value * 100, and you can type it directly into any Excel cell.
  • You can format the result as a percentage by right-clicking the cell, selecting Format Cells, and choosing Percentage from the Category list.
  • To apply the same formula to multiple rows at once, enter it in one cell and drag the fill handle down to copy it to the cells below.
  • If your old value is zero, Excel will show a #DIV/0! error because you cannot divide by zero — you may need to handle this case separately.

Formatting the result as a percentage

When you use the formula with *100 at the end, Excel returns a plain number like 20 instead of 20%. If you want it to display as a percentage, you have two options.

The first option is to remove the *100 from your formula and let Excel's percentage format do the work. Type =((B1-A1)/A1) instead, then right-click the cell and select Format Cells. In the dialog box, click the Number tab, select Percentage from the Category list on the left, and click OK. Excel will automatically multiply by 100 and add the % symbol.

The second option is to keep the *100 in your formula and manually add the % symbol by changing the cell format to Text. This is less common and usually not necessary — the first method is cleaner.

Copying the formula down to multiple rows

If you have a list of old values in column A and new values in column B, you do not need to type the formula separately for each row. Enter the formula once in cell C1, then copy it down to all the rows that have data.

Click on cell C1 (where your formula is). Look for the small square in the bottom-right corner of the cell — this is called the fill handle. Click and drag it down to the last row with data. Excel will automatically adjust the cell references for each row, so C2 will calculate the change for A2 and B2, C3 will calculate for A3 and B3, and so on.

If you have many rows, you can also double-click the fill handle instead of dragging. Excel will automatically fill down to the last row that has data in the adjacent columns.

Handling zero and negative starting values

The percent change formula works differently depending on what your old value is. If the old value is zero, Excel cannot perform the calculation and will display #DIV/0! in the cell. This is because dividing by zero is mathematically impossible.

If you expect zeros in your data, you can use an IF statement to handle them. Type =IF(A1=0,"N/A",((B1-A1)/A1)*100) to display "N/A" instead of an error when the old value is zero. You can replace "N/A" with any text or number you prefer.

Negative starting values work fine with the formula — if a company had a loss of -$100 last year and a loss of -$50 this year, the formula will correctly show that the loss decreased by 50%.

Showing percent change with one decimal place

By default, Excel may show many decimal places in your result, like 20.5555555. To make your numbers cleaner, you can round them to one or two decimal places.

Use the ROUND function by typing =ROUND(((B1-A1)/A1)*100, 1). The number at the end (1) tells Excel how many decimal places to keep. Change it to 2 for two decimal places, or 0 for no decimal places at all.

Alternatively, right-click the cell with your result, select Format Cells, go to the Number tab, and change the Decimal places field to the number you want. This changes only how the number displays, not the actual value Excel uses in other calculations.

Percent change vs. percentage point change

Percent change and percentage point change are different things, and it is easy to mix them up. If something goes from 50% to 60%, that is a 10 percentage point increase, but it is a 20% increase in the original value.

Use the percent change formula when you want to know how much something grew or shrank relative to where it started. Use simple subtraction (New Value - Old Value) when you are comparing percentages directly and want to know the difference in percentage points.

For example, if unemployment was 5% last year and is 6% this year, the percentage point change is 1 (6 - 5 = 1). The percent change is 20% because 6 is 20% higher than 5.

Frequently Asked Questions

What if my new value is smaller than my old value?

The formula still works and will return a negative number. A decrease from 100 to 80 gives -20%, which correctly shows a 20% drop. Negative results are normal and expected when values go down.

Can I calculate percent change for more than two values?

The basic formula only compares two values at a time. If you want to track change across multiple time periods, calculate the percent change between each pair of consecutive values, or compare each value to a single starting point by always using the same old value in your formula.

Why does my formula show #DIV/0! error?

This error means you are dividing by zero, which happens when your old value cell is empty or contains 0. Check that the cell reference in the denominator (the bottom part of the division) points to the correct old value, and that it is not blank.

How do I show the result as a whole number without decimals?

Right-click the cell, select Format Cells, choose Number from the Category list, and set Decimal places to 0. Or use the ROUND function with 0 as the second number: =ROUND(((B1-A1)/A1)*100, 0)

Can I use this formula for things other than money?

Yes. The percent change formula works for any numbers — sales figures, website traffic, temperature, population, test scores, or anything else that changes over time. The calculation is the same regardless of what you are measuring.