The Basic Formula for Percentage Change

To calculate percentage change in Excel, use this formula: =(New Value – Old Value) / Old Value. Excel will return a decimal. Multiply by 100 to see it as a percentage, or format the cell as a percentage and Excel does the multiplication for you.

For example, if a product cost $50 last month and costs $60 now, the formula is =(60-50)/50. Excel returns 0.2, which is 20 percent. This works for any two numbers: revenue, inventory, test scores, website traffic, or any metric that changes over time.

The formula works because percentage change measures how much something moved relative to where it started. The denominator (old value) is always the starting point, not the ending point. Using the wrong one is the most common mistake.

Key Takeaways

  • The percentage change formula is (New Value – Old Value) / Old Value, and you can type it directly into any Excel cell.
  • Format the result as a percentage by right-clicking the cell, choosing Format Cells, and selecting Percentage to avoid manual multiplication by 100.
  • A negative result means the value decreased; a positive result means it increased.
  • You can explore the same formula to an entire column by entering it once and dragging the fill handle down to copy it to other rows.
  • If the old value is zero, Excel returns a #DIV/0! error because you cannot divide by zero.

Setting Up Your Data in Excel

Arrange your data in columns so the formula can reference the cells. Put the old value in one column (for example, column B) and the new value in another (column C). Each row should represent one item or time period.

A typical layout looks like this: Column A holds the item name or date, Column B holds the starting value, and Column C holds the ending value. Column D is where you will put the percentage change formula. This layout makes it straightforward to copy the formula down to all rows at once.

If your data spans many rows, this column approach saves time because you enter the formula once and reuse it. If you only have two numbers to compare, you can type the formula directly into any empty cell without worrying about layout.

Entering and Copying the Formula

Click the cell where you want the result to appear. Type = to start the formula, then =(C2-B2)/B2 (replacing B2 and C2 with your actual cell references). Press Enter. Excel calculates the result when ready.

To copy this formula to other rows, click the cell with the formula, then drag the small square at the bottom-right corner of the cell down to the last row you need. Excel automatically adjusts the cell references for each row — the second row will use B3 and C3, the third row will use B4 and C4, and so on.

Alternatively, copy the cell (Ctrl+C), select the range where you want to paste, and paste (Ctrl+V). Both methods update the references correctly.

Formatting the Result as a Percentage

After you enter the formula, the cell shows a decimal like 0.2 or 0.15. To display this as a percentage (20% or 15%), select the cell or cells with the results, then right-click and choose Format Cells. Click the Number tab, select Percentage from the category list, and click OK.

Excel multiplies the decimal by 100 and adds the percent sign automatically. You can also use the percentage button in the toolbar — it is usually a % symbol. This is faster than right-clicking but gives you less control over decimal places.

If you want to show two decimal places (like 20.50%), right-click, choose Format Cells, select Percentage, and change the decimal places field to 2. The default is often zero decimal places, which rounds to whole numbers.

Interpreting Positive and Negative Results

A positive percentage means the value increased. If the result is 25%, the new value is 25% higher than the old value. A negative percentage means the value decreased. If the result is -10%, the new value is 10% lower than the old value.

The sign of the result depends entirely on whether the new value is larger or smaller than the old value. If new is larger, the numerator (new minus old) is positive, so the result is positive. If new is smaller, the numerator is negative, so the result is negative.

This is why the order matters: (New – Old) / Old always gives you the direction of change. Reversing it to (Old – New) / Old flips the sign and gives you the wrong answer.

Handling Special Cases and Errors

If the old value is zero, Excel shows #DIV/0! because division by zero is impossible. This happens when you are comparing a number to zero — for example, if a store had zero sales last year and 100 sales this year. Percentage change is not defined in this case, so you may want to note it separately or use a different metric.

If you see #DIV/0! in a column of results, check that column B (or whichever column holds the old value) does not contain any zeros. If it does, you can use an IF statement to handle it: =IF(B2=0,"N/A",(C2-B2)/B2). This displays "N/A" instead of an error when the old value is zero.

Negative numbers in either column work fine — the formula handles them correctly. A change from -50 to -30 is a 40% increase, and Excel calculates it properly.

Real-World Example: Tracking Sales Growth

Suppose you have monthly sales data. January sales were $5,000 (cell B2), and February sales were $6,200 (cell C2). Click cell D2 and type =(C2-B2)/B2. Press Enter. Excel shows 0.24, which you format as a percentage to get 24%.

This means February sales were 24% higher than January. If you have 12 months of data, enter the formula in D2, then drag it down to D13. Each row automatically calculates the percentage change from the previous month to the current month, as long as your old and new values are in the right columns.

You can also use this to compare year-over-year growth, inventory changes, website visitor counts, or any other metric that changes between two time points. The formula is the same; only the numbers and what they represent change.

Frequently Asked Questions

What if I want to calculate percentage change from a starting value to multiple ending values?

Use an absolute reference for the starting value. Instead of =(C2-B2)/B2, type =(C2-$B$2)/$B$2. The dollar signs lock B2 in place, so when you copy the formula down, it always divides by B2 but uses different values from column C. This is useful for tracking growth from a baseline year or starting inventory level.

Can I calculate percentage change if the values are in the same cell at different times?

No, you need two separate cells or columns — one for the old value and one for the new value. If you only have one number and need to compare it to a previous version, you must enter both values into Excel first. You cannot retrieve the old value from a cell that now contains the new value.

Why does my percentage change look wrong?

Check that you divided by the old value, not the new value. The formula must be (New – Old) / Old. Also verify that your cells are formatted as numbers, not text. If a cell looks like a number but is stored as text, the formula may not work. Right-click the cell, choose Format Cells, and select Number.

How do I show percentage change with a plus or minus sign?

Format the cells as a custom number format. Right-click, choose Format Cells, click the Number tab, select Custom, and enter +0.0%;-0.0%;0.0% in the format code field. This displays positive results with a plus sign, negative results with a minus sign, and zero as zero.

What is the difference between percentage change and percentage point change?

Percentage change is what the formula calculates — the relative shift from old to new. Percentage point change is the straightforward difference between two percentages. If approval rates rose from 60% to 75%, that is a 15 percentage point increase but a 25% percentage change (because 15 ÷ 60 = 0.25). Use the formula for percentage change unless you are specifically comparing two percentages.