Since the launch of Quicken in the 1980’s, the world has been categorizing financial transactions. Categories are awesome, and a big part of Tiller is making categories flexible and easy, but sometimes you want more than categories. Sometimes you want tags.
Tags can be helpful when you want to apply multiple classifications to a single transaction. Let’s say you enjoy mountain biking and you’re buying some new accessories for your mountain bike in anticipation of an upcoming ride. Mountain bike expenses generally fall into your “Gear” category, but you’re interested in tracking how much money you’re putting into each bike frame, and how much you’re spending for each event. You could categorize that new shock system under “Gear” while also tagging it as “Tall Boy” and “Grand Fondo.” This way you can see how much you’ve invested in your Tall Boy frame, and how much you’re spending to prepare for that upcoming Grand Fondo.
While Tiller doesn’t come with a built-in tagging capability, one of our customers, Jon Orlin, built a handy sheet to make it possible in any of our existing templates. In this post he shares the step-by-step instructions for how he built it.
For ease of use, and for those of you who are less spreadsheet savvy and just want the functionality in your sheet, we’re including the short version of how to get this feature. If you want to know how the magic works read through this entire post.
Quick and Easy Tag Setup
1. Open the Tiller spreadsheet where you’d like to use tagging.
2. Right click column E (the note column in most templates) and choose “insert 1 right.”
3. Tag a few transactions. You can go back and tag more later, but this is to ensure the Tags sheet works properly when you get it copied over.
Note: You can use multiple tags for a single transaction if you separate them with commas. If you use multiple tags for a single transaction do not add a space after the comma. The subsequent list is case sensitive. Be consistent so you don’t end up with duplicate tags.
4. Click here to open the sample Tags sheet.
5. Right click the tab at the bottom (Tags) then choose “Copy to” to copy this Tags sheet into your Tiller spreadsheet where you want to use tagging.
You can find your Tiller spreadsheet by clicking “recent” near the top or copy and paste the spreadsheet URL into the bottom of the window that pops up.
6. Navigate back to your Tiller spreadsheet and rename the Tags sheet (tab at the bottom) from “Copy of Tags” to “Tags.”
From here you should see the list of tags you’ve used so far populate into a list starting with cell A2. You can then select a tag from the dropdown list in cell F1 to review all transactions associated with that tag and get a summary of the total amounts and counts of the tag used organized by category.
If you have any issues getting this set up in your Tiller spreadsheet simply reach out to support via the chat window in the lower right corner of this page and we’ll help you get it set up. If you use this feature, and would like to see more capability, reporting or integration with tagging, ping us via chat (or email firstname.lastname@example.org) and let us know.
Understanding the Magic of the Tags Sheet
If you want to understand how this sheet is set up and build it yourself continue reading. Otherwise, you’re all set!
We’ll get started by opening up the Tiller spreadsheet where you’d like to utilize the tagging capability. (Basically, we’re repeating steps 1 – 3 of the quick setup method above.)
1. Create a new column called Tags in your Transactions sheet. You can add it anywhere you like, but in this example we put it in column F.
Note: You can easily insert new columns by right clicking any column and then choosing “insert 1 left or right.” We do not recommend right clicking the Category column to create new columns left or right of this column because it will duplicate the category logic, which we don’t want in this case. If you don’t put it in column F be sure to make note of the column letter.
2. Add a few tags to some transactions in this new Tags column to your Transactions sheet so you will have some data to work with. You can use multiple tags in a single transaction row as long as they are separated by a comma. We don’t recommend adding a space between tags separated by a comma.
3. Create a new sheet called Tags using the plus sign in the lower left corner of your sheet. In the first column, enter the heading “Tags” in A1.
4. First, get the letter of the column in your Transactions sheet that is storing the Tags. In our example, remember, we’re using column F.
5. To get a list of all the tag values, use the UNIQUE function. Place this formula, in A2 in the Tags sheet:
F2:F after Transactions! in this function is pulling in the Tag column in the Transactions sheet. F2:F means use the range from F2 to the bottom of the F column. We skip F1 because it’s the header column.
6. To ensure that we can use multiple tags for a single transaction we’ll use the JOIN function to create a list of all tags, separated by commas, in a single cell. The first part of the JOIN function is the delimiter (adds the comma between each tag). Now every tag from the earlier result will be listed in its own cell separated by a comma.
7. Now we want to list the tags vertically instead of horizontally so we add the TRANSPOSE function to this formula.
8. We need to make sure that each tag value appears only once in this new list, so we add another UNIQUE function:
=UNIQUE(TRANSPOSE(SPLIT(JOIN(“,”,UNIQUE(Transactions!F2:F)),”,”) ) )
9. Lastly, to sort the tags alphabetically, use the SORT function. Here is the final formula you can copy into cell A2.
=SORT(UNIQUE(TRANSPOSE(SPLIT(JOIN(“,”,UNIQUE(Transactions!F2:F)),”,”) ) ) )
Get useful information about your tagged transactions
Now you have a dynamic list of all the comma separated Tags from your Transaction sheet in column A of the Tags sheet. This list will auto-update as you add new tags to your Transaction sheet.
Let’s add a few more columns to the Tags sheet to make it more useful by displaying the transaction amount sum and count for each tag.
1. First let’s add some column headings. In row 1 give column B the header “Sum” and column C the header “Count.”
2. Next we’ll calculate the Sums in cell B2, using this formula:
=ARRAYFORMULA(IF(A2:A<>”” , SUMIF(Transactions!F2:F,”*”&A2:A&”*”,Transactions!D2:D), “”) )
The formula uses the functions ARRAYFORMULA, IF and SUMIF. These functions are beyond the scope of this tutorial, but essentially what’s happening is the transaction amounts in the Transaction sheet are summed if the Tag in each row of the Tag sheet is found in the Tag column on the Transactions sheet. By using the function ARRAYFORMULA, we can list the formula just once and have it fill down for all the Tags. By using the IF function, we can ignore rows where no tag is listed.
In this example:
- F2:F points to the Tags column in the Transactions sheet.
- A2:A refers to the Tags listed in the first column in the Tags sheet. There are *’s before and after the range, so the tag will do a match even if there are multiple tags in the Tags column.
- D2:D refers to the Amount column in the Transactions sheet which will be summed.
Note: You might need to adjust these column letters to match your Transactions sheet.
3. Finally, we’ll calculate the number of transactions that use a tag using the function COUNTIF in this formula.
=ARRAYFORMULA(IF(A2:A<>”” , COUNTIF(Transactions!F2:F,”*”&A2:A&”*”), “”) )
In this example, F2:F refers to the Tags column in the Transactions sheet. Transactions are only counted if they match the Tag in column A in the Tags sheet. Again, the ARRAYFORMULA function calculates all the results for every tag with just one formula cell. The IF function only shows results where the tag is not blank.
Show transaction details for a selected tag
Another handy bit of information you might want in this Tags sheet are the specific transactions associated with a selected tag. We can build this out with a bit of data validation and a few more functions.
1. In cell E1, add the header “Trxn by tag:”
2. Next we’ll create a dropdown to select which tag to filter on in cell F1. To make F1 into a dropdown list of all the tags, select F1, then open the “Data” menu at the top of your sheet and choose “Data validation.” Under Criteria set “List from a range” to A2:A and “Save”.
3. Next we’ll make sure that this cell doesn’t show blank or empty tags. This will remind you to select a Tag in F1 if no tag is selected. If F1 has a tag, it will also put it into E2. Add this formula to cell E2:
=IF(F1=””,”Choose tag in F1″,F1)
You’ll see this update to the selected tag when you choose a tag from the dropdown menu in F1.
4. Finally, we’ll use the QUERY function to generate our list of transactions by tag. Select cell E5 and enter the this formula:
=QUERY(Transactions!A:F,”SELECT A,B,C,D WHERE F CONTAINS ‘”&E2&”‘ ORDER BY A ASC”)
Here’s what the QUERY is doing in this circumstance:
- Transactions!A:F refers to the columns you wish to include in your query.
- SELECT A,B,C,D will show the information from those columns in the Transaction sheet.
- WHERE F CONTAINS ‘“&E2&”’ “ says only select rows where column F (the Tag column in the Transactions sheet in our example) contains the Tag value in E2.
- ORDER BY A ASC orders the list by column A (the Date column in our example) in ascending order. To use descending order, use DESC instead of ASC.
Note: You might need to change the letters A,B,C,D to match your Transactions sheet.
Voila! Now you have a Tags sheet that allows you to get a total amount of money spent per tag with the specific transactions that are using that Tag. Feel free to send us feedback or questions if you have any issues getting it set up. Once again, you can easily get the functionality in your sheet by referencing this help article.
Tiller Customer, Jon Orlin
Jon Orlin has been using financial software since the 1980’s. He’s a Peabody and Emmy Award winning video producer who has worked at CNN, Yahoo, and TechCrunch. Jon is now a consultant and enjoys creating Google Sheets on the side. Follow him on Twitter at @jonorlin.