Spreadsheets

Year-over-year spending by category with % change

Build a year-over-year spending table by category in Excel: last year, this year, rupee change and percent change, with sorting and new categories handled.

Editorial illustration for Year-over-year spending by category with % change

A year-over-year spending table has one row per category and five columns: last year, this year, the change in rupees and the change in percent. SUMIFS fills the first two from your dated transactions, subtraction gives the rupee change, and one division gives the percent. The only fiddly part is a category that had no spend last year, which needs its own rule.

How do I set up the dates so the years compare fairly?

Comparing a full last year with a part-finished this year makes everything look like it fell. Compare the same stretch of both years. On a Summary sheet put the window for this year in two cells:

  • H1 (this year from): =DATE(2026,4,1)
  • H2 (this year up to, not included): =DATE(2026,11,1)

Every last-year figure then uses EDATE with -12 on those same cells, so April to October 2026 is compared with April to October 2025.

What are the formulas?

Your transactions sit on a sheet called Data: dates in A, categories in B, amounts in C. Category names go down A2 onwards on the Summary sheet. Then:

  • B2 (last year): =SUMIFS(Data!$C$2:$C$500,Data!$B$2:$B$500,$A2,Data!$A$2:$A$500,">="&EDATE($H$1,-12),Data!$A$2:$A$500,"<"&EDATE($H$2,-12))
  • C2 (this year): =SUMIFS(Data!$C$2:$C$500,Data!$B$2:$B$500,$A2,Data!$A$2:$A$500,">="&$H$1,Data!$A$2:$A$500,"<"&$H$2)
  • D2 (change in rupees): =C2-B2
  • E2 (change in percent): =IF(B2=0,"New",D2/B2)

Format column E as a percentage. In Excel 365 and Google Sheets you can also generate the category list with =UNIQUE(FILTER(Data!$B$2:$B$500,Data!$B$2:$B$500<>"")), which leaves out blank rows.

What does the finished table look like?

Here is a made-up example for the April to October window:

CategoryLast year (₹)This year (₹)Change (₹)Change
Groceries96,0001,08,000+12,000+12.5%
Eating out30,00045,000+15,000+50%
Rent1,26,0001,36,500+10,500+8.3%
Pet care09,000+9,000New
Travel40,00022,000-18,000-45%
Total2,92,0003,20,500+28,500+9.8%

Check the arithmetic: the five rupee changes add to 28,500, and 28,500 divided by 2,92,000 is about 9.8 percent. The total row should be built from =SUM of the columns above and its percent from its own totals, not by averaging the percentages.

How do I handle a category that is new this year?

Dividing by zero produces an error, so the IF in column E prints "New" instead. That is honest: a rise from nothing is not a percentage. Pet care above rose by ₹9,000, which belongs in the rupee column. Do the reverse check too: a category with spend last year and none this year correctly shows -100 percent.

How do I sort by the biggest rise?

Select the table, choose Data, Sort, and sort by column D, largest to smallest. Sorting by the rupee change usually tells you more than sorting by percent, because a 50 percent jump on a small line matters less than 8 percent on rent. If you do sort by column E, remember that the word "New" sorts after the numbers, so your new categories may fall to the bottom unless you look for them.

Before you act on the numbers

  • Check that both windows contain the same number of months.
  • One-off items such as a wedding gift or a repair can swing a category; look at the rows behind any big movement.
  • Festival timing shifts a little each year, so a month that moved across the window's edge can create a false rise.

A quick way to read the table: scan the change column for the three largest rupee movements and write one line beside each, such as school fee rose, moved to a new flat, or one-off repair. A table with reasons next to the numbers is far more useful in April, when you set next year's plan, than numbers alone.

When assembling the Data sheet by hand becomes the slow step, SpendixAI can import statements and hold categories year after year, so a comparison like this is a report filter rather than a rebuild.

SPENDIXAI JOURNAL

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

Explore your reports