Excel Wildcards: Advanced Uses Beyond the Basics

Hi ,

You might think you already know wildcards in Excel.

Most people use them in COUNTIF or maybe XLOOKUP and stop there. That is only scratching the surface.

Wildcards appear in far more places than formulas, and when you combine them with other Excel tools, you can solve problems faster and with less effort.

In this guide, you will learn both the basics and several advanced uses that can improve how you work with data.

What Are Wildcards in Excel

Excel supports three wildcard characters:

These are mainly used in text matching scenarios, but the real power comes from how you combine them with functions and tools.

XLOOKUP with Wildcards

A common starting point is searching for text within a larger string.

For example, if you want to find descriptions containing the word boots in the table below:

You can use the XLOOKUP function like so:

And the result is below:

Key points:

This approach works well when you expect a single result. If you have multiple matches, you need a different tool.

FILTER for Multiple Matches

If you want all matching records, the FILTER function is the better choice:

And the result is below:

How it works:

Unlike XLOOKUP, this returns all matching rows.

COUNTIF with Wildcards

Wildcards turn the COUNTIF function into a flexible text search tool.

For example, we can count how many descriptions contain the word refund in this table:

Using this formula:

This counts matches regardless of where the word appears in the text and returns 2:

SUMIFS with Partial Matches

You can apply the same logic when summing data with the SUMIFS function.

For example, we want to sum the revenue for online channels:

We can use this formula:

Which returns 1700:

In this case:

This removes the need for exact matches in SUMIFS.

Data Validation Using Wildcards

Wildcards are also useful for enforcing patterns.

We can use the formula below in a custom data validation rule to ensure employee IDs follow the format EMP followed by four characters:

The question marks enforce exactly four characters after EMP as shown in the image below:

However, this approach has limitations. It does not enforce uppercase or numeric-only rules.

Strict Validation Without Wildcards

For stricter rules, combine multiple functions:

This ensures:

Wildcards are helpful, but not always sufficient on their own.

Advanced Filter with Wildcards

Formulas are not the only place wildcards work.

Advanced Filter allows you to extract records quickly.

For example, using criteria like:

ABC*

You can extract all records where values begin with ABC.

Keep in mind:

Find and Replace with Wildcards

Wildcards are extremely useful for cleaning data with the Find and Replace tool.

To remove text in brackets:

This removes everything inside parentheses, even if the text varies:

Using Wildcards Inside IF Logic

IF does not support wildcards directly.

However, you can combine it with COUNTIF:

This allows you to flag rows based on text patterns:

Escaping Wildcards

If your data contains actual wildcard characters, Excel will still treat them as wildcards.

To search for a literal asterisk:

The tilde tells Excel to treat the asterisk as a normal character.

This is often overlooked and can lead to incorrect results.

Conditional Formatting with Wildcards

You can use wildcard logic in conditional formatting to highlight rows, for example the rows below where the Note contains 'urgent':

Example:

Apply this as a rule to highlight rows containing the word urgent.

This is especially useful when you want to format multiple columns based on text in a single column, which can't be done with the built in conditional rormatting tools.

Why Wildcards Matter More Than You Think

Wildcards may seem simple, but their real value comes from combining them with:

Once you start using them across these tools, your workflow becomes faster and more flexible.

Take Your Excel Skills Further

This type of logic is what separates basic Excel users from confident, advanced users.

If you want to get comfortable combining formulas, validation, and data tools, the Excel Expert course walks through everything step by step.

You will learn how these features connect so you can build spreadsheets that are both powerful and reliable.

Final Thoughts

Wildcards are one of those features that look simple at first.

Once you start combining them with Excel's tools, they become a powerful way to search, clean, validate, and analyse data.

Most users stop at the basics. That is where the opportunity is.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub