In Excel conditional formatting, a question mark (?) is a wildcard in criteria: it matches exactly one character. For example, a formula rule using =COUNTIF(A2,"?????")=1 can highlight text in A2 that is exactly five characters long; use a tilde (~) before a question mark when you mean a literal question mark instead.
What the question mark means
Excel uses wildcards in certain text criteria. The question mark stands for one character, while an asterisk (*) stands for any number of characters, including none. These are not interchangeable: AB? can match “ABC” or “AB7,” but not “AB” or “ABCD.” AB* can match all four of those examples because the asterisk can represent zero or more characters.
A question mark does not mean “maybe one character” or “zero or one character.” It always represents one character when interpreted as a wildcard. It can match a letter, digit, space, or punctuation mark. Excel’s wildcard criteria are generally not case-sensitive, so case alone does not make a text value a different match.
That definition is useful when a formatting rule should identify text with a specific pattern: a fixed number of characters, a shared prefix followed by any character, or text containing a particular sequence. It is also important to distinguish a wildcard question mark from a literal ? that appears in a cell’s contents.
Make a wildcard-based conditional formatting rule
In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula, choose Format to pick the appearance, and confirm the dialogs. The formula must evaluate to TRUE for a cell to receive the format.
Suppose the range is A2:A100 and you want to highlight text entries that have exactly five characters. Select A2:A100, then enter this rule:
With the first cell of the selected range in A2, use =COUNTIF(A2,"?????")=1. It returns TRUE for a five-character text value such as “Maple” and FALSE for “Oak” or “Willow.” The reference A2 is relative, so Excel checks A3 for the next row, then A4, and so on.
To apply a rule across multiple columns, select the full target range first and make the formula reference its top-left cell. For example, if the range is B2:D50, a formula referring to B2 is evaluated relative to each cell. A locked reference such as $B$2 stays fixed, while B2 shifts as the rule is applied. That distinction often determines whether a rule behaves as intended.
On Excel for Mac, the same conditional-formatting concepts and formula logic apply; the ribbon layout or dialog wording can vary by release. Excel for the web also supports conditional formatting, though its available controls and dialogs may differ from desktop Excel. If a rule is difficult to build in the web interface, open the workbook in the desktop app and check the rule there.
Useful pattern examples
For each example, assume the rule is applied to the stated range and the formula refers to that range’s first cell. Choose the fill or font formatting after entering the formula.
- Exactly four characters: With text in A2:A100, use
=COUNTIF(A2,"????")=1. It highlights “Pine” but not “Fir” or “Aspen.” - Starts with A and has at least two characters: With text in A2:A100, use
=COUNTIF(A2,"A?*")>0. “Apple” and “A7” match; a single-character “A” does not. - Contains the sequence “cat” anywhere: With text in A2:A100, use
=COUNTIF(A2,"*cat*")>0. This matches “catalog” and “black cat.” The wildcard match is not case-sensitive. - Has a literal question mark anywhere: With text in A2:A100, use
=COUNTIF(A2,"*~?*")>0. The tilde tells the criteria to treat the next question mark as punctuation, not as a wildcard.
COUNTIF is useful here because its criteria understand the wildcard characters. The final comparison, such as >0 or =1, turns the count into a TRUE-or-FALSE result that conditional formatting can use. For the single-cell patterns above, the count is typically either zero or one.
How to match a literal question mark
To search for an actual question mark, escape it with a tilde in a wildcard criterion. For example, the criterion *~?* means “any characters, then a literal question mark, then any characters.” Without the tilde, *?* means a string containing at least one character; it does not specifically look for punctuation.
With text in A2:A100, the formula =COUNTIF(A2,"*~?*")>0 highlights a cell such as “Status?” but not “Status.” To match a cell whose entire content is just one question mark, use =COUNTIF(A2,"~?")=1. This distinction matters in lists of unanswered questions, survey responses, or codes where punctuation has meaning.
The tilde is Excel’s wildcard escape character in criteria: use ~? for a literal question mark, ~* for a literal asterisk, and ~~ for a literal tilde. If the text is being checked by a different function or rule type, confirm that it interprets wildcard escapes the same way. Using a formula rule makes the intended criteria more explicit than relying on an ambiguous-looking entry in a dialog.
Choosing between COUNTIF and a text-length formula
COUNTIF is a compact way to express wildcard patterns, but it is not the only option. For a straightforward requirement such as “highlight text with exactly five characters,” LEN can be easier to read and avoids wildcard criteria.
With text in A2:A100, use =AND(ISTEXT(A2),LEN(A2)=5) to highlight text values that are five characters long. For example, “Maple” returns TRUE, while “Oak” returns FALSE. ISTEXT excludes numeric values; without it, a number’s displayed appearance and its underlying value can make a length test less intuitive. Decide whether spaces should count: LEN includes spaces, so “Oak ” with a trailing space has four characters.
Use COUNTIF when the pattern itself matters, such as a prefix plus a fixed number of characters or a literal wildcard symbol. Use LEN when you simply need to test the number of characters. If matching must be case-sensitive, ordinary COUNTIF wildcard criteria are not enough; design a formula around a case-sensitive function such as EXACT where appropriate, and verify the desired pattern carefully.
Common problems and how to fix them
- The rule highlights too many cells: Check whether you used
*when you meant?. The asterisk can match any-length text, while each question mark represents one character. - The literal punctuation is not being found: Escape the question mark as
~?in a wildcard criterion. A bare question mark is a wildcard there. - The formula works in one row but not the rest: Confirm the formula refers to the top-left cell of the “Applies to” range. Review dollar signs in references; an accidentally fixed row or column can make every cell evaluate against the same location.
- Blank cells behave unexpectedly: A pattern requiring characters, such as
?????, will not match a blank. If the rule has additional tests, explicitly exclude blanks with a condition such asA2<>"". - Numbers do not match as expected: Wildcard criteria are intended for text patterns. If cells contain numeric values, decide whether they should be treated as numbers or text; formatting a number to show leading zeros does not necessarily change its stored value.
- The fill or font is not visible: Open Home > Conditional Formatting > Manage Rules and check the rule’s formula, range, and priority. Another rule may apply a conflicting format, or the “Applies to” range may omit the cells in question.
When checking a troublesome rule, test the pattern on a few simple sample values first. Include a match, a nonmatch, a blank, and—if relevant—a value with a trailing space. This separates a pattern problem from a range-reference or formatting-priority problem.
Conditional formatting versus Find and Replace
Conditional formatting changes how qualifying cells look; it does not alter their contents. A wildcard rule can be useful for visually flagging records, but it will not remove a question mark, rewrite a value, or make that character part of a cell’s underlying data. To change cell contents, use an editing or replacement method rather than conditional formatting.
Find and Replace also has its own wildcard options and escaping behavior, controlled by the choices in its dialog. Do not assume that typing the same characters into every Excel feature produces the same result. For conditional formatting, a formula rule using COUNTIF is a clear way to state a wildcard condition and have Excel apply a visual format when the condition is true.
Frequently Asked Questions
Does a question mark match one or any number of characters in Excel?
A wildcard question mark matches exactly one character. An asterisk matches any number of characters, including zero.
How do I highlight cells containing a literal question mark?
Apply a formula-based conditional-formatting rule. With the range A2:A100 selected, use =COUNTIF(A2,"*~?*")>0. The tilde escapes the question mark so Excel looks for punctuation.
Can I use a question mark in a conditional-formatting formula?
Yes. For wildcard matching, place it in a criterion used by a function such as COUNTIF. For example, ???? represents any four-character text value in a COUNTIF criterion.
Does the question mark match a space or a number?
Each wildcard question mark stands for one character, which can include a space, digit, letter, or punctuation. However, how numeric cell values behave in a text criterion may not match what their displayed formatting suggests; test the actual data or convert it to text if that is the intended comparison.
Why does my conditional-formatting rule apply to every row?
Check the formula’s cell reference and the rule’s “Applies to” range. For a range beginning at A2, a row-by-row rule usually needs a relative reference to A2, not a fully locked reference such as $A$2.
Is the question mark case-sensitive?
No. The question mark itself represents any one character, and COUNTIF wildcard matching is not case-sensitive. If a pattern must distinguish uppercase from lowercase letters, use a deliberately case-sensitive formula approach instead.
Related
- Excel Format Text: Uppercase, Lowercase, and Proper Case
- Proper Excel Data Entry: Rules, Tips, and Common Errors
- Proper Excel Formatting: Clear, Reliable Spreadsheet Rules
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Change Currency Format in Google Sheets: A Practical GuideTo change a cell’s currency format in Google Sheets, select the cell or range, then choose Format >…
- Excel Freeze Panes Shortcut: Keys and How It WorksIn Excel for Microsoft 365 on Windows, select the cell below and to the right of the rows…
- Pokémon Card Inventory Spreadsheet: What to TrackA useful Pokémon card inventory spreadsheet records each card’s identity, condition, quantity, purchase details, and storage location; in…
- Google Sheets: Combine Two Columns into OneYes. In Google Sheets, combine two columns row by row with a formula such as =A2&" "&B2, which…
- Excel Find and Replace: Steps, Options, and Common FixesTo find or replace content in Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and…
- Archive Emails by Date in Outlook: Step-by-Step GuideTo archive Outlook emails from a particular date range, search for messages received during those dates, select the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.