The Basic Formula for Percentage Increase or Decrease

To calculate percentage change in Excel, use this formula: (New Value − Old Value) / Old Value, then format the result as a percentage. If you're tracking a price that went from $50 to $75, you'd enter =(75-50)/50 in a cell, and Excel shows 0.5 or 50% depending on how you format it.

The formula works the same way for decreases. If something dropped from 100 to 75, the formula =(75-100)/100 gives you −0.25 or −25%. The negative sign tells you it's a decrease rather than a gain.

Most people make one mistake here: they divide by the wrong number. Always divide by the starting value, not the ending value. That's what makes the math show you the actual rate of change.

Key Takeaways

  • The percentage change formula is (New Value − Old Value) / Old Value, and you divide by whichever number you started with.
  • Format cells as percentage after entering the formula so Excel displays 25% instead of 0.25.
  • Negative results mean a decrease; positive results mean an increase.
  • You can build this formula into a spreadsheet once and copy it down to calculate changes for dozens of rows at once.
  • The same formula works for any numbers: prices, sales figures, inventory counts, or test scores.

Setting Up the Formula in Your Spreadsheet

Put your old values in one column and new values in another. Say column A holds last month's sales and column B holds this month's. Click on cell C1 (or wherever you want the percentage change to appear) and type =((B1-A1)/A1). The parentheses keep Excel from getting confused about the order of operations.

Press Enter. Excel calculates the result as a decimal—usually something like 0.15 or −0.08. That's correct math, but it doesn't look like a percentage yet. Right-click the cell, choose Format Cells, select Percentage from the Category list, and click OK. Now it displays as 15% or −8%, which is what you actually want to see.

Once the formula is in one cell and formatted correctly, you can copy it down to all your other rows. Click the cell with the formula, copy it (Ctrl+C on Windows, Cmd+C on Mac), then select the range of cells below it and paste. Excel automatically adjusts the row numbers in each formula, so C2 becomes =((B2-A2)/A2), C3 becomes =((B3-A3)/A3), and so on.

Handling Zero and Negative Starting Values

If your old value is zero, the formula breaks. You can't divide by zero, and Excel shows #DIV/0! as an error. This happens in real situations—a product that had zero sales last month but 50 this month, or a new account that didn't exist before. The percentage change is technically infinite, so the formula can't express it as a number.

One way to handle this is to use an IF statement that checks whether the old value is zero before calculating. Type =IF(A1=0,"N/A",((B1-A1)/A1)). This tells Excel: if A1 is zero, display "N/A" instead of trying to divide. Otherwise, calculate normally. This keeps your spreadsheet from looking broken while being honest that the math doesn't work in that case.

Negative starting values are mathematically valid but often confusing to interpret. If you started with −$50 (a debt) and ended with +$50 (a credit), the percentage change formula still works, but the result can be misleading. A −$50 to +$50 swing looks like a 200% increase mathematically, but it's really a swing from one side of zero to the other. Think about whether the formula makes sense for what you're actually measuring before you use it.

Comparing Multiple Periods at Once

You can build a table that shows percentage changes across several time periods. Put dates or period names across the top row and values in the rows below. Then create a separate section for your percentage calculations, or add a column for each comparison you want to make.

For example, if you have January sales in column A, February in column B, and March in column C, you could calculate January-to-February change in column D and February-to-March change in column E. Each column gets its own formula: =((B1-A1)/A1) for D1, =((C1-B1)/B1) for E1, and so on. This layout makes it straightforward to spot which periods had the biggest swings.

You can also calculate year-over-year changes by comparing the same month in different years. If 2023 data is in columns A through L (January through December) and 2024 data is in columns M through X, you'd compare A to M, B to N, C to O, and so on. The formula stays the same; you're just pointing it at different cells.

Formatting to Show the Right Number of Decimal Places

When you format cells as percentage, Excel usually shows two decimal places by default—so 0.1547 becomes 15.47%. That's fine for most purposes, but sometimes you want more or fewer digits. Right-click the cells, choose Format Cells, click the Numbers tab, and look for the Decimal Places field. Change it to whatever makes sense for your data.

For rough comparisons, one decimal place (15.5%) is often enough. For financial or scientific work, two or three decimal places (15.47% or 15.471%) gives more precision. Zero decimal places (15%) works when you're presenting to people who don't need to see small differences. There's no single right answer—it depends on how precise your data is and what you're using it for.

You can also use conditional formatting to color-code the results. Select your percentage cells, go to Conditional Formatting in the Home tab, and set rules like "highlight cells greater than 10% in green and less than −10% in red." This makes large changes jump out visually without making you scan through numbers.

Common Mistakes and How to Avoid Them

The most frequent error is dividing by the new value instead of the old one. If you accidentally type =((B1-A1)/B1), you get a different answer that doesn't represent the actual rate of change. Double-check your formula before you copy it down to dozens of rows—it's much easier to fix one cell than to redo a hundred.

Another mistake is forgetting to format as percentage. The math is correct, but 0.25 looks like a small number until you realize it means 25%. Formatting takes two seconds and makes your spreadsheet readable.

People also sometimes mix up which direction they're measuring. If you're comparing "before and after," make sure the "before" value is always in the denominator. If you're comparing "this year versus last year," the last year number goes in the denominator. Being clear about your direction prevents confusion later when you're explaining the numbers to someone else.

Frequently Asked Questions

What if I want to show percentage increase as a whole number without decimals?

Format the cells as percentage and set decimal places to zero. Right-click, choose Format Cells, go to the Numbers tab, select Percentage, and change Decimal Places to 0. Now 0.1547 displays as 15% instead of 15.47%.

Can I calculate percentage change if my data is in different sheets?

Yes. Reference the other sheet in your formula by typing the sheet name followed by an exclamation point and the cell. For example, =((Sheet2!B1-Sheet1!A1)/Sheet1!A1) compares a value from Sheet2 to a value from Sheet1. The formula works exactly the same way.

How do I show a percentage increase as a positive number and a decrease as negative?

The formula =(New-Old)/Old already does this automatically. Increases show as positive (0.25 or 25%), and decreases show as negative (−0.15 or −15%). No extra step needed.

What should I do if some of my old values are zero?

Use an IF statement to check for zero first: =IF(A1=0,"N/A",((B1-A1)/A1)). This displays "N/A" when the old value is zero instead of showing a division error. You can also use "—" or leave it blank, depending on what makes sense for your spreadsheet.

Can I calculate the percentage of a total instead of percentage change?

That's a different formula. For percentage of a total, use =(Part/Total). For example, if you sold 30 units out of 200 total, the formula is =(30/200), which gives 15%. Percentage change always compares an old value to a new value, while percentage of a total compares a part to a whole.