Tag Manager - Excel

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:

  1. Assists in creating drop-down menus in the Tags column on your Transaction sheet
  2. Sorts and filters the existing tags on your Transaction sheet
  3. Allows you to manually add tags to the list that don’t exist yet, but you may need in the future
  4. 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

Download the Tag Manager sheet.

Preparation

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”.

Insert the Tag Manager Worksheet

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.

  1. Open your linked Tiller Feeds workbook in Excel (the file that already contains your transactions).
  2. Open the workbook you just downloaded in Excel (the one containing the Tag Manager template).
  3. Right-click on the Tag Manager sheet in the workbook you just downloaded and click “Move or Copy”.
  4. In the “To book” dropdown, select your linked Tiller Feeds workbook.
  5. Leave the “Create a copy” checkbox unchecked.
  6. In the “Before sheet” dropdown, select “(move to end)”, then click OK. If you receive an alert about a name conflict, just click Yes.
  7. The new worksheet should appear in your Tiller Feeds workbook with the name “Tag Manager”.
  8. Navigate to “Data / Edit Links…” in the menu.
  9. Select the link to the downloaded workbook in the box.
  10. Click the “Change Source…” button.
  11. 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.

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:

  1. Go to the Transactions sheet and click on the header above your Tags column to select the entire column.
  2. Go to the “Data” menu and select “Data Validation\Data Validation…” from around the middle of the ribbon.
  3. From the “Allow:” list, choose “List”
  4. In the “Source:” field, enter =‘Tag Manager’!$B$4#
  5. 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).
  6. Click OK
  7. 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.
  8. 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.

This is cool, @jpfieber.

Personally, I had managed my tags in more of a top-down architecture— with a hardcoded Tags (definition) sheet— but I think your approach of gathering tags as they exist in multiple sheets and consolidating them is pretty clever. It’s easy to use.

Only suggestion— and feel free to take it or leave it… I really like the way you implemented the start/end cell lookups in E4:F4. I tried adding a Tags column to my Categories sheet but then needed to manhandle those formulas in row 4 to replicate them for my Categories sheet. Would it make sense to include the sheet name (from column D) and either assume the column name is “Tags” or add another column to set the header name, then wrap it all in an INDIRECT() function?

:trophy: Thanks for pushing at the boundaries and continuing to make the ecosystem better, @jpfieber. We are excited to offer you a $100 gift certificate as part of our 2022 Microsoft Excel Builders Challenge.

Seems the IF statements I have in column G aren’t working, I’ll have to play with that. If you drag the fill handle on G4 down to fill that formula to the lower cells, I think you should be able to just put “Categories” in D5, “D2” (or whatever column your Tags are in), and D1000 or whatever in F5. It should then show the range in G5, and column K should fill with any tags it finds. I never found a reason to use tags on my Categories sheet, but if that’s a common thing, I could build that in as a default, as I did with Transactions.