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.

In Excel syntax, if your old value is in cell A1 and your new value is in cell B1, you would write: =((B1-A1)/A1)*100. This gives you the percentage change as a positive or negative number. A positive result means an increase; a negative result means a decrease.

Excel will calculate this when ready once you press Enter. You can then copy this formula down to other rows if you have multiple pairs of values to compare.

Key Takeaways

  • The percentage change formula divides the difference between new and old values by the old value, then multiplies by 100.
  • In Excel, write =((B1-A1)/A1)*100 where A1 is the old value and B1 is the new value.
  • You can format the result as a percentage by selecting the cell and clicking the percentage button in the toolbar, which removes the need to multiply by 100.
  • Copying the formula down to multiple rows saves time when you have many values to compare.
  • Always put the old value in the denominator—reversing this gives you the wrong percentage.

Using Excel's Percentage Format to Simplify the Formula

If you format your result as a percentage before entering the formula, you can skip multiplying by 100. Instead, write: =((B1-A1)/A1) and let Excel handle the conversion.

To do this, select the cell where you want the result, right-click, and choose Format Cells. Under the Number tab, select Percentage and click OK. Now when you enter the formula without the ×100, Excel will display it as a percentage automatically.

This method is cleaner and reduces the chance of accidentally multiplying by 100 twice. Many people prefer it because the formula is shorter and the intent is clearer.

Copying the Formula to Multiple Rows

When you have a list of old and new values in columns, you don't need to type the formula for each row. Enter the formula once in the first row, then copy it down to the rest.

Click the cell containing your formula. You'll see a small square in the bottom-right corner of the cell—this is the fill handle. Click and drag it down to the last row of data. Excel automatically adjusts the cell references (A1 becomes A2, B1 becomes B2, and so on) for each row.

Alternatively, select the cell with the formula, copy it (Ctrl+C), then select the range of cells below and paste (Ctrl+V). Both methods produce the same result and save you from typing the same formula dozens of times.

Handling Negative Numbers and Zero Values

Percentage change works with negative numbers, but the result can be confusing. If your old value is negative and your new value is positive (or vice versa), the percentage change will be large because you're crossing zero. This is mathematically correct but may not reflect what you're trying to measure.

Zero in the old value position creates a problem: you cannot divide by zero, so Excel will display #DIV/0! error. If you're working with data that might contain zeros, use an IF statement to handle this: =IF(A1=0,"N/A",((B1-A1)/A1)*100). This tells Excel to show "N/A" instead of an error when the old value is zero.

For negative-to-positive changes, consider whether percentage change is the right metric. Sometimes a straightforward difference (new minus old) tells a clearer story than a percentage.

Creating a Percentage Change Column in a Spreadsheet

Set up your spreadsheet with three columns: one for the item name, one for the old value, and one for the new value. Add a header in a fourth column called "Percentage Change" or "% Change".

In the first data row of the percentage change column, enter your formula. If your old values are in column B and new values are in column C, write: =((C2-B2)/B2)*100 (or without the ×100 if you've formatted as percentage). Then copy this formula down to all rows with data.

This layout makes it straightforward to see at a glance which items increased or decreased and by how much. You can also sort by the percentage change column to find your biggest gainers and losers.

Using Absolute References for a Fixed Baseline

Sometimes you want to compare multiple new values against a single old value—for example, comparing this month's sales to last year's sales for each product. Use an absolute reference by adding dollar signs to lock the baseline cell.

If your baseline (old value) is in cell A1 and you want to compare it to new values in column B, write: =(B2-$A$1)/$A$1*100. The dollar signs tell Excel to always use A1, even when you copy the formula down. The B2 reference will change to B3, B4, and so on, but A1 stays fixed.

This is useful for comparing multiple time periods to a single reference point, or comparing several departments to a company-wide average.

Frequently Asked Questions

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

Use the formula without multiplying by 100: =((B1-A1)/A1). This gives you 0.25 instead of 25%. Format the cell as a number with decimal places. Decimals are useful when you're doing further calculations with the result.

Can I calculate percentage change between more than two values?

The basic formula only compares two values at a time. To track change across three or more time periods, calculate the percentage change between each pair separately. For example, calculate the change from month 1 to month 2, then month 2 to month 3. This shows you the trend step by step.

Why does my formula show a huge percentage when the old value is very small?

Small denominators create large percentages. If your old value is 1 and your new value is 10, the change is 900%. This is correct mathematically but may not be meaningful. Consider whether percentage change is the right metric for very small starting numbers.

How do I show percentage change as a positive or negative symbol instead of a number?

You can use conditional formatting to color cells red for decreases and green for increases. Select your percentage change column, go to Conditional Formatting in the Home tab, and choose a color scale or icon set. This makes trends visible at a glance without reading the numbers.