How to compare periods in Excel
"How are we doing versus last month?" is the most common question a spreadsheet answers. Period comparison lines up two windows of time and shows the change — in pounds and in percent. Here's how, and the one-click alternative.
When you reach for a period comparison
The same simple maths answers most of the questions a report ever gets asked.
Monthly reviews
This month vs last, with the percentage everyone asks for.
Year-over-year
Strip seasonality by comparing the same month a year apart.
Budget vs actual
The same maths compares plan against reality.
How to do it in Excel
Two columns, two formulas, then a little formatting to make the movers jump out.
Put the periods side by side
Category, then a column for this period and one for last period.
Absolute change
=ThisPeriod − LastPeriod for the raw movement in pounds or units.
Percentage change
=(ThisPeriod − LastPeriod)/LastPeriod, formatted as a percentage. Guard against divide-by-zero with IFERROR.
Make it readable
Add up/down arrows with conditional formatting and sort by the biggest movers.
Two traps catch people out. Dividing by zero (or a tiny base) makes percentage change explode or error — wrap it in IFERROR and flag "new" lines separately. And a big percentage on a tiny number is noise: +400% on £5 isn't a story. Always show the absolute change next to the percentage so readers can judge whether the swing actually matters.
The faster way — skip the setup
Guarding every percentage and rebuilding each cycle adds up. DataHub Pro does the comparison from two periods.
Simple maths, fiddly polish
- Line up the two periods by hand
- Guard every percentage against divide-by-zero
- Add arrows and sorting manually
- Rebuild each reporting cycle
Comparisons, done for you
- Upload two periods — change and % computed
- New and zero-base lines handled cleanly
- Biggest movers surfaced automatically
- Straight into a board-ready report
FAQ
How do I calculate percentage change in Excel?
Use =(new − old)/old and format the result as a percentage. Wrap it in IFERROR to handle cases where the old value is zero, which would otherwise cause a divide-by-zero error.
How do I compare this month to last month in Excel?
Put each period in its own column next to the category, then add an absolute-change column (this − last) and a percentage-change column ((this − last)/last). Sort by the change to see the biggest movers.
How do I do a year-over-year comparison?
Compare the same period one year apart — for example June this year against June last year — so seasonal patterns cancel out. The change and percentage-change formulas are identical; only the two columns you pick differ.
Keep exploring
More visual analysis you can run on your own data — no sign-up needed to read.
This period vs last, instantly
Upload two periods and DataHub Pro computes the change, the percentage and the biggest movers for you. Free to try, no card.
Start free →