I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question.

Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the articles and books I'm reading. Say I'm reading "I, Claudius". I want to give it these tags: history, fiction, biography, disability, politics, drama. Then, if I want to display all the articles/books tagged with "politics", I can maybe search/display/pivot with that tag.

Maybe preferably, the tags should all be in one cell with each word separated by a comma. If each word were in a cell of its own, it would make the table really messy, I think.

I am open to other labelling solutions too.


  • I've never seen a tag solution in excel, but that's not saying there isn't one. Maybe check out evernote Mar 27, 2012 at 12:59

5 Answers 5


As far as I know there are no built-in features that can parse and summarize comma-separated tags in Excel. You can, of course, create your own solution with worksheet functions and a little VBA. Here's a quick solution for doing this.

Step 1: Press Alt+F11 to open the VBA editor pane in Excel. Insert a new module and paste in this code for a custom function.

Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate.  sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer

On Error GoTo EH
rra = rr
out = ""
i = 1

Do While i <= UBound(rra, 1)
    If rra(i, 1) <> False Then
        out = out & rra(i, 1) & sep
    End If
    i = i + 1
out = Left(out, Len(out) - Len(sep))
Exit Function

rra = rr.Value
Resume Next

End Function

This function will allow you to create comma-separated lists to summarize the tag data you have.

Step 2: In a worksheet, enter in a cell (H2 in the example below) the tag you want to search for. In the cell to the right, enter the following formula by pressing Ctrl+Shift+Enter.

=IFERROR(CCARRAY(IF(NOT(ISERROR(FIND(H2,$B$2:$B$6))),$A$2:$A$6),", "),"No matches found.")

By pressing Ctrl+Shift+Enter, you are entering the formula as an array formula. It will appear surrounded by {...} in the formula bar. Note that in the formula $B$2:$B$6 is the range that holds all the tags for the items listed in $A$2:$A$6.

enter image description here

If you don't mind your matches being listed in a column instead of in a list in one cell, you can return matches for tags using only worksheet functions.

Where your titles are in Column A, the tags are in Column B, and the tag you are searching for is in H2, you can use the following array formula in I2 and fill down as far as you need:


enter image description here

The formula works by first forming an array of numbers based on whether the tags in each row contains the search term. If a match is found, the row number is stored in the array. If it is not found, 2000000 is stored in the array. Next, the SMALL(<array>,ROW()-1) part of the formula returns the ROW()-1th smallest value from the array. Next, this value is passed as an index argument to the INDEX() function, where the value at that index in the array of titles is returned. If a number greater than the number of rows in the title array is passed to INDEX() as an argument, an error is returned. Since 2000000 is passed as the argument when no matches are found, an error is returned. The IFERROR() function then returns "" in this case.

It is important to grasp how ROW() is being used in this formula. If you want to display your list of results starting in a different row, you will need to adjust the second argument of the SMALL() function so that it returns the first smallest value from the array. E.g., if your list of results starts in Row 1 instead of Row 2, you would use SMALL(...,ROW()) instead of SMALL(...,ROW()-1).

Also, if your list of titles and tags does not start in Row 1, you will need to adjust the formula as well. The second argument of the IF() function must be adjusted so that a match in the first row of your data returns 1. E.g., if your list of titles starts in Row 2 instead of Row 1, you will need the formula to include IF(...,ROW($A$2:$A$7)-1,...) instead of IF(...,ROW($A$1:$A$6),...).

  • Wow, this works perfectly! I prefer it on Google Spreadsheet, though. But it uses JavaScript. I'll try and do something similar there though I am a complete and utter beginner with the language. Just two more questions: What does it mean to "summarize" data? What does your code in step 1 do? I'm asking this question in particular because I think I might have to do the same with JavaScript in Google Spreadsheet. Thanks so much for your solution!
    – nusantara
    Mar 29, 2012 at 16:07
  • Great, I'm glad it's of use. By "summarize", I just mean creating a comma-separated list of matching titles. The VBA is required for this purpose. With built-in Excel functions, it's fairly straightforward to create an array formula that can pick out matching tags. However, it's not possible to return these results in one cell without VBA, because the CONCATENATE function in Excel cannot take an array as an argument (it only reads the first item from the array if you try it). The VBA function is made to create comma-separated lists from an array of values.
    – Excellll
    Mar 29, 2012 at 16:38
  • After writing the above response, I realized that there is a way to do something similar without VBA. Instead of returning the list of matching titles in one cell, a column of matching titles can be returned. I can edit my post to include this method as well if you're interested.
    – Excellll
    Mar 29, 2012 at 16:40
  • It's possible without VBA? That would be better, I think. If it's not too inconvenient, yes, it would be awesome if you could include the method you mentioned. It would be so helpful to anyone searching for this topic! Thanks for explaining "summarization". I tried concatenation too but, yeah, couldn't use it the way I wanted it to. Now I know it can't behave like an argument.
    – nusantara
    Mar 31, 2012 at 10:04
  • Yes, it's possible. I'll try to post the solution here in a few minutes. Sorry for the delay!
    – Excellll
    Apr 5, 2012 at 13:57

Another idea:

Use the inbuilt filter functionality. Filter by the Tags column, then you can search for rows containing a given tag.

A row with the tag list: comedy,horror,romance

would show up when searching for any of those three tags.


While doing this programmatically works well in some cases I found a manual approach worked for me. Using columns for tags you can easily tag a row item by entering a 0 into the column. You then have one row and enter 1s in each of the tag columns for that row (you can colour this row). Then when you sort by one of the tags the 1s (blue) row acts as a separator. Between your filtered results (0s) and everything else (_).

This has a few advantages. 1. You don't have to type your tag each time. 2. You can easily cross reference to check if you have duplicate or similar items that can be reduced to one.


My non-VBA approach is to list out tags in one column (let's say column H), separating items with commas. From there, I use a combination of "Text to Columns" and "Remove Duplicates" to get my list of tags. I copy past this into the header row of my main sheet (in this case, beginning in column L. Then, in each cell below the individual tag headers, enter the following:


This gives the best of both worlds - the tag column (H) is easy for people to read; the individual tag columns (with their 0s and 1s) are easy for the computer to read. It's dynamic if you remain within the given set of tags; if not, you need to add your new tag to the header row, then copy your formulas over.

From there, Pivot-Tables are your friend.


It's not clear to me exactly how you want to use the tags. But in Google Sheets, you can use the SPLIT() function to split a string of tags (delimited by spaces, commas, or whatever you want) into several other cells, and I'm sure even Excel has a function that can search a cell for a tag.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .