The Basic Percentage Change Formula in Excel
To calculate percentage change in Excel, subtract the old value from the new value, divide by the old value, and multiply by 100. The formula is: ((New Value – Old Value) / Old Value) * 100. If you put the old value in cell A1 and the new value in cell B1, you would type =((B1-A1)/A1)*100 into an empty cell to get the percentage change.
Excel will return a decimal number representing the percentage. A result of 25 means a 25% increase; a result of -15 means a 15% decrease. The negative sign tells you the value went down rather than up.
You do not need to multiply by 100 if you format the cell as a percentage after entering the formula. If you use =((B1-A1)/A1) without the *100, then right-click the cell, choose Format Cells, and select Percentage, Excel will display the result as a percentage automatically.
Key Takeaways
- The percentage change formula is ((New Value – Old Value) / Old Value) * 100, and you enter it as a formula starting with an equals sign.
- If your old value is in A1 and new value is in B1, type =((B1-A1)/A1)*100 to see the result as a number like 25 or -15.
- You can skip the *100 and instead format the cell as Percentage to display the result with a % symbol.
- Negative results mean the value decreased; positive results mean it increased.
- Copy the formula down to calculate percentage change for multiple rows of data at once.
Setting Up Your Data in Columns
Arrange your data so the old values are in one column and the new values are in another. For example, put January sales in column A and February sales in column B. This layout makes it straightforward to write one formula and copy it down for all your rows.
Leave the first row for headers if you want to label what each column contains. Type "Original" in A1, "New" in B1, and "Percent Change" in C1. Then start your data in row 2. This keeps your spreadsheet organized and makes it clear what each column represents.
Make sure both columns contain numbers, not text. If a cell looks like a number but Excel treats it as text, the formula will return an error. Numbers are right-aligned by default in Excel; text is left-aligned. If your numbers are left-aligned, they are stored as text and need to be converted.
Entering and Copying the Formula
Click on the cell where you want the percentage change to appear—usually the first empty row in a new column. Type the formula =((B2-A2)/A2)*100, using the row number that matches your first data row. Press Enter, and Excel calculates the result.
To explore this formula to multiple rows, click the cell with your formula, then grab the small square at the bottom-right corner of the cell (called the fill handle) and drag it down as far as you need. Excel automatically adjusts the row numbers in each copy—row 2 becomes row 3, row 4, and so on. This saves you from typing the formula over and over.
Alternatively, click the cell with your formula, press Ctrl+C to copy, then select the range of cells below it where you want the formula to go. Press Ctrl+V to paste. Excel fills all the selected cells with the formula, adjusting row numbers automatically.
Formatting Your Results as Percentages
If you used the formula without *100—that is, =((B2-A2)/A2)—your result will appear as a decimal like 0.25 instead of 25. To display it as a percentage, select the cell or cells with your results, right-click, and choose Format Cells.
In the Format Cells dialog, click the Number tab, then select Percentage from the Category list on the left. Set the decimal places to however many you want—usually 1 or 2 for business data. Click OK, and Excel displays your results with a % symbol. A decimal of 0.25 becomes 25%, and -0.15 becomes -15%.
You can also use the percentage button in the toolbar. Select your cells, then click the % button in the ribbon. This applies percentage formatting when ready, though it may not let you control decimal places as precisely.
Handling Zero and Negative Old Values
If your old value is zero, the formula will return a #DIV/0! error because you cannot divide by zero. This is a real mathematical problem, not a mistake in Excel. You cannot calculate a meaningful percentage change from zero to any other number using this formula.
If you encounter this, you have a few options. You can leave the cell blank or type "N/A" to show the calculation is not possible. Alternatively, use an IF statement to check for zero: =IF(A2=0,"N/A",((B2-A2)/A2)*100). This tells Excel to display "N/A" if the old value is zero, and calculate the percentage change only if it is not.
Negative old values work fine mathematically. If you go from -10 to 20, the percentage change is 300% (an increase of 30 divided by the old value of -10). The result may seem counterintuitive, but the formula is working correctly.
Common Mistakes to Avoid
The most common 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 result. Always subtract the old from the new: (New – Old), not (Old – New).
Another mistake is forgetting the parentheses. The formula must be ((B2-A2)/A2)*100, not =B2-A2/A2*100. Without the parentheses around (B2-A2), Excel follows the order of operations and divides before subtracting, giving you the wrong answer.
Do not mix up which column is which. If your old values are in column B and new values are in column A, swap them in the formula. Using the wrong columns will give you the opposite sign and the wrong magnitude.
If you see #VALUE! error, check that both cells contain numbers. If one contains text or a space, the formula cannot work. Delete any extra spaces and make sure both cells have actual numbers in them.
Frequently Asked Questions
What does a negative percentage change mean?
A negative percentage change means the value decreased from the old amount to the new amount. For example, if sales went from 100 to 75, the percentage change is -25%, showing a 25% drop. The negative sign is part of the result and tells you the direction of change.
Can I calculate percentage change if my old value is negative?
Yes, the formula works with negative old values, but the result can be confusing. If you go from -10 to 20, the percentage change is 300%. The math is correct, but the interpretation is tricky because you are comparing a negative starting point to a positive ending point.
How do I show percentage change with one decimal place?
After formatting your cells as Percentage, right-click and choose Format Cells. In the Number tab, set Decimal Places to 1. This displays results like 25.3% instead of 25.25%. You can adjust the number to show more or fewer decimal places as needed.
What if I want to see the result as a whole number without decimals?
Format the cells as Percentage and set Decimal Places to 0. Alternatively, if you used the *100 version of the formula, format the cells as Number with 0 decimal places. Both methods round the result to the nearest whole number.
Can I use this formula to compare more than two values?
This formula compares exactly two values: an old and a new. To track change across three or more time periods, calculate percentage change between each pair separately. For example, calculate the change from January to February, then February to March, using the same formula for each pair.