While working in Excel, we often need to filter a large dataset based on specific tags. Filtering datasets in this manner can save a significant amount of time and work in obtaining only what we require. In this article, we will discuss two quick and simple approaches to filter tags in Excel. So, let’s start with this article and explore these approaches.
Download Practice Workbook
What Are Tags in Excel?
A tag works as simply an identifier of a cell. If we assign a tag to a cell, we can filter out that specific cell by that assigned tag. In large datasets, tags come in handy while filtering the dataset. In Excel, we can also filter by multiple tags at a time.
2 Simple Methods to Filter Tags in Excel
In this section of the article, we will learn two convenient ways to filter tags in Excel. Let’s say, we have the List of Movies with Tags as our dataset. Our goal is to filter the list of movies by different tags.
Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
1. Using Sort & Filter Option from Ribbon
Using the Sort & Filter option is one of the most effective ways to filter tags in Excel. Now let’s use the steps mentioned below to do this.
Steps:
- Firstly, select the entire dataset and then go to the Home tab from Ribbon.
- After that, click on the Sort & Filter option from the Editing group.
- Now, choose the Filter option from the drop-down.
Consequently, drop-down icons will appear on the column headers as shown in the following image.
- Following that, click on the drop-down icon beside the Tags column.
- Now, type in the Tag according to which you want to filter. In this case, we typed in Drama.
- Lastly, click OK.
Subsequently, the list of movies will be filtered and only the movies that have a Drama tag will appear on the worksheet as demonstrated in the following picture.
Read More: How to Add Tags in Excel (2 Simple Ways)
2. Applying Excel Formula
Applying an Excel formula is another smart way to filter by tags in Excel. In the following section, we will learn the detailed steps to do it.
Steps:
- Firstly, create a table as shown in the image below.
- After that, use the following formula in cell B13.
=IF(ISNUMBER(SEARCH(B$12,$D5)),$B5,"")Here, cell B12 indicates the first column title of the newly created table, cell D5 refers to the Tags for the Movie Cinderella, and cell B5 represents the first cell of the Movie column.
Formula Breakdown
- SEARCH(B$12,$D5) → The SEARCH function returns the position of a specific text from a string.
- Here, B$12 → It is the find_text argument.
- $D5 → This represents the within_text argument.
- Output → 11.
- The ISNUMBER function tells us whether a value is a number or not. Here, if we get a numerical value from the SEARCH function, that means the searched text (Tag) is present in the list of Tags.
- Now, ISNUMBER(SEARCH(B$12,$D5)) → It becomes ISNUMBER(11).
- Output → TRUE.
- Now, the IF function will return the name of the Movie, if we have a TRUE output from the ISNUMBER function. Else it will return a blank.
- Here, ISNUMBER(SEARCH(B$12,$D5)) → It is the logical_test argument.
- $B5 → This represents the [value_if_true] argument.
- “” → It indicates the [value_if_false] argument.
- Output → Cinderella.
- Then, press ENTER.
As a result, you will have the following output on your worksheet, as shown in the image below.
- Now, click on cell B13 and drag the Fill Handle up to cell G13.
Subsequently, you will see the Tags that are included in the Movie Cinderella.
- Following that, select the range of cells B13:G13 and drag the Fill Handle down up to cell G18.
Consequently, you will have all the Movies separated according to their tags as marked in the picture given below.
Read More: How to Add Tag to Properties in Excel (with Easy Steps)
How to Filter Multiple Tags in a Cell in Excel
In this section of the article, we will learn to filter multiple tags in a cell in Excel. So, let’s explore the steps to do this.
Steps:
- Firstly, follow the steps mentioned in method 1 to get the following output on your worksheet.
- After that, click on the drop-down icon beside the Tags column.
- Then, type in the name of the Tags that you want to filter in the marked box in the following image. Here, we typed in Adventure, Drama to filter the dataset based on these two tags.
- Finally, click OK.
Note: You can also type in more tags according to your requirements.
As a result, the dataset will be filtered by multiple tags, as demonstrated in the following image.
Read More: How to Use Multiple Tags in One Cell in Excel (4 Suitable Methods)
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
Conclusion
So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to filter tags in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.
