Skip to Content

How To Filter Data With Wildcard In Google Sheets (2 Easy Ways)

In this beginner-friendly guide, we will explore a powerful technique that can make your data filtering woes a thing of the past. Say hello to the magic of filtering data with wildcards in Google Sheets.

Whether you’re a spreadsheet guru or this is your first attempt to filter data with wildcard in Google Sheets, rest assured that today’s guide will show you the step-by-step process of filtering your data using wildcard. 

To better understand what wildcard filtering entails, we have put together a use case/scenario section that conveys the message better. Check it out for more insight. 

Scenario/Use Case

Suppose you’re managing an e-commerce business and have a spreadsheet containing a list of all your products. The list includes product details such as names, descriptions, and prices. Your marketing team asks for a quick report of all products related to a specific keyword: “summer.”

Without wildcards, finding these products can be a daunting task. You could use the basic filter tool, but it may not catch all the relevant entries. Here’s where wildcards come to the rescue.

In the subsequent section, we will delve into the practical aspect of this guide, showing you the steps to filter data with wildcard in Google Sheets. 

Filter Data With Wildcard In Google Sheets – Manual Method

Now that we’ve covered the basics of using wildcards to filter data in Google Sheets and shared a use case/scenario of when you might need this, let’s roll up our sleeves and get hands-on with it.

To make things super clear, we’ll demonstrate how simple it is to filter data with wildcards in Google Sheets using a practice example.

We’ll work with the following sample data for this hands-on exercise. 

With our sample data now figured out, let’s quickly show you how to filter data with wildcard in Google Sheets. 

Copy the Example Google Sheets

To get the most out of this tutorial on filtering data with wildcards in Google Sheets, feel free to follow along with our example sheet.

Click here to access the example sheet

If you already have your own sample sheet with data that you’d like to filter, feel free to follow along. Just remember to adapt the criteria and columns to your specific needs.

Step 1: Add Filter To The Column You Intend To Filter Using Wildcard

Let’s start by applying a filter to the column in our spreadsheet where we want to search using a wildcard. This example will focus on our “Product Name” column. Follow these simple steps

Choose the entire column where you want to apply the filter. In our case, it’s the “Product Name” column in column A. 

With that done, go to the “Data” menu at the top of your Google Sheets. From the Data menu, click on the option labeled “Filter views.” 

In the submenu that appears when you select “Filter views,” click “Create new filter view.” This action sets the stage for applying your custom filter to the selected column.

If you followed the steps exactly as we detailed above, Google Sheets should automatically add the filter icon to the select column. 

Here, check out what ours looks like: 

Step 2: Filter Data With Wildcard

Begin by locating the filter icon. It’s usually a small funnel-shaped icon located near the column header. In our case, it’s next to the “Product Name” column. Click on this icon to open the filtering options.

Once you click the filter icon, a menu with various filtering options will appear. From this menu, select “Filter by condition.” This choice will allow you to create a specific filter condition based on your wildcard search.

Once you choose the “Filter by condition” option, you’ll notice a small box below it that currently says “None.” To proceed, click on the dropdown button next to this box, scroll all the way down, and select the “Text contains” option.

Step 3: Enter Wildcard Search Term

Now that we’ve picked the “Text contains” option, it’s time to tell Google Sheets what we want. In this case, we want to find products with “Summer” in their names. Here’s how to do it.

Look for the box where you can type in your search term. In our example, type the word “Summer” (including the asterisks, like this: *Summer*) into this box.

Once we type the search term, we only need to hit the OK button. 

If you’ve followed the steps we demonstrated, your spreadsheet should now magically display only the products that contain your search term.

Let’s see what our spreadsheet looks like after applying the wildcard filter for the word “Summer.”

As you can see in the screenshot above, we’ve accomplished our goal. We’ve used the wildcard filter to ensure our spreadsheet only shows products with “summer” in their name. 

This is one of the ways you can filter data with wildcards in Google Sheets. 

Filter Data With Wildcard In Google Sheets Using the Filter Function

Filtering data with wildcards in Google Sheets is not limited to manual filtering. You can also use the FILTER function to perform this task efficiently. In this section, we’ll guide you through the steps to filter data with wildcard in Google Sheets using the FILTER function. 

We will use the sample data in the previous example to demonstrate this method. 

Step 1: Choose A Blank Cell

Before we get started, let’s pick an empty cell where our filtered data will appear. Think of it as the place where the magic happens. In our example, we’ll choose cell F2.

Step 2: Write the FILTER Function

Now, let’s write the FILTER function to filter the data based on a wildcard search term. Since our objective is to filter for products containing the word “summer” in their names. Here’s how you can do it:

Navigate to the formula bar and type in the following formula: 

=FILTER(A2:B11, SEARCH(“summer”, A2:A11))

Step 3: Hit Enter

Once you press Enter, you’ll instantly see the filtered data in your selected cell. It will show only the rows where the product names contain the word “summer.”

Here is what our spreadsheet looks like after filtering our data using wildcard. 

And that’s it. You’ve successfully used the FILTER function to filter data with wildcard in Google Sheets. This method can save you time and effort, especially when dealing with large datasets and complex filtering criteria.

Final Thoughts

In Google Sheets, there are many ways to filter your data, but using wildcards gives you precise control over what you’re looking for. In this guide, we’ve walked you through the step-by-step process of filtering your data with wildcards.

Whether you like doing things manually or prefer the automated approach using the filter function, these techniques are here to make your data searches easier and more efficient.

We hope this guide has given you all the essential knowledge you need to confidently filter data with wildcard in Google Sheets.