Overview
There’s a template I use on the Google side that wants to ‘own’ the Tags column on my Transaction sheet. The problem is I already use the Tags column for other things as well, so it needs to be shared. To do this, I came up with the Tag Manager sheet. It provides a way to combine tags from multiple sources into one list that you can then use as a data validation source on your Tags column, so it behaves like the Categories column. Though I haven’t seen any templates on the Excel side yet that use tags, I wanted to make an Excel version available so it’s ready when needed, and even without other templates, it acts as a source of tags so you can start using a drop-down menu on your Tags column. Note that where the Google Sheets version supports multiple comma separated tags, Excel isn’t quite as advanced in that area, so I’m currently filtering them out. Once the TEXTSPLIT command comes along, we should be able to add that feature (if anyone knows another way, let me know!).
The Tag Manager template provides four functions:
- Assists in creating drop-down menus in the Tags column on your Transaction sheet
- Sorts and filters the existing tags on your Transaction sheet
- Allows you to manually add tags to the list that don’t exist yet, but you may need in the future
- Allows you to add a range of tags from another template
All the tag sources are then combined, filtered and sorted to provide a single combined list that you can use as a source for the drop-down menus on the Transactions sheet.
It is designed to work with the Tiller Foundation Template and references the Transactions sheet.
Installation
- If you don’t already have a
Tagscolumn on your Transactions sheet, you’ll need one. Somewhere to the right of your “Amount” column, right click one of the column headers and choose “Insert”. A new column will appear to the left of the one you right-clicked and will likely have the name “Column1”. Change that name to “Tags”. - Download the Tag Manager workbook.
- Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.
At this point your new template should be functional and linked to your local workbook’s data.
Setup
In the Tag Manager sheet, you should see any existing tags you may have from the Transactions sheet in columns B and J. Column J is the raw list of existing tags, with any containing commas filtered out, then duplicates are removed, and the list is sorted. Notice column I contains three ‘sample’ tags (Tag1, Tag2, Tag3). If you look to column B, you’ll see the Combined Tags" list, a result of merging tags from both columns I and J. You should edit column I to remove the sample tags and include any that you like to have ready to use, or you can just leave it blank.
If you have a template that generates tags, and you 'd like to include them in the Combined Tags list, enter the sheet name in D5, and then the start and end to the range of tags in E5 and F5. After doing this, you should see those tags merged into your Combined Tags list.
To use the Combined Tags list on the Tags column of your Transactions sheet:
- Go to the Transactions sheet and click on the header above your Tags column to select the entire column.
- Go to the “Data” menu and select “Data Validation\Data Validation…” from around the middle of the ribbon.
- From the “Allow:” list, choose “List”
- In the “Source:” field, enter =‘Tag Manager’!$B$4#
- Switch to the “Error Alert” tab within the Data Validation window and uncheck “Show error alert after invalid data is entered” (doing this allows you to type in new tags in the Transactions sheet instead of having to go back to the Tag Manager sheet).
- Click OK
- This will add a drop-down menu to every cell in the Tags column, with the menu showing all the tags in the Combined Tags list.
- If you don’t like your Tags header having a drop-down, you can select that cell, select “Data Validation\Data Validation…”, click the “Clear All” button, and then click OK.
Usage
You can now add tags to any of your transactions by selecting them from the drop-down, which is much easier than trying to remember what you called the tags you’ve already entered! Note that you can type in a new tag that doesn’t exist in the list, and it will be added to the Combined Tags list automatically so you can use it in other transactions. You can also use multiple tags, separated by a comma, but they won’t show up in the Combined Tags list (the list gets way to messy with all the possible combinations of tags), and you’ll see a green triangle in the upper-left corner of the cell warning you that you’ve broken the data validation rule (which is fine).
Note
Only change cells that are highlighted green, changing other cells will likely break the sheet! I hope this works as well for others as it has for me! Please let me know any issues you run into so I can get them fixed. Also let me know if you have ideas on how to improve it! I’ve also released a Google Sheets version here.
Thanks for pushing at the boundaries and continuing to make the ecosystem better,