How to highlight case sensitive duplicates

Answer

To highlight case-sensitive duplicates in Excel, the built-in conditional formatting is not enough because it treats text like "Apple" and "apple" as the same. You must use a formula that distinguishes the case of letters.

Steps to Highlight Case-Sensitive Duplicates

1. Select the Range
   Highlight the cells where you want to check for duplicates, for example, A1:A20.

2. Open Conditional Formatting

   • Go to the Home tab.

   • Click Conditional Formatting → New Rule → Use a formula to determine which cells to format.

3. Enter the Formula
   In the formula box, enter:

   =SUMPRODUCT(--EXACT(A1,$A$1:$A$20))>1

   Explanation:

   • EXACT(A1,$A$1:$A$20) checks each cell in the range against A1 and is case-sensitive.

   • -- converts TRUE/FALSE values into 1/0.

   • SUMPRODUCT(...)>1 counts how many times the exact match occurs. Values greater than 1 are duplicates.

4. Choose Formatting

   • Click Format and select a color or font style to highlight duplicates.

   • Press OK to apply.

5. Apply the Rule
   The formatting will now highlight all cells that are duplicates with the same case.

Notes

• The formula works for both Windows and Mac Excel versions.

• Adjust $A$1:$A$20 to your actual data range.

• It is case-sensitive: "Apple" and "apple" are treated as different values.

Example

This method ensures exact matches including capitalization are detected.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report