Wildcard characters are special characters in Microsoft Excel that let you extend or narrow down your search query. You can use these wildcards to find or filter data, and you can also use them in formulas.

In Excel, there are three wildcards: Asterisk (*), question mark (?), and tilde (~). Each of these wildcards has a different function, and though only three, there's a lot you can accomplish with them.

Wildcards in Excel

  • Asterisk *: Represent any number of characters where the asterisk is placed.
    • A query such as *en will include both Women and Men, and anything else that ends in en.
    • Ra* will include Raid, Rabbit, Race, and anything else starting with Ra.
    • *mi* will include anything that has a mi in it. Such as Family, Minotaur, and Salami.
  • Question Mark ?: Represents any single character where the question mark is: S?nd. This includes Sand and Send. Moreover, ???? will include any 4 letter text.
  • Tilde ~: Indicates an upcoming wildcard character as text. For instance, if you want to search for a text that contains a literal asterisk and not a wildcard asterisk, you can use a tilde. Searching for Student~* will bring up only Student*, while searching for Student* will include every cell that starts with Student.

How to Filter Data With Wildcards in Excel

A sample sheet for Excel wildcards.

With wildcards, you can make a specific filter for your data in Excel. In this example spreadsheet, we have a list of the first ten elements from the periodic table. We're going to make two different filters: First to get a list of elements that end with ium, and second to get a list of elements that only have four letters in their names.

  1. Click the header cell (A1) and then drag the selection over to the last element (A11).
  2. On your keyboard, press Ctrl + Shift + L. This will make a filter for that column.
  3. Click the arrow next to the header cell to open the filter menu.
  4. In the search box, enter *ium.
  5. Press Enter.
  6. Excel will now bring up all the elements in the list that end with ium.

Related: How to Count Unique Values in Excel

Result of the filter with asterisk wildcard.

Now let's run a filter to see which elements in this list have only four letters in their names. Since you've already created a filter, you'll just need to adjust the content of the search box.

  1. Click the arrow next to the header cell. This will open the filter menu.
  2. In the search box, enter ????.
  3. Press Enter.
  4. Excel will now show only the elements that have four letters in their names.
excel-element-2

To remove the filter and make all the cells visible:

  1. Click the arrow next to header cell.
  2. In the filter menu, under the search box, mark Select All.
  3. Press Enter.

If you're curious about the filter tool and want to learn more about it, you should read our article on how to filter in Excel to display the data you want.

How to Search Data With Wildcards in Excel

The Find and Replace tool in Excel supports wildcards and lets you use them to narrow down or expand your search. In the same spreadsheet, let's try to search for elements that begin and end with N.

  1. Press Ctrl + F on your keyboard to bring up the Find and Replace window.
  2. In the text box in front of Find what enter N*N.
  3. Click Find All.
  4. The window will extend to display a list of cells that contain elements beginning and ending with N.

Now let's run a more specific search. Which element in the list begins and ends with N, but has only four letters?

  1. Press Ctrl + F on your keyboard.
  2. In the text box, type N??N.
  3. Click Find All.
  4. The Find and Replace window will extend to show you the result.

Related: How to Use the Find and Replace Search Feature in Excel

How to Use Wildcards in Excel Formulas

While not all functions in Excel support wildcards, here are the six ones that do:

  • AVERAGEIF
  • COUNTIF
  • SUMIF
  • VLOOKUP
  • SEARCH
  • MATCH

Related: How to Do a VLOOKUP in an Excel Spreadsheet

Excel wildcards with COUNTIF

In this example, we have a list of cars and their engine configurations. We're going to use the COUNTIF function to count three kinds of cars and then display the results in three separate cells. First, we can count how many cars are powered by 12-cylinder engines, then we can count how many cars have four-letter names.

Now that you have a grip on wildcards, it's easy to guess that the first and second objectives can be accomplished solely with the asterisk.

  1. Select cell E6 (in our example).
  2. In the formula bar, enter the formula below:
    =COUNTIF(B2:B11, "*12")
    This formula will take the cells B2 to B11 and test them for the condition that the engine has 12 cylinders or not. Since this is regardless of the engine's configuration, we need to include all the engines that end with 12, thus the asterisk.
  3. Press Enter.
  4. Excel will return the number of 12-cylinder cars in the list.

Counting the number of GT cars is done similarly. The only difference is an additional asterisk at the end of the name, which alerts us to any cars that do not end with GT but still contain the letter.

  1. Select cell E7.
  2. In the formula bar, enter the formula below:
    =COUNTIF(A2:A11, "*GT*")
    The formula will test cells A2 to A11 for the *GT* condition. This condition states that any cell that has the phrase GT in it, will be counted.
  3. Press Enter.
  4. Excel will return the number of GT cars.

The final objective is slightly different. To count the cars that have four-letter model names, we're going to need the question mark wildcard.

  1. Select cell E8.
  2. In the formula bar, enter the formula below:
    =COUNTIF(A2:A11, "* ????")
    This formula will test the car names, the asterisk states that the car brand can be anything, whereas the four question marks make sure that the car model is in four letters.
  3. Press Enter.
  4. Excel will return the number of cars that have four-letter models. In this case, it's going to be the Golf and the DB11.
Excel wildcards used in the COUNTIF function.

Get the Data You Want With Wildcards

Wildcards are simple in nature, yet they have the potential to help you achieve sophisticated things in Excel. Coupled with formulas, they can make finding and counting cells much easier. If you're interested in searching your spreadsheet more efficiently, there are a couple of Excel functions that you should start learning and using.