Skip to main content

Add Year Data to Your Tiller Spreadsheet

Learn how to add a "year" column to your transactions or balance history sheet.

Heather Phillips avatar
Written by Heather Phillips
Updated over a week ago

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.

Google Sheets

Microsoft Excel

For Google Sheets

  1. 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.

  2. 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,

  1. Click the cell in Row 2 of the Date column

  2. 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.

Adding the Year column

  1. Click into the first empty cell on the far right in Row 1 and type the word Year.

  2. Select the first cell in Row 2 of the Year column and paste the following formula:

    =YEAR([@Date])

  3. The year should populate for all the rows below if your Transactions sheet is set up as an Excel Table.

Did this answer your question?