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.
Download the Tag Manager sheet.
If you don’t already have a “Tags” column 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”.
The instructions are for the desktop version of Excel, the web version is not recommended for the installation but does work once the Tag Manager sheet is installed.
- Open your linked Tiller Feeds workbook in Excel (the file that already contains your transactions).
- Open the workbook you just downloaded in Excel (the one containing the Tag Manager template).
- Right-click on the Tag Manager sheet in the workbook you just downloaded and click “Move or Copy”.
- In the “To book” dropdown, select your linked Tiller Feeds workbook.
- Leave the “Create a copy” checkbox unchecked.
- In the “Before sheet” dropdown, select “(move to end)”, then click OK. If you receive an alert about a name conflict, just click Yes.
- The new worksheet should appear in your Tiller Feeds workbook with the name “Tag Manager”.
- Navigate to “Data / Edit Links…” in the menu.
- Select the link to the downloaded workbook in the box.
- Click the “Change Source…” button.
- Navigate to the active workbook (i.e. select your personal spreadsheet).
At this point your new template should be functional and linked to your local workbook’s data.
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.
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).
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.