How to calculate percentage change in Excel between an old and a new value, worked on six product lines this year against last. Covers formatting as a percentage, lines that start or end at zero, a loss turning into a profit, applying and reversing a percentage increase, why the total change is not the average of the line changes, and percentage points.
Percentage change in Excel is the new value minus the old value, divided by the old value: =(C2-B2)/B2, formatted as a percentage. Revenue that rises from $1,250,000 to $1,400,000 has changed by +12.0%. The formula is one cell; the work is in the zeros, the negatives and the totals, which is where most percentage-change errors come from.
Percentage change = (New value − Old value) / Old value
With last year in B2 and this year in C2:
=(C2-B2)/B2
or, the same result in fewer characters:
=C2/B2-1
A positive result is an increase, a negative one a decrease. Microsoft's Calculate percentages page uses the first form for both directions.
The result is a decimal, 0.12. To show it as 12%, select the cells and click Percent Style in the Number group on the Home tab, or press Ctrl+Shift+% on Windows (Format numbers as percentages). On a Mac, use Percent Style on the Home tab. Format the cell, never multiply by 100: a formatted 0.12 shows 12%, but a formatted 12 shows 1200%.
One row per item, with the old and new values side by side:
| Column | Content |
|---|---|
| A | Product line, customer or account |
| B | Old value: last year, last month or budget |
| C | New value: this year, this month or actual |
Both columns must be in the same units and built the same way: the same revenue definition, the same treatment of returns, the same period length. A change between a 12-month figure and an 11-month figure measures the calendar, not the business.
In D2, filled down to row 7, the formula that handles zero and negative starting values (explained in the next section):
=IF(B2=0,"n/a",(C2-B2)/ABS(B2))
| Product line | Last year (USD) | This year (USD) | Change (USD) | % change |
|---|---|---|---|---|
| Pumps | 1,250,000 | 1,400,000 | 150,000 | 12.0% |
| Valves | 860,000 | 817,000 | -43,000 | -5.0% |
| Seals | 310,000 | 372,000 | 62,000 | 20.0% |
| Filters | 95,000 | 0 | -95,000 | -100.0% |
| Sensors | 0 | 120,000 | 120,000 | n/a |
| Service contracts | 540,000 | 594,000 | 54,000 | 10.0% |
| Total | 3,055,000 | 3,303,000 | 248,000 | 8.1% |
The total row uses the totals, not the lines:
=(SUM(C2:C7)-SUM(B2:B7))/SUM(B2:B7)
(3,303,000 − 3,055,000) / 3,055,000 = 248,000 / 3,055,000 = 8.12%, shown as 8.1%.
The check: the line changes in dollars must sum to the total change. 150,000 − 43,000 + 62,000 − 95,000 + 120,000 + 54,000 = 248,000. If they do not, a line is missing or double counted.
A zero start. Sensors had no sales last year, so =(C6-B6)/B6 divides by zero and returns #DIV/0!. There is no percentage for "from nothing"; the IF(B2=0,"n/a",...) traps it, and the line should be labeled new, with its $120,000 shown in dollars.
A zero end. Filters was discontinued: 0 this year gives −100.0%, which is correct. Label it discontinued so a reader does not hunt for an error.
A negative start. Operating profit moving from −40,000 to +20,000 improved by 60,000. The plain formula gets the sign wrong:
| Formula | Result |
|---|---|
=(C2-B2)/B2 |
-150% |
=(C2-B2)/ABS(B2) |
+150% |
Dividing by the absolute value of the old figure makes the sign follow the direction of travel. Even so, +150% says little about a swing from loss to profit. Report the change in dollars, +$60,000, and say in words that the result moved from a loss to a profit.
Going the other way, from a rate to a value, uses one plus the rate. With the rate in D2:
=B2*(1+D2)
=B2*(1-D2)
=C2/(1+D2)
The first applies an increase: 1,250,000 × 1.12 = 1,400,000. The second applies a decrease: 1,250,000 × 0.88 = 1,100,000. The third reverses an increase to find the starting value: 1,400,000 / 1.12 = 1,250,000. Reversing with =C2*(1-D2) is a common mistake: it returns 1,232,000, not 1,250,000, because 12% of the new value is larger than 12% of the old one.
The simple average of the five line changes that can be computed is:
(12.0% − 5.0% + 20.0% − 100.0% + 10.0%) / 5 = −12.6%
That says the business shrank when it grew by 8.1%. The average gives Filters, a $95,000 line, the same weight as Pumps, a $1,250,000 line, and leaves out Sensors entirely. The total change is the change in the totals; any average across lines has to be weighted by size, which is what weighted average in Excel covers, and count-weighted and value-weighted explains why the two disagree.
When the measure is already a percentage, say which change you mean. A gross margin that moves from 30% to 33% has risen by 3 percentage points and by 10%, because (33 − 30) / 30 = 0.10. Readers of a finance report usually expect points; write "up 3 points" or "up 3 pp" and keep "%" for relative change.
Percentage difference compares two values with no before and after, such as two regions, and usually divides the gap by their average: =ABS(A2-B2)/AVERAGE(A2,B2).
Change over several years is a different calculation again. One-period change compounds; for the average yearly rate across a span, use CAGR in Excel.
To flag the lines whose change matters, color them with conditional formatting in Excel, and for how AI tools trip on exactly these calculations, see why AI gets numbers wrong.
A percentage says how much a number moved, not why. Covirage's tools compute changes by line and in total from your export, handle new and lost lines explicitly, and break the change into its drivers: customers, products and prices. The external AI model explains the drivers without doing the arithmetic. See revenue driver analysis, and how to read a movements page for the change shown as amounts and percentages together. To split a change into price, volume and mix, see price-volume-mix analysis, and to show the changes on one page, see how to build an Excel dashboard.
=(new-old)/old, for example =(C2-B2)/B2, formatted as a percentage. The shorter =C2/B2-1 gives the same answer. Positive results are increases, negative results are decreases.
Use the same formula. A fall from 860,000 to 817,000 gives =(817000-860000)/860000 = -5.0%. If you want the decrease shown as a positive number, use =(B2-C2)/B2 and label the column as a decrease.
Multiply by one plus the percentage: =B2*(1+10%) or =B2*(1+D2) where D2 holds the rate. To take a percentage off, use =B2*(1-D2). To find the original before an increase, divide: =C2/(1+D2).
Percentage change compares a new value with an old one, so the order matters. Percentage difference compares two values with no before and after, usually dividing the gap by their average: =ABS(A2-B2)/AVERAGE(A2,B2).