How to Use Like in Excel: Master Wildcards in Minutes

What 'LIKE' means in Excel and why it matters

In Excel, there is no direct LIKE operator as you find in SQL. Instead, you simulate LIKE-style pattern matching with wildcards inside text-criteria formulas. If you want to master how to use like in excel, this guide walks you through reliable techniques that scale from small worksheets to large data sets. The concept matters because text-search tasks are among the most frequent data-cleaning operations in spreadsheets. With wildcards, you can pull out items that start with, end with, or contain a given sequence without building multiple nested IF statements.

According to XLS Library, most everyday data tasks hinge on flexible matching rather than exact equals. Pattern-based checks save time and reduce errors when you’re sifting through customer lists, product catalogs, or log files. The key is understanding when to use COUNTIF-type formulas vs. the dynamic capabilities of modern Excel (365) such as FILTER. You’ll also learn when to normalize data (trim spaces, convert to consistent case) to avoid false negatives. With practice, you’ll convert many manual searches into one concise formula.

Wildcards you can use in Excel formulas

Excel supports a compact set of wildcards that act like the SQL LIKE operators. The asterisk () matches any number of characters, the question mark (?) matches exactly one character, and the tilde (~) escapes a wildcard to treat it as a literal character. When you embed these in text criteria, Excel searches become flexible rather than rigid. For example, "sales" finds any text that begins with "sales," while "report" locates items containing "report" anywhere in the string. The key is to place the pattern in quotes within COUNTIF/COUNTIFS, SUMIF/SUMIFS, or FILTER to guide the engine toward your target. If you’re starting out, keep a small test column to verify the results before applying formulas across an entire data range. (Note: wildcard behavior is consistent across Excel for Windows and Mac, but some functions vary in their support for dynamic arrays.)

Using COUNTIF/COUNTIFS for pattern matching

COUNTIF is the simplest way to apply a LIKE-style test to a single range. Use a wildcard in the criteria to match the text pattern you’re after. For example, =COUNTIF(A2:A100,"*report") counts cells that end with “report.” For multiple criteria, COUNTIFS lets you combine patterns across fields, such as =COUNTIFS(A2:A100,"2026", B2:B100,"sales"). XLS Library analysis shows that pattern-based checks are a common workflow in data-cleaning tasks, especially when filtering lists for categories or statuses. When patterns get more complex, break them into helper columns or leverage dynamic array formulas for immediate results. This approach scales from tiny lists to large datasets while remaining transparent and auditable.

Additionally, you can pair COUNTIF with functions like EXACT to enforce case-sensitive checks where needed, or with SEARCH to locate patterns without matching the entire field.

Using SUMIF/SUMIFS, AVERAGEIF with wildcards

SUMIF and SUMIFS let you accumulate values that meet a text pattern, which is especially useful for category-based totals. For instance, =SUMIF(C2:C100,"refund", D2:D100) adds up all amounts where the description contains "refund." AVERAGEIF and AVERAGEIFS behave similarly, returning average values for rows that match a wildcard pattern. On large data sets, this can dramatically simplify financial reporting and data summarization. As you implement these, keep in mind that sum-based tests can be affected by non-printing characters; trim data if results look off. When you combine wildcards with numeric ranges, ensure your criteria do not overlap or miss edge cases. Always validate with a small sample before committing to a full data pull.

Advanced techniques: FIND, SEARCH, and TEXT functions to refine matches

If you need more control than wildcards provide, use FIND (case-sensitive) or SEARCH (case-insensitive) to locate patterns inside a text string, then combine with LEFT, RIGHT, or MID to extract matches. The TEXT function can normalize numbers and dates before comparison, improving consistency. In Excel 365, FILTER can return a spill range of rows that meet a wildcard-based criterion, which is invaluable when you want to visualize a live subset. For example, =FILTER(A2:C100,ISNUMBER(SEARCH("report",A2:A100))) returns rows containing the term anywhere within the text. These techniques help you replicate nuanced LIKE behavior beyond simple wildcards.

Handling literal wildcard characters and escaping

Sometimes you need to search for a literal *, ?, or ~, not use them as wildcards. In such cases, prefix the character with a tilde (~) to escape it, e.g., =COUNTIF(A2:A100,"~data") looks for the string data literally rather than applying a wildcard. Be mindful of data that contains extra spaces or inconsistent capitalization; use TRIM and UPPER/LOWER as part of your workflow to normalize inputs before applying wildcard tests. If patterns fail unexpectedly, inspect the data for hidden characters such as non-breaking spaces and consider cleaning them with CLEAN.

Practical workflows and templates

Build a reusable template that documents the exact wildcard patterns used in each column, along with tested examples and expected results. Start with a simple workbook that contains a data sheet, a criteria sheet, and a results sheet. Use helper columns to stage complex patterns before pulling them into your main formulas. According to XLS Library, adopting a modular approach—patterns in one place, formulas in another—reduces errors and accelerates training for teams. As you practice, consider sharing a short cheat sheet of core wildcards and example patterns with teammates to encourage consistency across projects. The XLS Library team recommends integrating wildcard-based techniques into daily Excel workflows.