How to Calculate Percentage Change in Excel, Month over Month and Year over Year

The percentage change formula in Excel and Google Sheets, plus the fixes for a zero or negative base, percentage points and average monthly growth.

Founder, Glow Reports. , 3 min read
  • Excel
  • Formulas
  • Reporting Workflows
Four bar blocks on a platform with curved arrows between them, two pointing up and one pointing down, beside a sheet of grid paper
On this page
  1. How do you calculate percentage change in Excel?
  2. How do you calculate month-over-month growth for a whole year?
  3. Why does Excel show #DIV/0! or a strange result for percentage change?
  4. What is the difference between percent and percentage points?
  5. How do you calculate the average monthly growth rate?
  6. Where does Glow Reports fit?
  7. Common questions

Your monthly report needs a growth line: revenue up or down against last month, orders against the same month last year. The formula is short, new value minus old value, divided by the old value. It still goes wrong in four places: a zero or negative starting value, a rate that moves from 2% to 2.5%, an average of monthly growth rates, and a seasonal month compared with the one before it. Here is the formula in Excel and Google Sheets, then each trap with the fix.

How do you calculate percentage change in Excel?

Subtract the old value from the new one, divide by the old value, and format the cell as a percentage. With last month in B2 and this month in C2, type in D2, then select HomePercent Style.

Microsoft's own example uses earnings of 2,342 in November and 2,500 in December. returns 0.06746, which shows as 6.75% once formatted. A fall works the same way, with a minus sign. returns -0.03, or -3.00% Microsoft Support.

Three habits keep the column right.

  • Keep the parentheses. divides first and subtracts after, so it returns C2 minus 1.
  • Format the result, not the inputs. Excel stores 6.75% as 0.0675. A cell that shows 6.75 with no percent sign holds a figure 100 times too large for any formula that uses it.
  • Use the Increase Decimal and Decrease Decimal buttons to show one decimal. The cell keeps the full value for later math.

In Google Sheets the formula is the same. Select the cells and choose FormatNumber, then Percent Google Docs Editors Help.

How do you calculate month-over-month growth for a whole year?

Put the months in rows and the totals in one column, then compare each row with the row above. With months in A2:A13 and revenue in B2:B13, type in C3 and fill it down. The first month stays empty. It has nothing to compare with.

Here is a short example, recomputed in code before it went into this article:

MonthRevenueChange vs last month
January40,000
February44,00010.0%
March39,600-10.0%
April47,52020.0%

If the monthly totals come from an order export, build them first with a pivot table grouped by month. A pivot can also show the change itself through Show Values As% Difference From with (previous) as the base item. The steps are in how to group a pivot table by month.

Year-over-year growth uses the same formula with a twelve-row gap. With 24 months in B2:B25, compares January this year with January last year. Use it for seasonal businesses. Here is why. A December retail peak followed by a quiet January shows a large monthly fall every year, and the year-over-year line tells you whether this January is better or worse than the last one.

Why does Excel show #DIV/0! or a strange result for percentage change?

Because the old value is zero or negative. A change from zero has no percentage, so Excel returns #DIV/0!. A change from a negative number flips the sign, so a loss that turns into a profit shows as a fall.

Take a product line that lost 5,000 last month and made 2,000 this month. returns -140%, a fall, for what is an improvement. Pick one of two fixes and keep it for the whole report:

  • Divide by the absolute value of the old figure: . The same example returns +140%. ABS returns a number without its sign Microsoft Support.
  • Show no percentage at all when the base is zero or negative, and show the difference in currency instead. Many finance teams prefer this, because +140% on a loss tells the reader little.

For a zero base, wrap the formula so the report shows a blank instead of an error: . IFERROR returns the value you give it when the formula returns an error, and the formula's result otherwise Microsoft Support. It hides every error, though, including a typo in a reference. hides only the zero case and is the safer choice.

What is the difference between percent and percentage points?

Percentage points are the gap between two percentages. Percent is the change relative to where you started. A conversion rate that goes from 2.0% to 2.5% rose by 0.5 percentage points, and by 25%.

A percentage point is the arithmetic difference between two percentages Wikipedia. Both figures are true. Readers mix them up. Write the unit next to the figure in your report: "conversion rate up 0.5 points, from 2.0% to 2.5%". For rates such as conversion, churn or retention, the points difference is with both cells as percentages. Keep the relative change for amounts such as revenue, orders or average order value.

How do you calculate the average monthly growth rate?

Use the compound rate, not the average of the monthly changes. In Excel, returns the steady monthly rate that turns 40,000 into 47,520 over three months: 5.91%. The simple average of the three changes above is 6.67%, and it overstates the growth.

48,545 is where 40,000 lands after three months at the simple average of 6.67%. The real April figure is 47,520.

The average fails because each change applies to a different base. A rise of 10% followed by a fall of 10% does not bring you back to the start: 40,000 becomes 44,000, then 39,600. RRI takes the number of periods, the start value and the end value, and returns the equivalent rate of growth Microsoft Support. The same figure without RRI, and in any spreadsheet:

Compound monthly growth rate, 5.91%
=(47520/40000)^(1/3)-1

The exponent is one over the number of periods, which is the number of months minus one. Four months of data hold three changes. Getting this count wrong is the most common slip, so check that the rate rebuilds the last month: should land close to 47,520.

Where does Glow Reports fit?

The formulas above are enough when the monthly totals already sit in a sheet. The work grows when the totals come from raw exports that change shape every month, and the growth line needs to be rebuilt by hand each time.

With Glow Reports, you upload the CSV or Excel export and ask in plain language for revenue by month with the change against the previous month and the same month last year. The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can check which base it divides by and edit the query if a zero month needs different handling. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

What is the formula for percentage change?

(New value - old value) / old value. In Excel, with the old value in B2 and the new one in C2, type and format the cell as a percentage.

How do I calculate a percentage decrease in Excel?

With the same formula. A decrease returns a negative number, such as -3.00% for a fall from 2,500 to 2,425. To show the fall as a positive figure in a "decrease" column, use .

Why is my percentage change over 100%?

Because the new value is more than twice the old one. A move from 1,000 to 2,500 is a change of 150%. Check the base too. A small starting value, such as a product's first month, makes any later month look like a huge rise.

Should I average monthly growth rates?

No. Use the compound rate with or . The simple average ignores that each month grows from a different base and overstates growth.

How do I calculate year-over-year growth in Excel?

Compare each month with the same month a year earlier. With monthly totals in one column, the formula skips twelve rows: for January this year against January last year.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload this month's export to Glow Reports and ask for revenue by month with the change, and the SQL behind each figure.

Keep reading