How to Find Case-Sensitive Duplicates in Excel

Why Excel's Default Duplicate Detection Is Case-Insensitive

Excel functions like COUNTIF, MATCH, and the built-in Remove Duplicates feature all perform case-insensitive comparisons by design. When you highlight duplicates using Conditional Formatting, Excel flags both Apple and APPLE as duplicates of each other.

This works perfectly for text data like product names or city names where capitalization is just a display preference. But it creates a problem when case carries meaning — such as in database IDs, employee codes, software license keys, warehouse location codes, and user handles.

When Case Matters in Real Data

Common scenarios where case-sensitive duplicate checking is necessary:

• Product SKU codes: SKU-A100 and sku-a100 may represent different items from different suppliers.
• Usernames and account IDs: Most databases are case-sensitive by default for logins.
• Software license keys: Keys are usually exact strings with specific casing.
• API keys and tokens: Bearer abc123XYZ is different from Bearer ABC123xyz.
• Warehouse and location codes: Bin-A1 vs bin-a1 may map to different shelf locations.
• System exports from databases: Exports from MySQL, PostgreSQL, or other case-sensitive systems preserve case exactly as stored.

Method 1: EXACT + SUMPRODUCT Formula

The EXACT function compares two text values and is fully case-sensitive. Wrap it inside SUMPRODUCT to check against a range:

=SUMPRODUCT(--EXACT(A2,$A$2:$A$100))>1

This formula counts how many times the exact value in A2 appears in the range A2:A100. If the count is greater than 1, it returns TRUE (a duplicate exists).

Place this in column B, starting at B2, and drag it down. TRUE rows are case-sensitive duplicates. You can then filter column B for TRUE to see only the matching rows.

Finding the First Occurrence vs. Later Occurrences

To flag only the second and later occurrences (not the first), adjust the range to end before the current row:

=SUMPRODUCT(--EXACT(A2,$A$2:A2))>1

The second reference grows as you drag the formula down, so only rows where the value has already appeared above are flagged. This is useful when you want to keep the first instance and remove the rest.

Method 2: Helper Column with EXACT and IF

If you prefer readable labels over TRUE/FALSE, use an IF formula:

=IF(SUMPRODUCT(--EXACT(A2,$A$2:$A$100))>1,"Duplicate","Unique")

This displays "Duplicate" or "Unique" next to each row, which is easier to explain to teammates or include in a report.

Method 3: Conditional Formatting with a Custom Formula

To highlight case-sensitive duplicates visually:

1. Select the column containing your values, such as A2:A100.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter this formula (adjusting the range to match your data):

=SUMPRODUCT(--EXACT(A2,$A$2:$A$100))>1

5. Choose a fill color and click OK.

Cells that match exactly — including case — will be highlighted. Cells that only match case-insensitively will not be flagged.

Method 4: Power Query for Large Datasets

For large exports or datasets you process repeatedly, Power Query is a more reliable approach because it handles case sensitivity consistently across all transformations.

1. Load your data into Power Query via Data > From Table/Range.
2. Add a custom column with a formula that groups duplicates by exact text match.
3. Group by the key column and count rows per group to find groups with more than one row.
4. Filter down to groups with count greater than 1 and expand back to individual rows.

Power Query formulas are case-sensitive by default, unlike Excel formulas, so the comparison happens exactly as you intend.

Case-Insensitive Cleanup Before De-Duplication

Sometimes the opposite problem occurs: values that should be treated as the same are stored with inconsistent capitalization. For example, North, NORTH, and north all mean the same thing but look different to Excel.

To normalize values before de-duplicating:

=LOWER(TRIM(A2))

This makes all variants lowercase and removes extra spaces at the same time. Create a helper column with this formula, then run duplicate detection on the helper column instead of the original. This is the right approach for city names, department names, product categories, and other text that should be case-insensitive.

How to Handle Case-Sensitive Duplicates Across Two Columns

When duplicates are defined by a combination of columns — for example, first name plus last name, or region plus product code — concatenate them before comparing:

=SUMPRODUCT(--EXACT(A2&B2,$A$2:$A$100&$B$2:$B$100))>1

This checks whether both the value in column A and the value in column B match exactly at the same time. Only rows where both values match, with the same case, are flagged as duplicates.

Before You Delete Anything

Duplicates are not always errors. A customer can place multiple orders. A SKU can appear in multiple warehouses. A user can have multiple sessions. Before deleting flagged rows, ask:

• Are these true duplicates, or legitimate repeated entries?
• Is the duplicate caused by a system export that ran twice?
• Should both case variants exist, or should one be corrected to match the other?
• Which instance should be kept — the first, the most recent, or the one with the most complete data?

When the answer is clear, the Duplicate Row Finder can flag duplicates across one or more columns and let you download a clean workbook before deleting anything from the original file.

Frequently Asked Questions

Does Excel's Remove Duplicates feature consider case?

No. The built-in Remove Duplicates feature is case-insensitive. It treats ABC and abc as the same value and removes one without warning. If case matters, use the EXACT formula method to identify duplicates first, then review them manually before deleting.

Why does COUNTIF not work for case-sensitive duplicates?

COUNTIF is explicitly case-insensitive. It counts both SKU-A1 and sku-a1 as matches for the criterion SKU-A1. There is no way to make COUNTIF case-sensitive. Use SUMPRODUCT with EXACT instead.

Can I use VLOOKUP or MATCH for case-sensitive lookups?

Standard VLOOKUP and MATCH are case-insensitive. For case-sensitive lookups, use an INDEX/MATCH combination with EXACT:

=INDEX(B:B,MATCH(TRUE,EXACT(A:A,D2),0))

Enter this as an array formula with Ctrl + Shift + Enter in older Excel versions. In Excel 365, it works as a regular formula.

What is the fastest way to find case-sensitive duplicates in a large list?

Sort the list first so identical values are adjacent, then use a helper column with =EXACT(A2,A3) to compare each row with the next. This is much faster than SUMPRODUCT on large datasets because it only compares neighboring rows rather than scanning the full range for each cell.