Excel's "Highlight Duplicate Cells" is highlighting values which are actually different

Simple test case:

• Create new workbook
• Format column A as "Text"
• Enter the following into cells A1 and A2:

1234567800000639
1234567800000630

• Select Column A, and in the "Conditional Formatting" submenu, select "Highlight Cells Rule" > "Duplicate Values".

Excel is highlighting these as duplicates, even though they are different.

I suspect Excel is treating them as numbers and it becomes a rounding error, but I have specifically set to Text format and visually see different values, so I'm not sure how to tell Excel to treat it as Text.

1 Answer

Excel has a 15-digit limit to numerical precision, and as you've worked out your numbers have 16 so Excel is converting to a number and can't tell they're not duplicates (despite the fact you've marked them as text and put ' at the start of the numbers to signify a string).

One workaround is to concatenate a letter onto the column of strings you are testing for duplicates. As you can see here, by adding "a" on the front of your string Excel treats it as a real string.