Skip to main content
All CollectionsTransactionsBasics
Add Year Data to Your Tiller Spreadsheet
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 year 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. Insert a new column. To insert a new column right click any column header letter and choose to insert 1 left or right. We recommend adding it to the left or right of the month column.

  2. Paste the below formula into the header row of the new column. 

  3. If you find that the values are not formatted as a year (e.g. 2019) select the entire column by clicking the column letter and change the format to plain text from the Format menu of the Google Sheet.

=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 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 into the Date cell in row 2

  2. Review the very top of your Excel workbook and look for either "Table" or "Table Design" ribbon that will appear to the right of the View ribbon.

If your Transactions sheet is not set up as an Excel Table click here to get the steps on how to do that.

Adding the Year column

  1. Click into the first far right empty cell in row 1 and type the word "Year".

  2. Select the first cell in row 2 in the year column and then click into the formula bar at the top

  3. Paste in this formula:

    1. =YEAR([@Date])

  4. 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?