All Collections
Transactions
Basics
How to Manually Add Month Data
How to Manually Add Month Data

Learn how to add historical month data for transactions your manually adding.

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

The Transactions sheet in all Tiller spreadsheets have the "Month" column as a default that is populated by the automated data feeds. Usually you will need to scroll to the right in your Transactions sheet to see it.

You might need to manually add month data if you're preparing historical data that you want to manually add to your Transactions sheet, or inputting transactions for accounts Tiller can't automate.

For Google Sheets

For Microsoft Excel

Adding month data in Google Sheets

You can use the below arrayformula to have it automatically fill in the month data for the data set in a "Month Array" column. 

  1. Click into the first empty cell in row 1 to the far right or insert a column to the left of the existing Month column.

  2. Paste this formula into the header row (row 1)

    1. This assumes the "Date" is in column B (the default in the Foundation template). Change the formula to match the location of the date data in your prep sheet. 

    2. =arrayformula(if(row(B1:B)=1,"Month Array",if(B1:B<>"",date(year(B1:B),month(B1:B),1),"")))

  3. Copy the month dates (right click > Copy or CTRL+C/CMD+C) from this new column for the transactions where the month data is missing. Usually this is for historical transactions you've manually added.

  4. Right click into the first cell in the Month column that corresponds to the transaction where you need to add the month data.

  5. Choose Paste Special > Values Only to paste the Month data into the Month column for the transactions where the month data is missing.

  6. Delete the helper column you added out to the right. Tiller automatically populates the month data for transactions it fills in so there is no need for the duplicate month data column.

Adding month data in 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 Month Data to your Transactions sheet

  1. Click into the first far right empty cell in row 2. This is usually to the right of the Date Added column, but this could vary depending on whether you've added your own custom columns. The column header should be blank and the column will not be in the Excel Table boundary yet.

  2. Click into the formula bar at the top

  3. Paste in this formula:

    1. =DATE(YEAR([@Date]), MONTH([@Date]), 1)

  4. Copy the month dates from this new column for the transactions where the month data is missing. Usually this is for historical transactions you've manually added.

  5. Right click into the first cell in the Month column that corresponds to the transaction where you need to add the month data.

  6. Choose Paste Special > Values to paste the Month data into the Month column for the transactions where the month data is missing.

  7. Delete the helper column you added out to the right. Tiller automatically populates the month data for transactions it fills in so there is no need for the duplicate month data column.

Did this answer your question?