Spreadsheets

A rolling 12-month table in Google Sheets that shifts every month

Build a rolling 12-month table in Google Sheets with TODAY, EDATE and SUMIFS so the months slide forward each month, plus a warning on TODAY recalculation.

Editorial illustration for A rolling 12-month table in Google Sheets that shifts every month

In Google Sheets, generate the month headers from TODAY and EDATE, then put one SUMIFS under each header. The first header is the first day of the month twelve months back, every other header is EDATE of the one before it, and the whole table moves forward on its own when a new month begins.

How do I build the month headers?

  1. Keep your expenses on a tab called Data, with dates in column A, categories in B and amounts in C, from row 2 to row 1000.
  2. On a new tab called Rolling, type this in B1: =EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-12)
  3. In C1 type =EDATE(B1,1) and copy it across to M1.
  4. Select B1:M1 and apply a custom date format that shows month and year, such as mmm yy.

If you want a single formula instead, put this in B1 and it fills B1:M1 by itself: =ARRAYFORMULA(DATE(YEAR(TODAY()),MONTH(TODAY())-12+SEQUENCE(1,12,0,1),1)). DATE copes with month numbers below 1, so the year changes over correctly.

What goes under each header?

Write the category names down column A, from A2. In B2 enter:

  • =SUMIFS(Data!$C$2:$C$1000,Data!$B$2:$B$1000,$A2,Data!$A$2:$A$1000,">="&B$1,Data!$A$2:$A$1000,"<"&EDATE(B$1,1))

Copy it across to M2 and down through your categories. The dollar sign before A keeps the category column fixed while you copy sideways, and the one before the 1 in B$1 keeps the header row fixed while you copy down. In column N, add =SUM(B2:M2) for a row total, and =SUM(B2:B10) under each month for a month total.

CellWhat it holdsExample if today is in November 2026
B1First month of the window1 Nov 2025
M1Last month of the window1 Oct 2026
A2A category name typed by youGroceries
N2Twelve-month total for that row=SUM(B2:M2)

With made-up groceries figures of 9,800, 10,200, 11,500, 9,700, 10,100, 10,400, 9,900, 10,600, 10,300, 11,800, 10,500 and 12,000, the row total is ₹1,26,800 and the monthly average is about ₹10,567.

What happens on 1 December?

TODAY now returns a December date, so B1 becomes 1 December 2025 and M1 becomes 1 November 2026. The November 2025 column disappears from the table without any warning, and the new month is added at the right. That is the feature, but it means the table never holds history. If you want to keep older months, copy the block at each month end and use Edit, Paste special, Values only into an Archive tab.

Why does TODAY need a warning?

  • TODAY is volatile. Sheets recalculates it when you open the file and when something changes, and the Calculation setting under File, Settings can also refresh it on a timer.
  • The time zone under File, Settings decides when the day changes. A sheet set to a different zone can roll over to the new month hours before or after you do.
  • Editing an old entry changes the table at once. That is helpful for corrections and risky if you showed someone last week's figures.

Why do my headers show five-digit numbers?

Dates are stored as serial numbers, and a cell that shows something like 45962 is just missing its date format. Select the header row and apply a date format from the Format, Number menu.

Why do all my totals show zero?

Usually the dates in Data are text rather than real dates, often after a pasted statement. Type =ISNUMBER(Data!A2) in a spare cell: TRUE means a real date, FALSE means text. Retyping one date, or using Data, Split text to columns on the date column, often converts a whole column at once.

Before you rely on the table

  • Compare one month's column with a hand total from your Data tab.
  • Make sure every category used in Data appears in column A; add a Total row for the whole month and a check line that compares it with a plain SUMIFS over all categories.
  • Format the cells as rupees so lakhs read properly.

If you find yourself pasting a new statement into Data every month, SpendixAI can import statements as PDF, CSV or Excel files and hold the categorised transactions, leaving the sheet for the experiments you enjoy.

SPENDIXAI JOURNAL

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

Explore your reports