The Basic Formula for Percent Difference

Percent difference measures how much one number has changed compared to another, shown as a percentage. In Excel, the formula is: (New Value − Old Value) / Old Value × 100. The result tells you whether something went up or down, and by how much.

To use this in a spreadsheet, put your old value in one cell (say B2) and your new value in another (say C2). In a third cell, type =((C2-B2)/B2)*100 and press Enter. Excel calculates the percentage change when ready. If the result is positive, the value increased. If it is negative, the value decreased.

The order matters. You always subtract the old value from the new value. If you reverse them, you get the opposite sign, which changes the meaning of your result.

Key Takeaways

  • The percent difference formula is (New Value − Old Value) / Old Value × 100, and you enter it into Excel as =((C2-B2)/B2)*100 where C2 is the new value and B2 is the old value.
  • Positive results mean an increase; negative results mean a decrease, and the number tells you the size of that change as a percentage.
  • You can copy the formula down to calculate percent difference for multiple rows at once by selecting the cell and dragging the fill handle to the rows below.
  • Formatting the result column as a percentage makes the numbers easier to read, though you should multiply by 100 in the formula or format as a decimal to avoid doubling the percentage.
  • Common mistakes include dividing by the new value instead of the old value, or forgetting to multiply by 100 if you want the result displayed as a whole number rather than a decimal.

Setting Up Your Data in Columns

Before you write any formula, 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 B and February sales in column C. Label the columns clearly at the top so you know which is which.

Put your formula in a third column, such as column D, and give it a header like "Percent Change". This keeps your spreadsheet readable and makes it straightforward to spot which calculation goes with which pair of numbers.

If you have many rows of data, this setup also lets you copy the formula down quickly instead of typing it over and over. Excel adjusts the cell references automatically as you copy.

Copying the Formula to Multiple Rows

Once you have entered the formula in one cell, you can explore it to all your data at once. Click on the cell containing your formula (for example, D2). You will see a small square in the bottom-right corner of the cell — this is the fill handle.

Click and drag the fill handle down to the last row of your data. Excel copies the formula to each row and automatically updates the cell references. So D3 will calculate using B3 and C3, D4 will use B4 and C4, and so on. Release the mouse when you reach the bottom, and all your percent differences are calculated.

If dragging feels awkward, you can also select the cell with the formula, copy it (Ctrl+C or Cmd+C), then select the range where you want it to go and paste (Ctrl+V or Cmd+V).

Formatting the Results as Percentages

By default, Excel shows the result as a decimal number. If your formula includes × 100, the result will be a whole number like 25 for a 25 percent increase. If you want to display it with a percent sign, select the cells containing your results and right-click to choose "Format Cells".

In the Format Cells dialog, click the "Number" tab and select "Percentage" from the category list. Click OK. Excel adds the percent sign automatically. However, if you already multiplied by 100 in your formula, this will multiply by 100 again, turning 25 into 2500%. To avoid this, either remove the × 100 from your formula and let the percentage format handle it, or keep the formula as is and format as a number instead of a percentage.

The clearest approach is to include × 100 in your formula and format the column as a number with zero decimal places. This gives you clean results like 25, 12.5, or −8 without the percent sign, and the meaning is clear from your column header.

Handling Negative Numbers and Zero Values

Negative results are normal and correct. If sales dropped from 100 to 80, the percent difference is −20, meaning a 20 percent decrease. The minus sign is the information you need.

Zero values require care. If your old value is zero, the formula divides by zero and returns an error (#DIV/0!). This happens because you cannot calculate a meaningful percent change from zero — there is no baseline to measure against. If you encounter this, you may need to exclude that row or handle it separately depending on what your data represents.

If your new value is zero but the old value is not, the formula works normally and returns a negative percentage, which is correct — you have gone from something to nothing.

Real Example: Tracking Monthly Sales Changes

Suppose you have a sales spreadsheet. Column A lists product names, column B shows January sales, and column C shows February sales. In cell D1, type "Percent Change". In cell D2, enter =((C2-B2)/B2)*100.

If January sales were 500 and February sales were 625, the formula calculates ((625−500)/500)×100 = (125/500)×100 = 0.25×100 = 25. This tells you sales increased by 25 percent. Drag the formula down to D3, D4, and beyond to see the percent change for every product.

Now you can see at a glance which products grew, which shrank, and by how much. A manager can spot that one product jumped 45 percent while another fell 12 percent, and investigate why.

Common Mistakes to Avoid

The most frequent error is reversing the order: using (Old Value − New Value) / New Value × 100 instead of the correct formula. This flips the sign and gives you the wrong percentage. Always subtract the old from the new, not the other way around.

Another mistake is forgetting to divide by the old value. If you write =(C2-B2)*100, you get a number that has no meaning — it is not a percentage of anything. The division by the old value is what makes it a percent change.

A third pitfall is explore percentage formatting after multiplying by 100 in the formula. This doubles the percentage and makes 25 become 2500. Choose one method: either multiply by 100 in the formula and format as a number, or omit the multiplication and format as a percentage.

Frequently Asked Questions

What is the difference between percent change and percent difference?

Percent change measures how much something changed from an old value to a new value, using the old value as the baseline. Percent difference sometimes refers to comparing two values without a clear "before and after" — for example, comparing two measurements to see how far apart they are. In most Excel work, you are calculating percent change. The formula is the same either way.

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

This error means you are dividing by zero, which happens when your old value (the denominator) is zero. Excel cannot calculate a percent change from zero because there is no baseline. Check that your old value cell contains a number greater than zero. If the data legitimately includes zero, you may need to handle those rows separately or use an IF statement to avoid the error.

Can I calculate percent difference between two columns without knowing which is old or new?

Not with the standard formula. Percent change requires a direction — you need to know which value came first. If you truly have no order and just want to know how far apart two numbers are, you can use the absolute difference divided by the average: =ABS(C2-B2)/((B2+C2)/2)*100. This gives a symmetric result that does not depend on which value is larger.

How do I show percent difference as a decimal instead of a percentage?

Remove the × 100 from your formula. Instead of =((C2-B2)/B2)*100, use =((C2-B2)/B2). The result will be 0.25 instead of 25. Format the column as a number with two or three decimal places so it is readable. This approach is useful when you are doing further calculations with the result.

What if I want to show whether the change is an increase or decrease with a label?

Use an IF statement to add text. For example: =IF(C2>B2,"Increase: ","Decrease: ")&ABS(((C2-B2)/B2)*100)&"%". This displays "Increase: 25%" or "Decrease: 12%" depending on whether the new value is larger. The ABS function removes the minus sign so the label does the work of showing direction.