The Basic Percent Change Formula in Excel

To calculate percent change in Excel, use this formula: =((New Value – Old Value) / Old Value) * 100. This tells you how much something has increased or decreased as a percentage of where it started. If the result is positive, the value went up. If it's negative, the value went down.

The formula works the same way whether you're tracking sales figures, temperature shifts, inventory levels, or any other measurement that changes over time. Excel handles the math when ready once you enter the values in the right cells.

Key Takeaways

  • The percent change formula is =((New Value – Old Value) / Old Value) * 100, and you enter it into any empty cell in your spreadsheet.
  • You can reference cells directly (like =((B2-B1)/B1)*100) so the formula updates automatically if your numbers change.
  • A positive result means an increase; a negative result means a decrease.
  • Multiplying by 100 converts the decimal to a percentage — without it, Excel shows 0.25 instead of 25%.
  • Format the cell as a percentage after entering the formula to display the result cleanly without typing *100 each time.

Setting Up Your Data and Cells

Start by arranging your numbers in two columns: the old or starting value in one column, and the new or ending value in the next. For example, put January sales in column B and February sales in column C. Label your columns clearly so you don't mix them up — the order matters, because subtracting the new value from the old value gives you the opposite sign.

Leave an empty column or cell where you want the percent change result to appear. This is where you'll type the formula. Click on that cell to select it before you start typing.

Entering the Formula Step by Step

Click the cell where you want the result. Type the equals sign first — this tells Excel you're entering a formula, not text. Then type the formula exactly as shown: =((B2-B1)/B1)*100, but replace B2 and B1 with the actual cell references for your new and old values.

For example, if your old value is in cell A1 and your new value is in cell A2, type =((A2-A1)/A1)*100. Press Enter. Excel calculates the result and displays it in that cell. If you see an error like #DIV/0!, it usually means your old value is zero, which makes the formula impossible (you can't divide by zero).

To copy this formula down to other rows, click the cell with the formula, then drag the small square at the bottom-right corner of the cell down as far as you need. Excel automatically adjusts the cell references for each row, so you don't have to type the formula over and over.

Using Cell References Instead of Typing Numbers

Always reference cells in your formula rather than typing the actual numbers. This way, if your data changes, the percent change updates automatically without you having to re-enter the formula. For instance, if sales figures get corrected next week, the percent change recalculates when ready.

To reference a cell, just click on it while you're typing the formula. Excel inserts the cell address for you. This is faster and more accurate than typing A1 or B5 by hand, and it prevents mistakes.

Formatting the Result as a Percentage

After you enter the formula with *100 at the end, the result appears as a regular number — for example, 25 instead of 25%. If you want it to display with a percent sign, right-click the cell and select "Format Cells." Choose "Percentage" from the Category list and click OK.

Alternatively, you can skip the *100 in your formula and let Excel's percentage format handle the conversion. Use =((New Value – Old Value) / Old Value) without multiplying by 100, then format the cell as a percentage. Excel automatically multiplies by 100 and adds the % sign. Both methods give the same result — use whichever feels clearer to you.

Handling Negative Values and Zero

If your old value is zero, the formula returns a #DIV/0! error because division by zero is impossible. In real situations, this often means you're starting from nothing — like calculating growth when a product had zero sales last month. You may need to note this separately or exclude it from your analysis, because percent change doesn't have a meaningful answer when the starting point is zero.

Negative percent changes are normal and correct. If a value drops from 100 to 75, the percent change is –25%, which tells you it decreased by a quarter. If both your old and new values are negative, the formula still works — just follow the math carefully. A change from –100 to –50 is a 50% increase (the value got closer to zero).

Common Mistakes to Avoid

The most frequent error is reversing the order: subtracting the new value from the old value instead of the other way around. This flips the sign of your answer. Double-check that you're subtracting the starting number from the ending number, not the reverse.

Another common mistake is forgetting the *100 or forgetting to format as a percentage. If you see 0.25 when you expect 25%, you've left out one of these steps. Also, make sure you're dividing by the old value, not the new value — the denominator is the baseline you're measuring change against.

If you copy a formula down and the results look wrong, check that the cell references updated correctly. Sometimes an absolute reference (with a $ sign, like $B$1) is needed if you want one cell to stay the same while others change. For most percent change calculations, you don't need absolute references, but it's worth checking if results seem off.

Frequently Asked Questions

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

Use the formula without *100: =((New Value – Old Value) / Old Value). This gives you 0.25 instead of 25. You can then format the cell as a decimal with however many places you want, or leave it as is. Both formats show the same information — it's just a display choice.

Can I calculate percent change for multiple rows at once?

Yes. Enter the formula in the first row, then click the cell and drag the small square at the bottom-right corner down to the last row you need. Excel copies the formula and adjusts the cell references automatically for each row.

Why does my formula show an error?

The most common cause is a zero in the old value cell, which creates a #DIV/0! error. Check that your old value is not zero. Other errors like #NAME? usually mean you typed the formula incorrectly — make sure you used parentheses in the right places and spelled cell references correctly.

How do I calculate percent change if my values are in different sheets?

Reference the other sheet by typing its name followed by an exclamation mark and the cell address. For example: =((Sheet2!B2-Sheet1!B1)/Sheet1!B1)*100. The formula works the same way; you're just telling Excel to look in a different sheet for one or both values.

Should I use absolute or relative cell references?

For most percent change calculations, use relative references (like B2 and B1 without dollar signs). This way, when you copy the formula down, each row calculates its own percent change. Use absolute references (like $B$1) only if you want one cell to stay the same while others change — for example, if you're comparing all months to a fixed baseline month.