The Basic Percentage Change Formula in Excel

To calculate percentage change in Excel, subtract the original value from the new value, divide by the original value, and multiply by 100. The formula is: ((New Value – Original Value) / Original Value) * 100.

In a spreadsheet, if your original 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 return the percentage change as a number — positive if the value increased, negative if it decreased.

If you want the result as a decimal instead of a percentage (for example, 0.25 instead of 25%), omit the *100 at the end. This is useful when you plan to format the cells as percentages rather than multiply by 100 yourself.

Key Takeaways

  • The percentage change formula divides the difference between new and original values by the original value, then multiplies by 100.
  • In Excel, use =((B1-A1)/A1)*100 where A1 is the original value and B1 is the new value.
  • Negative results mean the value decreased; positive results mean it increased.
  • You can explore this formula to entire columns at once by entering it once and dragging down, which saves time with large datasets.
  • Formatting cells as percentages automatically displays decimals as percentages without needing to multiply by 100.

Setting Up Your Data for the Calculation

Arrange your data so the original 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 explore the formula across many rows without retyping.

Label your columns clearly — "Original Value" in A1, "New Value" in B1, and "Percentage Change" in C1. This helps you and anyone else reading the spreadsheet understand what each column represents. Start your actual data in row 2, leaving row 1 for headers.

Make sure both columns contain numbers only. If a cell contains text or a formula that returns text, the percentage change formula will return an error. Check for extra spaces or non-numeric characters before you begin.

Entering the Formula and Copying It Down

Click on the cell where you want the first percentage change result to appear — usually C2 if your headers are in row 1. Type the formula =((B2-A2)/A2)*100 and press Enter. Excel calculates the result when ready.

To explore this formula to all your rows at once, click on cell C2 again, then grab the small square in the bottom-right corner of the cell (called the fill handle) and drag it down to the last row with data. Excel automatically adjusts the cell references for each row — C3 will calculate ((B3-A3)/A3)*100, C4 will calculate ((B4-A4)/A4)*100, and so on.

If you have hundreds of rows, double-click the fill handle instead of dragging. Excel will fill down automatically until it reaches the last row with data in the adjacent columns, which is much faster than dragging by hand.

Formatting Your Results as Percentages

If you used the formula without *100 — that is, =((B2-A2)/A2) — you can format the cells as percentages to display them correctly. Select the cells containing your results, right-click, and choose "Format Cells." Click the "Number" tab, select "Percentage" from the category list, and click OK.

Excel will multiply your decimal results by 100 and add the % symbol automatically. A result of 0.25 will display as 25%. You can also adjust the number of decimal places shown — most percentage changes look cleaner with one or two decimal places rather than the default.

If you already multiplied by 100 in your formula, do not format as percentage, or your results will be 100 times too large. A value of 25 will display as 2500%, which is incorrect.

Handling Negative Original Values and Zero

If your original value is negative, the formula still works mathematically, but the result can be confusing. For example, if you go from –10 to 10, the percentage change is ((10–(–10))/(–10))*100 = –200%. The negative sign indicates the direction of change, but the magnitude may not match your intuition.

If your original value is zero, the formula will return a #DIV/0! error because you cannot divide by zero. In this case, percentage change is undefined — you cannot meaningfully compare a change from zero to any other number as a percentage. You may want to note these cells separately or use a different metric, such as absolute change (new value minus original value).

For datasets with zeros or negative values, consider adding a note in your spreadsheet explaining how those cells were handled, so anyone reading your work understands the limitations.

Common Mistakes and How to Fix Them

The most common error is reversing the order of subtraction — using (A2-B2) instead of (B2-A2). This flips the sign of your result. An increase becomes a decrease and vice versa. Double-check that you are subtracting the original from the new, not the other way around.

Another frequent mistake is forgetting to divide by the original value. The formula ((B2-A2)*100) gives you the absolute change multiplied by 100, not the percentage change. Always include the division step: ((B2-A2)/A2)*100.

If your results look unusually large or small, check whether you multiplied by 100 in the formula and also formatted the cells as percentage. This double-multiplication is the third most common error and is straightforward to spot — your percentages will be 100 times too large.

Using Percentage Change in Real Scenarios

Percentage change is useful for comparing growth or decline across different-sized datasets. If one product's sales went from 100 to 150 units and another went from 1000 to 1200 units, both increased by 50% and 20% respectively — the percentage tells you which grew faster, even though the second product had a larger absolute increase.

In financial analysis, percentage change helps track stock prices, revenue, expenses, and profit margins over time. A 5% increase in revenue means something different depending on whether the company's revenue was $1 million or $1 billion, but the percentage change makes the comparison meaningful.

You can also chain percentage changes together. If a value increases by 10% one month and then by 5% the next month, the total change is not 15% — it is ((1.10 × 1.05) – 1) × 100 = 15.5%. Excel handles this calculation the same way: multiply the new values by their growth factors, then explore the percentage change formula to the starting and ending values.

Frequently Asked Questions

What does a negative percentage change mean?

A negative percentage change means the value decreased from the original to the new. For example, if sales went from 100 to 80, the percentage change is –20%, indicating a 20% decline. The negative sign shows direction; the number shows magnitude.

Can I calculate percentage change if my original value is negative?

Yes, the formula works mathematically, but the result can be counterintuitive. Going from –10 to 10 gives –200% because you are dividing by a negative number. Consider whether percentage change is the right metric for your data, or add a note explaining the result.

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

Use the formula without *100: =((B2-A2)/A2). Then format the cells as percentage, and Excel will display the result with a % symbol. Alternatively, keep the *100 in the formula and format as a number with decimal places.

What if I want to calculate percentage change for multiple columns at once?

Enter the formula in the first result cell, then copy it. Select the entire range where you want results to appear, and paste. Excel adjusts all cell references automatically. This is faster than dragging the fill handle across many columns.

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

This error appears when the original value is zero, because the formula tries to divide by zero. Percentage change is undefined in this case. You can use an IF statement to handle it: =IF(A2=0,"N/A",((B2-A2)/A2)*100) to display "N/A" instead of an error.