All Collections
Transactions
Basics
Add a Group column to the Transactions Sheet
Add a Group column to the Transactions Sheet

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

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

It can be useful to see the category group as you're categorizing transactions in the Transactions sheet. 

Google Sheets

  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($D$1:$D)=1,"Group",iferror(vlookup($D$1:$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("Group",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))


If you get any errors at this point, make sure that your Category column is in column D in the Transactions sheet or adjust the formula where it indicates D1:D for the category column to use the correct column letter for your categories.

Note: you can add the column for Group 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. 

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 Group 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 group that corresponds to the category will appear in the group column 

=IFNA(INDEX(Categories[Group],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.

Did this answer your question?