Spreadsheets

Compare this month with the same month last year in a chart

Compare each month's spending with the same month last year in Excel: two SUMIFS series using EDATE shifts and a clustered column chart.

Editorial illustration for Compare this month with the same month last year in a chart

To compare this year with last year month by month, list the twelve months, add a This year column with SUMIFS for each month, and a Last year column with the same SUMIFS shifted back twelve months using EDATE. Select the month labels and both columns, insert a Clustered Column chart, and each month shows two bars side by side.

Set up the month list

In column E, put the first month's start date, for example 1 April 2026 in E2, and in E3 write =EDATE(E2,1), filled down to E13. Format column E as mmm yyyy. In column A, make plain text labels for the chart axis with =TEXT(E2,"mmm"). Starting at April gives you a financial-year view, and starting at January gives a calendar-year view.

The two SUMIFS columns

  • B2, this year: =IF($E2>TODAY(), NA(), SUMIFS(Data[Amount], Data[Date], ">="&$E2, Data[Date], "<"&EDATE($E2,1)))
  • C2, last year: =SUMIFS(Data[Amount], Data[Date], ">="&EDATE($E2,-12), Data[Date], "<"&EDATE($E2,-11))
  • D2, change: =IF(N(C2)=0, NA(), B2/C2-1), formatted as a percentage

EDATE moves a date by whole months, so EDATE of 1 April 2026 minus 12 is 1 April 2025, and minus 11 is 1 May 2025. That pair of dates is exactly last year's April. Returning #N/A for months that have not happened yet stops the chart showing a bar of zero.

Build the chart

  1. Select A1:C13, the month labels and the two value columns, with headers.
  2. Choose Insert, then Clustered Column.
  3. Colour last year in a pale grey and this year in a strong colour, so the eye goes to the current bars.
  4. Add a gap width of about 60 per cent and a legend at the top.
  5. Leave the vertical axis starting at zero. Truncated axes exaggerate small changes.

Why does a month look so different from last year?

Check timing before reading anything into the bars. Say Diwali shopping, gifts and travel fell in October this year but in November last year. Then October looks much higher and November much lower, but the festival cost simply moved.

MonthThis year (₹)Last year (₹)Change
Oct58,00047,000+23.4%
Nov41,00052,000-21.2%
Oct and Nov together99,00099,0000.0%

These are invented numbers. October is 58,000 divided by 47,000 minus 1, which is 23.4 per cent up, and November is 41,000 divided by 52,000 minus 1, which is 21.2 per cent down. Together the two months match. Adding a two-month total next to the monthly bars stops you drawing the wrong conclusion.

For a running view, add a year-to-date pair. In F2 write =SUM(B$2:B2) and in G2 write =SUM(C$2:C2), filled down. These show how far ahead or behind last year you are at the end of each month, which is steadier than the single-month bars when one festival month swings the picture.

What else can move a comparison?

  • Annual items, such as insurance premiums, school fees or society charges, landing in a different month.
  • A change in categories or in how you record shared payments.
  • A new EMI, a house move or a change in family size, which affects every month after it.
  • Whether transfers between your own accounts were left out both years.

Use it for questions, not verdicts

The chart is best at showing where to look. A month that is higher than last year is a prompt to open that month's transactions, find the two or three largest items, and decide whether they were planned. If you want the comparison without maintaining the SUMIFS, SpendixAI offers a library of 25 ready-made reports and a Report builder for filtering and grouping your records.

Quick checklist: both years use the same date logic, future months are blank, last year's records are complete, and festival timing has been considered.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports