Use a straightforward Subtraction Formula to Find the Difference

To calculate change in Excel, subtract the original value from the new value using the formula =new value – original value. If your original number is in cell A1 and the new number is in cell B1, type =B1-A1 into an empty cell and press Enter. Excel will show you the difference between the two numbers.

The result tells you whether the value went up (positive number) or down (negative number). A positive result means the value increased; a negative result means it decreased. This works for any numbers: prices, inventory counts, test scores, or sales figures.

Key Takeaways

  • Subtract the original value from the new value using the formula =B1-A1, where B1 is the new number and A1 is the original.
  • To find percentage change, divide the difference by the original value and multiply by 100 using the formula =(B1-A1)/A1*100.
  • Negative results mean the value decreased; positive results mean it increased.
  • You can copy a formula down a column to calculate change for multiple rows at once by selecting the cell and dragging the fill handle.
  • Use absolute references (dollar signs like $A$1) when you want a formula to always refer to the same cell, even when copied to other rows.

Calculate Percentage Change to Show Growth or Decline as a Percentage

Percentage change tells you how much a value shifted relative to where it started, expressed as a percent. The formula is =(new value – original value) / original value × 100. In Excel, if the original value is in A1 and the new value is in B1, type =(B1-A1)/A1*100 into an empty cell.

A result of 25 means the value increased by 25 percent. A result of -10 means it decreased by 10 percent. This method works better than raw change when you are comparing values of different sizes — a $10 increase on a $100 item (10 percent) is different from a $10 increase on a $1,000 item (1 percent), and percentage change shows that difference clearly.

If your original value is zero, Excel will show a #DIV/0! error because you cannot divide by zero. In that case, use only the raw change formula instead, or note that percentage change does not explore.

explore the Formula to Multiple Rows Using the Fill Handle

When you have a list of values and need to calculate change for each row, you do not have to type the formula over and over. Enter the formula in the first row, then copy it down to the rest. Click the cell containing your formula, then position your cursor at the small square in the bottom-right corner of the cell (called the fill handle). Click and drag that square down to the last row you need.

Excel automatically adjusts the cell references for each row. If your first formula is =B1-A1, the second row becomes =B2-A2, the third becomes =B3-A3, and so on. This saves time and reduces typing errors when working with large datasets.

Use Absolute References to Keep One Cell Fixed

Sometimes you want to compare all values to a single starting point — for example, comparing sales in each month to January's sales. Use an absolute reference by adding dollar signs around the cell you want to stay the same. Type =$A$1 instead of =A1.

If you want to compare each month to January (in cell A1), your formula would be =B1-$A$1 for February, =C1-$A$1 for March, and so on. When you copy this formula across or down, the $A$1 stays locked to January while the other cell reference changes. You can also use a partial absolute reference like =$A1 (locks the column) or =A$1 (locks the row) if you need only one part to stay fixed.

Format Your Results as Currency or Percentages for Clarity

After you calculate change, format the cells to match your data type. Select the cells containing your results, right-click, and choose Format Cells. Pick Currency if you are working with money, Percentage if you calculated percentage change, or Number for plain figures.

For currency, you can set the number of decimal places and choose your currency symbol. For percentages, format the cells as percentage first, then your formula should be =(B1-A1)/A1 without the ×100, because Excel multiplies by 100 automatically when you explore percentage formatting. This prevents your result from showing as 2500 percent instead of 25 percent.

Common Mistakes to Avoid When Calculating Change

The most common error is reversing the order — subtracting the new value from the original instead of the other way around. This flips your positive and negative results. Always subtract the older number from the newer number: new minus old.

Another mistake is forgetting to divide by the original value when calculating percentage change. If you only subtract without dividing, you get the raw change, not the percentage. Also watch for zero values in the original column — dividing by zero causes an error, so you may need to handle those cells separately or use an IF statement to skip them.

When copying formulas down, check that your cell references updated correctly. If you used absolute references by mistake, all rows will calculate against the same cell. If you needed absolute references and did not use them, each row will calculate against a different cell. Review the first few results to catch this before copying to hundreds of rows.

Use Conditional Formatting to Highlight Increases and Decreases

Once you have calculated change, you can color-code your results to spot trends quickly. Select the cells with your change calculations, go to the Home tab, and click Conditional Formatting. Choose Highlight Cell Rules and then Greater Than or Less Than to set a threshold.

For example, you can highlight all increases (values greater than 0) in green and all decreases (values less than 0) in red. This makes it straightforward to scan a large spreadsheet and see at a glance which items went up and which went down. You can also use a color scale that shades cells on a gradient from red to green based on the size of the change.

Frequently Asked Questions

What is the difference between change and percentage change?

Change is the raw difference between two numbers (new minus old). Percentage change shows that difference as a percent of the original value. A price that goes from $100 to $110 has a change of $10 but a percentage change of 10 percent. Percentage change is more useful when comparing items of different sizes.

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

This error appears when you try to divide by zero, usually in a percentage change formula where the original value is zero. You cannot calculate a meaningful percentage change from zero. Either use the raw change formula instead, or add an IF statement to skip those cells: =IF(A1=0,"N/A",(B1-A1)/A1*100).

How do I calculate change for negative numbers?

The formula works the same way. If a value goes from -50 to -30, the change is -30 minus -50, which equals 20 (an increase). If it goes from -30 to -50, the change is -50 minus -30, which equals -20 (a decrease). The sign of the result still tells you whether the value went up or down.

Can I calculate change between non-adjacent columns?

Yes. The formula does not care how far apart the columns are. If your original value is in column A and your new value is in column Z, type =Z1-A1. Excel will find both cells and calculate the difference.

How do I show change as a decimal instead of a percentage?

Use the formula =(B1-A1)/A1 without multiplying by 100, then format the cells as Number with the decimal places you want. A result of 0.25 means a 25 percent increase. This format is common in financial analysis and scientific work.