The Basic Formula for Percentage Change

Percentage change in Excel uses a simple formula: (New Value – Old Value) / Old Value, then multiply by 100 to get a percentage. If you're comparing sales from one month to the next, or tracking how a price has shifted, this formula tells you the rate of change as a percentage rather than just a raw number.

In Excel, you write this as =(B2-A2)/A2*100, where A2 is your starting value and B2 is your ending value. The result shows you whether something went up or down, and by how much. A positive number means growth; a negative number means decline.

For example, if a product cost $50 last month (A2) and costs $60 this month (B2), the formula =(B2-A2)/A2*100 gives you 20, meaning a 20% increase. If the price dropped to $40, the same formula gives you -20, a 20% decrease.

Key Takeaways

  • The percentage change formula is (New Value – Old Value) / Old Value, multiplied by 100 to convert to a percentage.
  • In Excel, enter the formula as =(B2-A2)/A2*100, replacing A2 and B2 with the cell references that hold your old and new values.
  • You can format the result as a percentage by selecting the cell, right-clicking, choosing Format Cells, and selecting Percentage from the Category list.
  • To apply the same formula to multiple rows at once, enter it in one cell, then drag the fill handle (small square at the bottom-right corner) down to copy it to other cells.

Setting Up Your Data in Columns

Before you write any formula, arrange your data so the old value is in one column and the new value is in another. This makes the formula straightforward and easy to copy down if you have many rows to calculate.

Put your starting values in column A and your ending values in column B. Label the columns clearly — for example, "January Sales" in A1 and "February Sales" in B1. Then put your first data point in A2 and B2. This layout lets you write the formula once and reuse it for every row below.

If your data is already spread across your spreadsheet in a different arrangement, you can still use the formula — just adjust the cell references to point to wherever your numbers actually are.

Writing and Copying the Formula Down

Click on cell C2 (or whichever column you want the result in) and type =((B2-A2)/A2)*100. The parentheses make sure Excel does the subtraction first, then the division, then the multiplication — the order matters for getting the right answer.

Press Enter. Excel calculates the percentage change for that row and shows the result. Now you have the formula in C2, and you need to copy it down to every other row with data.

Click on C2 again to select it. Look at the bottom-right corner of the cell — you'll see a small square (the fill handle). Click and drag that square down to the last row of your data. Excel copies the formula to every cell you drag over, and it automatically adjusts the row numbers. So C3 becomes =((B3-A3)/A3)*100, C4 becomes =((B4-A4)/A4)*100, and so on.

Formatting the Result as a Percentage

After you calculate the percentage change, the number might show as 20 instead of 20%, or it might show as 0.2 depending on how Excel interprets your formula. You can format it to look like a proper percentage.

Select the cells that hold your results (C2 through C10, for example). Right-click and choose Format Cells. In the window that opens, click the Number tab, then select Percentage from the Category list on the left. Set the decimal places to 1 or 2 if you want (2 decimal places shows 20.00%, 0 shows 20%). Click OK.

Now your results display as percentages with the % symbol. If you used the formula with *100 at the end, you may see numbers like 2000% instead of 20% — in that case, remove the *100 from your formula and just use =((B2-A2)/A2), then format as percentage.

Handling Zero or Negative Starting Values

If your old value (the denominator in the formula) is zero, Excel shows a #DIV/0! error because you cannot divide by zero. This is mathematically correct — you cannot calculate a meaningful percentage change from zero.

If you have cells with zero values, you have two choices. You can leave the error as-is if it makes sense in your context (it signals that the calculation is impossible). Or you can use an IF statement to handle it: =IF(A2=0,"N/A",((B2-A2)/A2)*100). This formula checks whether A2 is zero; if it is, it displays "N/A" instead of an error; if it isn't, it calculates normally.

Negative starting values work fine mathematically. If you started at -$10 and ended at $10, the formula still works and shows a 200% increase. Just make sure the context makes sense — percentage change from a negative number can be confusing to interpret, so consider whether you need to explain it to your reader.

Common Mistakes and How to Fix Them

The most common mistake is reversing the order — using (Old Value – New Value) instead of (New Value – Old Value). This flips the sign, so increases look like decreases and vice versa. Double-check that you subtract the old from the new, not the other way around.

Another mistake is forgetting the parentheses. If you type =B2-A2/A2*100, Excel follows the order of operations and divides A2 by A2 first (which always equals 1), then multiplies by 100, giving you a wrong answer. The parentheses force Excel to do the subtraction and division in the right order: =((B2-A2)/A2)*100.

If your result looks way too large or too small, check whether you've already formatted the cells as percentage. If you use *100 in the formula and then format as percentage, Excel multiplies by 100 again, giving you 2000% instead of 20%. Either remove the *100 from the formula or don't format as percentage — use one or the other, not both.

Percentage Change for Multiple Comparisons

If you need to compare more than two time periods — say, January to February, February to March, and March to April — you can set up multiple columns. Put January in A, February in B, March in C, and April in D. Then create a column for "Jan to Feb" change, another for "Feb to Mar" change, and so on.

In the "Jan to Feb" column, use =((B2-A2)/A2)*100. In the "Feb to Mar" column, use =((C2-B2)/B2)*100. Each formula compares the two adjacent columns, so you can see the percentage change for each step. This approach works well when you're tracking trends over time and want to see whether the rate of change is speeding up or slowing down.

Frequently Asked Questions

What if I want to show the result as a decimal instead of a percentage?

Remove the *100 from the formula and use =((B2-A2)/A2) instead. The result will show as 0.2 for a 20% increase. You can then format it as a decimal with however many places you want by right-clicking, choosing Format Cells, selecting Number, and setting decimal places.

Can I calculate percentage change if my values are in rows instead of columns?

Yes. If your old value is in A2 and your new value is in B2 (side by side), the formula stays the same: =((B2-A2)/A2)*100. If they're in different rows — say A2 and A3 — use =((A3-A2)/A2)*100. The formula adapts to wherever your numbers are; just make sure you're pointing to the right cells.

How do I calculate the average percentage change across multiple rows?

Calculate the percentage change for each row separately using the formula above, then use =AVERAGE(C2:C10) to find the average of all those percentages. This tells you the typical rate of change across your dataset, though be aware that averaging percentages can be misleading if your starting values are very different from row to row.

What does a negative percentage change mean?

A negative percentage means the value went down. If you see -15%, that means the new value is 15% lower than the old value. The formula automatically produces a negative number when the new value is smaller than the old value, so you don't need to do anything special to calculate decreases.