The Basic Formula for Percent Change

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 calculate the result when ready. A positive number means the value increased; a negative number means it decreased.

For example, if a product cost $50 last month (A1) and costs $65 this month (B1), the formula shows a 30% increase. If it dropped to $40, the formula shows a -20% decrease. The parentheses matter — they tell Excel to do the subtraction and division before multiplying by 100.

Key Takeaways

  • The percent change formula is =(New Value - Old Value) / Old Value * 100, and it works the same way whether values increased or decreased.
  • You can explore this formula to a whole column at once by typing it once and dragging the fill handle down, which saves time when you have dozens of rows.
  • Formatting the result as a percentage (right-click, Format Cells, Percentage) removes the need to multiply by 100 in your formula.
  • Percent change is different from percentage of total — use this formula only when comparing an old number to a new number, not when breaking down parts of a whole.

Setting Up Your Data in Columns

The easiest way to calculate percent change for multiple rows is to organize your data into three columns: one for the item name or date, one for the old value, and one for the new value. Put your percent change formula in a fourth column.

For instance, if you have monthly sales figures, put the month in column A, last year's sales in column B, and this year's sales in column C. Then in column D, type the formula =((C2-B2)/B2)*100 in the first data row (row 2, since row 1 holds your headers). Excel will calculate the percent change for that row.

Once you have the formula in D2, click on that cell and look for the small square in the bottom right corner (the fill handle). Click and drag it down to copy the formula to every row with data. Excel automatically adjusts the cell references — D3 will use B3 and C3, D4 will use B4 and C4, and so on. This is much faster than typing the formula for each row by hand.

Using Percentage Format Instead of Multiplying by 100

If you format your result as a percentage, you do not need to multiply by 100 in the formula. Instead, use =((B1-A1)/A1) and then format the cell as a percentage. The result will display the same way.

To format a cell as a percentage, right-click on it and choose Format Cells. In the window that opens, click the Number tab, select Percentage from the list on the left, and click OK. Excel will multiply your decimal result by 100 and add the % symbol automatically.

This approach is cleaner if you plan to use the numbers in other calculations later, because Excel stores the actual decimal value (0.30 for 30%) rather than the multiplied number (30). It also makes your formula shorter and easier to read.

Handling Zero and Negative Starting Values

The percent change formula breaks down when your old value is zero, because you cannot divide by zero. Excel will show #DIV/0! as an error. If you have rows where the old value is zero, you have a few options.

One option is to use an IF statement to check for zero before calculating. The formula =IF(A1=0,"N/A",((B1-A1)/A1)*100) will display "N/A" instead of an error when the old value is zero. This keeps your spreadsheet clean and tells anyone reading it that the percent change cannot be calculated for that row.

When the old value is negative, the formula still works mathematically, but the result can be confusing. For example, if a value went from -$10 to +$10, the percent change is -200%, which is technically correct but counterintuitive. In these cases, consider adding a note or using a different metric to explain what happened.

Common Mistakes to Avoid

The most common mistake is reversing the order — subtracting the new value from the old value instead of the other way around. This gives you the opposite sign (negative instead of positive). Always subtract the old from the new: (New - Old), not (Old - New).

Another mistake is forgetting the parentheses. If you type =B1-A1/A1*100 without parentheses, Excel follows the order of operations and divides A1 by A1 first, giving you the wrong answer. The parentheses force Excel to do the subtraction and division in the right order.

A third mistake is mixing up percent change with percentage of total. Percent change compares two different time periods or versions of the same thing. Percentage of total breaks down the parts of a single whole. These use different formulas and answer different questions.

explore Percent Change to Real Scenarios

Percent change is useful for tracking how things grow or shrink over time. If you manage a budget, you can compare this year's spending to last year's and see which categories grew the most. If you track website traffic, you can see which months had the biggest jumps or drops compared to the previous month.

You can also use it to compare performance across different items. If one product's sales grew 15% and another grew 8%, the percent change formula lets you see that comparison at a glance. This is more meaningful than just looking at the raw numbers, especially when the items started at different sizes.

In spreadsheets with many rows, conditional formatting can highlight the results. You can color cells green if the percent change is positive and red if it is negative, making trends visible when ready. This is helpful when you are presenting data to others or scanning a large table for patterns.

Frequently Asked Questions

What is the difference between percent change and percentage point change?

Percent change is what the formula calculates — the relative shift from one value to another, expressed as a percentage of the starting value. Percentage point change is the straightforward difference between two percentages. If unemployment was 5% last year and 7% this year, the percentage point change is 2 points, but the percent change is 40% (because 7 is 40% higher than 5).

Can I calculate percent change if my values are in different units?

No. Percent change only makes sense when comparing the same thing measured the same way. You cannot calculate percent change between dollars and euros, or between pounds and kilograms. Convert to the same unit first, then use the formula.

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

Use the formula =((B1-A1)/A1) without multiplying by 100, and format the cell as a number instead of a percentage. A result of 0.30 means 30%. This format is useful if you need to use the result in further calculations.

What if I want to calculate percent change for the entire year, not month by month?

Use the oldest value as your starting point and the most recent value as your ending point. If you have January in column B and December in column C, the formula =((C2-B2)/B2)*100 gives you the percent change for the whole year. You do not need to add up the monthly changes.

Can Excel calculate percent change automatically without a formula?

No. You must enter the formula yourself. Excel does not have a built-in percent change function, but once you type the formula once, you can copy it to other cells or rows, which is nearly as fast.