Skip to main content

Add a Type column to the Transactions Sheet

Learn how you can display the category type alongside your other data in the Transactions sheet.

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

Google Sheets

It can be useful to see the Category Type — Income, Expense, or Transfer — as you're categorizing transactions in the Transactions sheet.

To add the Type column to your Transactions sheet:

  1. On the Transactions sheet, insert a column to the left of the Account column by right-clicking the column letter then choose Insert 1 Column Left

  2. Paste the array formula below into the new column header (row 1) and press enter:

    =arrayformula(if(row(D1:D)=1,"Type",iferror(vlookup(D1:D,{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Type",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Type",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))

  3. As you categorize your Transactions, the Type will automatically fill based on the Category's type on the Categories sheet.


Microsoft Excel

  1. Insert a column to the left of the Account column by right-clicking the column letter then choose Insert

  2. Enter the title Type in Row 1 of your new column

  3. Paste the below formula into Row 2 of that new column. It should automatically populate into all subsequent rows in that column if your Transactions sheet is set up as an Excel Table - see important notes below.

    =IFNA(INDEX(Categories[Type],MATCH([@Category],Categories[Category],0)),"")

  4. Once transactions are categorized the type that corresponds to the category will appear in the Type column

Important notes when using this formula for Excel:

  • These steps will only work if your Transactions sheet is set up as an Excel Table. In some older versions of our Excel template or Transactions sheets created by the Tiller Money Feeds add-in (if you started in a blank sheet) the Transactions sheet is not formatted as an Excel Table. Reference this guide for more information and how to verify whether yours is already set up as an Excel table.

  • When you fill transactions we have noticed that the formula does not persist in row 2, but does persist in other new rows. You may need to copy/paste the formula back into Row 2 after a fill.

  • This formula for Excel is based on this Community topic. If you know a more effective way to display the group in an Excel Table please write to us at support@tiller.com so we can update this content.
    ​
    ​Note: you can add the column for Type wherever you want it to appear, but we don't recommend to the right or left of the Category column because it will add the data validation (dropdown menu) formatting and it's meant to be a read-only column.
    ​
    ​

Did this answer your question?