Excel: How to avoid a question mark triggering the duplicate rule

One can prefix ~ when * or ? is found. The ~ tells the countif we want the literal string and not the wildcard:

=COUNTIF($A$1:$A$10,SUBSTITUTE(SUBSTITUTE($A1,"*","~*"),"?","~?"))>1

The COUNTIF function accepts wildcards, so when the cell contains ? it does a wildcard search.

If the problem is only with the question mark, you could test for it:

=IF(a1="?", false, countif(a1:a10, a1)>1) 

First, you need to lock your range references in conditional formatting formulas. Using the formula exactly as you have it means the conditional formatting won't work the way you expect. For cell A1, it'll look at the range A1:A10 but, for cell A10, it'll look at the range A10:A19. Your original formula should have at least set the row numbers as absolute instead of relative values: =countif(a$1:a$10, a1)>1.

Regardless, you need to change the formula. COUNTIF() accepts wildcards. ? means a single character and * means any number of characters. When the cell value is the literal string ?, it counts the cells in the range that contain a single characters. Instead, try this:

=AND(A1<>"",SUM(--EXACT(A1,A$1:A$10))>1)

EXACT() checks if two inputs are an exact match and returns either TRUE or FALSE. Adding the -- in the front is a quick way of converting TRUE to 1 and FALSE to 0 because it's "negative negative". You could also use 1* instead of --. You just need some math operation. The whole function, then, counts all the exact matches within the range. Since it doesn't use wildcards like COUNTIF(), the liter string ? is just treated like a literal string. Adding the A1<>"" requirement lets it ignore blank cells.