🏆 Tag Manager - Excel

Microsoft Excel Show & Tell
1

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!).

TagManager1
TagManager11788×313 25.8 KB

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

  1. 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”.
  2. Download the Tag Manager workbook.
  3. 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:

  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.

2

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.

3

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.

4

@jpfieber will the tag manager workbook also work in google sheets as well or is it only for excel?

5

There is also a Google Sheets version!

6

Excel does indeed have a “TEXTSPLIT” command at this current time. Might we see a multiple comma separated tags option soon?
I just started with Tiller two days ago, so from my very limited awareness it looks like this old thread is still the most current information on Tags with Excel in Tiller. Please let me know if I’m wrong.

7

Thanks for pointing that out. I’ll take a look and see if I can get that working.

1 Like
8

Hello, I just added the Tag Manager tab to my Excel workbook. I did find that I had to copy the formula from cell G4 to cell G5 in order for Tags in a second tab of my workbook to be recognized. The default formula on the imported Tab Manager tab was " =IF( " , appears truncated. Just mentioning as this step was not mentioned in the instructions.
I have a question: Can the workbook Transaction tab support two Tag columns? Or will this create some confusion? I plan on testing but thought I would ask first before possibly creating an issue. Thank you.

9

Actually, the formula is multi-line, so if you pull down the formula bar window to make it taller, you can see the other 4 lines. Here’s the formula:

=IF(
   OR(D5="",SEARCH("Sheet Name ",D5)),
   "Enter a Sheet Name & Start/End Cells",
   "'"&D5&"'!"&E5&":"&F5
)

It’s saying if column D is blank, or it contains the place holder text, then show instructions, otherwise, build the range description from the sheet name in column D and the start and end cells in columns E & F.

10

I just added the Tags Report and Tag Manager to my excel workbook. I followed the instructions and everything is working except I am unable to create the drop-down menu for every cell in the Tags column in the Transactions sheet.

When I click above the Tags column header and select that entire column (Step #1), the data validation selection is grayed out (Step #2). If I click on one cell, I can select data validation and follow the rest of the steps. This just applies to that cell then. Even in that case, when I look at the cell I get a dropdown, but it doesn’t list all of the tags, it only shows: ‘Tag Manager’!$B$4#

I know this topic is a bit older and something may have changed. It would certainly be much easier to have the dropdown and not have to remember all the tag names. Any help is much appreciated!

11

Excel is a bit annoying that way, it doesn’t let you input the range manually, which is really unfortunate. Try selecting the second cell in your Tags column and then either drag down until you’ve gone as far as you think your transactions will go, or scroll down, hold down the shift key, and click the last cell you want to have validation and it will select all the cells between. Then open Data Validation and apply the settings and it should work on all the selected cells.

12

That worked - thanks. Like you said, unfortunate. Should be able to select the whole column.

The 2nd issue is still happening. The drop-down menu is created in all the cells, but when I click on it this is what I get. Not a list of all my tags from the tag manager sheet.

tag manager dropdown

13

Looks like you may have left out the ‘=’, it’s a formula that gets entered for that so it has to start with an ‘=’

14

I did copy directly from instructions and I did have the ‘=’ in there and got this message so that’s why I thought to try it without. I didn’t change the Tag Manager sheet at all. It does seem to point to the cell that starts the combined tags list.

tag manager error
tag manager error720×320 17.9 KB

15

Update: I got it to work. Had to go to the web version of Excel. Went to data validation and put ‘=’ in the source and then used “you can select cells directly from the grid” to point to cell B4 on the Tag Manager sheet. That basically put the same thing I was copying into that field - except for the ‘#’ sign. I added the ‘#’ sign to the end and it works as expected. Thank you for creating this and all your help. Happy New Year!

1 Like