The basic formula for percent difference

Percent difference measures how much a value has changed between two points, shown as a percentage. In Excel, the formula is (New Value - Old Value) / Old Value, then multiply by 100 to convert to a percentage. If your old value is in cell A1 and your new value is in cell B1, you would type =((B1-A1)/A1)*100 into a cell to get the result.

The formula works the same way whether your values went up or down. A positive result means the value increased; a negative result means it decreased. For example, if a price rose from $50 to $60, the percent difference is ((60-50)/50)*100, which equals 20 percent.

You can also skip the multiplication by 100 and format the cell as a percentage instead. Type =((B1-A1)/A1) and then right-click the cell, select Format Cells, and choose Percentage. Excel will automatically display the decimal as a percentage.

Key Takeaways

  • The percent difference formula in Excel is ((New Value - Old Value) / Old Value) * 100, entered as =((B1-A1)/A1)*100 if your values are in cells A1 and B1.
  • You can multiply by 100 in the formula or format the cell as a percentage after entering the formula without the multiplication.
  • Positive results show an increase and negative results show a decrease, making it easy to spot whether a value went up or down.
  • The formula works for any two values — prices, sales figures, population counts, or any other numbers you want to compare.
  • If your old value is zero, the formula will return a #DIV/0! error because you cannot divide by zero.

Setting up your data in columns

Before you write the formula, organize your data so the old value and new value are in separate columns. Put your old values in one column (for example, column A) and your new values in the next column (column B). Label the columns at the top so you remember which is which — something like "Previous Month" and "Current Month" or "2023 Sales" and "2024 Sales."

If you have multiple rows of data, you can enter the formula once in the first data row and then copy it down to all the other rows. Click the cell with your formula, then drag the small square in the bottom-right corner of the cell down to the last row with data. Excel will automatically adjust the cell references for each row.

Handling negative numbers and zero values

When your old value is negative, the formula still works, but the result can be confusing. For example, if a value changes from -10 to 10, the percent difference is ((10-(-10))/(-10))*100, which equals -200 percent. This is mathematically correct but can be hard to interpret in real situations.

If your old value is zero, Excel will show a #DIV/0! error because the formula tries to divide by zero, which is impossible. You can prevent this error by using an IF statement: =IF(A1=0,"N/A",((B1-A1)/A1)*100). This tells Excel to display "N/A" instead of an error when the old value is zero.

Negative new values work fine in the formula without any special handling. A change from 50 to -50 gives you ((−50−50)/50)*100, which equals -200 percent, showing a large decrease.

Percent difference vs. percent change

Percent difference and percent change are the same thing — both use the formula ((New Value - Old Value) / Old Value) * 100. The terms are interchangeable. Some people use "percent change" when talking about a single value over time, and "percent difference" when comparing two separate measurements, but Excel treats them identically.

Do not confuse percent difference with percentage point difference. If something goes from 20 percent to 25 percent, the percentage point difference is 5 points, but the percent difference is ((25-20)/20)*100, which equals 25 percent. Percentage points measure the absolute gap; percent difference measures the relative change.

Using absolute references to lock cells

If you want to compare multiple new values against a single old value, use an absolute reference to lock that cell. Instead of =((B1-A1)/A1)*100, type =((B1-$A$1)/$A$1)*100. The dollar signs tell Excel to keep A1 fixed when you copy the formula down.

This is useful when you have a baseline value you want to measure everything against. For example, if A1 contains a company's revenue in 2020 and you have revenue figures for 2021, 2022, and 2023 in cells B1, B2, and B3, you can use the absolute reference formula in B1 and copy it down. Each row will compare its new value to the 2020 baseline.

Rounding your results

Percent difference calculations often produce long decimals. To round your result to a specific number of decimal places, wrap your formula in the ROUND function. Type =ROUND(((B1-A1)/A1)*100,2) to round to two decimal places. Change the 2 to any number of decimal places you want.

If you have already entered the formula without rounding, you can also format the cell to show fewer decimal places. Right-click the cell, select Format Cells, choose Number, and set the decimal places. This only changes how the number displays, not the actual value stored in the cell, so calculations based on that cell will still use the full precision.

Real-world example with sales data

Suppose you have monthly sales figures and want to see how much each month changed from the previous month. In column A, you have January through June sales: 5000, 5500, 5200, 6100, 5900, 6400. In column B, you want to calculate the percent change from each month to the next.

In cell B2, enter =((A2-A1)/A1)*100 to calculate the change from January to February. This gives you ((5500-5000)/5000)*100, which equals 10 percent. Copy this formula down to B3 through B6, and Excel will automatically adjust the references. You will see that February was up 10 percent, March was down about 5.5 percent, April was up about 19 percent, and so on.

If you want to see these as percentages instead of numbers with decimals, select cells B2 through B6, right-click, choose Format Cells, and select Percentage. Excel will display 10%, -5.45%, 19.23%, and so on, making the results easier to read at a glance.

Frequently Asked Questions

What is the difference between percent difference and percent change?

They are the same thing. Both use the formula ((New Value - Old Value) / Old Value) * 100. The terms are used interchangeably in Excel and in most contexts.

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

This error appears when your old value is zero, because the formula tries to divide by zero. Use =IF(A1=0,"N/A",((B1-A1)/A1)*100) instead to display "N/A" when the old value is zero.

Can I copy the formula to multiple rows at once?

Yes. Enter the formula in the first row, click the cell, then drag the small square in the bottom-right corner down to the last row you want to fill. Excel will adjust the cell references automatically for each row.

How do I show the result as a percentage instead of a decimal?

Remove the *100 from your formula so it reads =((B1-A1)/A1), then right-click the cell, select Format Cells, and choose Percentage. Excel will display the result as a percentage automatically.

What if I want to compare multiple values to one baseline value?

Use absolute references with dollar signs: =((B1-$A$1)/$A$1)*100. The dollar signs lock cell A1 so it stays the same when you copy the formula down to other rows.