Spreadsheets

Add a month name column that fills itself in Google Sheets

Make a month name column in Google Sheets that fills itself for every new transaction with one ARRAYFORMULA and an IF guard for blank rows.

Editorial illustration for Add a month name column that fills itself in Google Sheets

Put one formula in the header cell of a new column and Google Sheets fills the month for every row, including rows you add later: ={"Month";ARRAYFORMULA(IF(A2:A="","",TEXT(A2:A,"mmm yy")))}. The IF keeps blank rows empty, and TEXT turns each date into a label such as Apr 25. Make sure nothing else is typed below it in that column.

How do I set it up?

  1. Check that column A holds real dates. Click a cell and look at Format, then Number. A left-aligned date is probably text.
  2. Pick the empty column beside your data, say E, and leave all of E2 down empty.
  3. In E1 paste ={"Month";ARRAYFORMULA(IF(A2:A="","",TEXT(A2:A,"mmm yy")))}
  4. Press Enter. E1 shows Month, and E2 down shows the label for each date.

The curly brackets build a two-part array. The first part is the word Month, which becomes the header. The semicolon stacks the second part under it, which is the ARRAYFORMULA result for every row from 2 to the end of the sheet. Since the range A2:A has no end row, new rows are covered automatically.

What does the IF guard do?

Without IF(A2:A="","",...), the formula would run on the empty rows below your data. TEXT of a blank cell is a date in December 1899, so you would get hundreds of rows of Dec 99. The guard returns an empty string when the date cell is empty, so the column looks blank until you add data.

Can I catch text dates as well?

Yes. If a pasted date is text, TEXT returns the text unchanged and you may not notice. Add a check: ={"Month";ARRAYFORMULA(IF(A2:A="","",IF(ISNUMBER(A2:A),TEXT(A2:A,"mmm yy"),"Check date")))}. Any row that says Check date needs its date fixed.

Which label format should I use?

Format codeResult for 14 Apr 2025Use it for
"mmm"AprOne-year sheets
"mmm yy"Apr 25Readable labels across years
"mmmm yyyy"April 2025Headings
"yyyy-mm"2025-04Labels that sort in date order

For an Indian financial year, a month number that starts at April is handy. ={"FY month";ARRAYFORMULA(IF(A2:A="","",MOD(MONTH(A2:A)-4,12)+1))} returns 1 for April and 12 for March. Check: for a March date, MONTH is 3, 3 minus 4 is minus 1, and MOD(-1,12) is 11, so the result is 12.

How do I use the month column in totals?

Type a label such as Apr 25 in G2 and use =SUMIFS($C$2:$C$500,$E$2:$E$500,G2). Or build a monthly summary without helper formulas for each cell: =QUERY(A1:E,"select E, sum(C) where E is not null group by E label sum(C) 'Total'",1). Since the labels are text, the order may follow the order they first appear, so use the yyyy-mm format, or order by a separate date, if you need a strict calendar sequence.

What about sorting the sheet?

Sorting rows while a formula column sits in the selection can be messy. Select only the columns that hold typed data, such as A to D, before you sort, and leave the formula column to recalculate beside them. Sorting with the header formula untouched keeps the array intact.

A note on locale

If your sheet is set to a locale that uses semicolons between formula arguments, replace the commas between arguments with semicolons. Check under File, Settings, Locale. The format codes inside TEXT stay the same in English-language setups.

For a quick monthly total without writing SUMIFS cell by cell, you can use a pivot table: choose Insert, then Pivot table, add the month label to Rows and the amount to Values as SUM. Because the label column fills itself, the pivot picks up new rows when you refresh its range. Make the range open ended, for example A1:E, so it grows with the data.

When a sheet stops being enough

If you are importing statement after statement and tidying columns each time, SpendixAI takes bank and card statements as PDF, CSV or Excel files, and lets you check the rows before they are saved, with dashboards and reports organised by period.

Checklist

  • Column empty below the formula.
  • Dates are real, not text.
  • One new row added to test that the month fills in.
SPENDIXAI JOURNAL

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

Explore your reports