The year column is handy when you want to build a pivot table and filter on year. A Year column is not included in your Transactions sheet by default. Use the steps below to add a year column to your Transactions sheet in Google Sheets or Microsoft Excel.
For Google Sheets
On your Transactions sheet, right-click the column header letter to the left or right of the Month column and choose Insert 1 Column to the Left or Right. We recommend keep it next to the Month column.
Paste the below formula into the header row of the new column.ย โ
=arrayformula(if(isblank(B:B), "", if(row(B:B)=1,"Year",year(B:B))))
In the above formula B represents the Date column. If the date is in any column other than B update this formula to use the correct column letter before pasting it into the header row.ย
If the values do not appear formatted as a year (e.g. 2025), select the entire column by clicking the column letter then click Format > Number > Plain text.
If you get a "circular dependency" error, it's because you put the formula in column B and you need to change the formula to use the new column letter for the Date column.
As new transactions or balance history entries are added, the year will automatically populate into this column.ย
For Microsoft Excel
The Tiller Foundation Template Transactions sheet is set up as an Excel Table so the formula used below automatically fills to all empty cells in the table boundary in the column where you add it in row 2. These steps will only work if your Transactions sheet is set up as an Excel Table.
To verify that your Transactions sheet is set up as an Excel Table,
Click the cell in Row 2 of the Date column
Review the very top of your Excel workbook and look for either Table or Table Design in the menu options
If your Transactions sheet is not set up as an Excel Table, follow these steps before continuing.