Skip to main content
All CollectionsTransactionsBasics
Add a Type column to the Transactions Sheet
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 week ago

Google Sheets


​Note: These steps are for Google Sheets templates that have a Type column in the Categories sheets.

It can be useful to see the category type, income, expense or transfer, as you're categorizing transactions in the Transactions sheet. The Simple Business template already has this column in the Transactions sheet, but other Google Sheets templates built by Tiller do not. 

Here's how you can get it:

  1. Insert a column to the left of the Account column. Right click the column letter and choose "Insert 1 left"

  2. Paste this arrayformula 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),"")))


Microsoft Excel

  1. Insert a column to the left of the Account column. Right click the column letter and choose "Insert"

  2. Title the column Type in row 1

  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.

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

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

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@tillerhq.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?