Excel Filter Duplicates: Is Matching Case-Sensitive?

Formulas & functions · Updated November 19, 2026

No. In Excel for Microsoft 365 on Windows, the regular filter and built-in duplicate tools do not distinguish text by letter case: “Maple” and “MAPLE” are treated as matches for these purposes. To identify duplicates only when the capitalization is identical, use the case-sensitive EXACT function in a helper column, then filter the results.

What Excel’s built-in tools consider a duplicate

Excel has several features that people describe as “filtering duplicates,” but they do different jobs. The dropdowns created with Data > Filter let you show or hide rows based on values or text conditions. Remove Duplicates deletes repeated records from a selected range. Advanced Filter can copy unique records to another location. None of these is a case-sensitive duplicate checker: for ordinary text comparisons, uppercase and lowercase letters do not make values different.

For example, if a column contains Maple, MAPLE, and maple, the built-in duplicate-removal operation can treat those entries as duplicates. Similarly, a text filter for a value is not a reliable way to isolate only one capitalization. The regular filter is useful for narrowing down a list, but it does not add a case-sensitive comparison option.

This distinction matters when capitalization carries meaning, such as in product codes, usernames, identifiers imported from another system, or labels where capitalization is part of the official value. In ordinary lists of names or descriptions, case may be cosmetic, and treating those variants as duplicates may be exactly what you want.

How the regular Filter feature behaves

Data > Filter adds a dropdown to each header in a data range. You can select values from the dropdown or choose conditions such as Text Filters > Equals or Text Filters > Contains. Those options help display records that meet a condition; they do not report which values occur more than once. And because Excel’s normal text matching is case-insensitive, changing the capitalization in the criterion will not make it an exact-case test.

For instance, suppose A2:A5 contains Maple, MAPLE, Birch, and maple. A standard filter can show or hide rows based on that text, but it cannot distinguish the three versions of Maple by capitalization alone. To filter only exact-case repeats, create a separate result column first, then filter that column for TRUE.

To turn on the dropdowns, select a cell in the data range and choose Data > Filter. On Windows, Ctrl+Shift+L toggles filters on or off. If the data is an Excel table, its header dropdowns are normally available already. On a Mac, use Data > Filter if the Windows shortcut is not applicable to your keyboard setup.

Use EXACT to find case-sensitive duplicates

EXACT compares two text strings and returns TRUE only when their characters match, including capitalization. It is case-sensitive, unlike common worksheet comparisons such as COUNTIF. Combine EXACT with SUMPRODUCT to check whether a value appears more than once in a range.

With the values to check in A2:A100, and the formula entered in B2, use:

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

Fill the formula down through B100. For a text value in A2, EXACT compares it with every value in A2:A100; SUMPRODUCT counts the TRUE results. If the count exceeds one, the formula returns TRUE. If A2 is “Maple” and the range also contains “Maple” but only “MAPLE” otherwise, the exact-case occurrences count as duplicates, while the all-uppercase entry is not part of that count. The IF wrapper leaves a blank source cell blank rather than marking every blank as a duplicate.

To display only the repeated, exact-case values, enable the filter dropdowns and select TRUE in column B. The result includes every occurrence of a duplicated spelling, including its first occurrence. That is often the most useful view when you want to inspect or review each matching row.

Show only later occurrences instead

Sometimes you want to keep the first exact-case entry and mark only later repeats. Use a running range that begins at the first data row and expands as the formula is filled down.

With the list starting in A2 and the result formula in B2, enter:

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

Fill it down alongside the list. In row 2, the formula checks only A2, so it cannot be a later repeat. In the next row, it checks from A2 through A3; each subsequent row checks all values from A2 through the current row. Thus, the first exact-case occurrence returns FALSE and later occurrences return TRUE. With “Maple,” “MAPLE,” “Maple” in that order, only the final “Maple” is marked TRUE.

Filter the helper column to TRUE to review only later copies. This method does not delete anything, which makes it safer for auditing than immediately using Remove Duplicates. If you do decide to delete rows, keep a copy of the original data first and confirm that the first occurrence is the one you intend to retain.

Highlight matches without filtering rows

If your goal is to spot duplicates visually, conditional formatting can color all entries that have an exact-case match elsewhere in the list. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2:A100 selected and A2 as the active cell, enter:

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

Choose a format and confirm the rule. The formula checks the full range for each selected cell, so every occurrence in a duplicated case-sensitive group is highlighted. Unlike the running-range formula, this does not distinguish the first instance from later ones. Conditional formatting changes the appearance, not the cell values, and it does not remove or hide rows.

Check the selected range and the row reference before saving the rule. If your data begins on row 3, for example, select the corresponding data cells and adjust the formula’s reference to the first selected row. A mismatched starting row can make highlighting appear one row off or produce results that seem inconsistent.

Why COUNTIF and Remove Duplicates can mislead

A common attempted shortcut is COUNTIF. It is convenient for counting ordinary duplicates, but COUNTIF is not case-sensitive, so it will count “Maple” and “MAPLE” together. It can therefore mark values that are not duplicates under a strict capitalization-sensitive definition. EXACT avoids that issue because it compares the text with case taken into account.

Remove Duplicates is also different from a case-sensitive check. Find it on the Data tab after selecting the relevant data; if the list has headers, confirm the header option in the dialog. The command changes the selected data by removing rows it identifies as duplicates, and it does not provide a setting to make its text comparison case-sensitive. Use it when Excel’s normal duplicate definition is appropriate, not when capitalization must distinguish records.

Advanced Filter’s “Unique records only” option is another way to extract unique records, not a workaround for case-sensitive matching. If case distinctions are essential, make the distinction explicit in a formula or prepare the data with a method that preserves it before filtering. Avoid assuming that a feature called “unique” necessarily uses the definition of uniqueness your work requires.

Practical checks before acting on the results

Case is only one possible difference between two cells. A value may also contain a leading or trailing space, a nonbreaking space copied from a webpage, or a different character that looks similar. EXACT compares the strings it receives; it does not clean or standardize them. If two entries look identical but are not matched, inspect the underlying text and consider cleaning the source data separately.

  • Decide whether you mean all members of a duplicated group or only later occurrences; choose the full-range or running-range formula accordingly.
  • Check that the comparison range covers every relevant row and that the formula is filled down to the last record.
  • Keep headers outside the comparison range so that the header is not counted as data.
  • Use a helper column or conditional formatting to review results before deleting or overwriting records.
  • For mixed data containing numbers and text, confirm how the values are stored. A number and a text string that looks like that number may not behave as the same value.

These formulas work in Microsoft 365 for Windows and use long-established functions, so they are not dependent on newer dynamic-array functions such as FILTER or UNIQUE. Excel for Mac has the same relevant worksheet functions, though menu presentation and keyboard shortcuts can differ. Excel for the web also supports many standard formulas and filtering operations, but the exact editing controls can vary; use the desktop app if a particular conditional-formatting option is unavailable in your environment.

Frequently Asked Questions

Does Excel’s Filter button find duplicates?
No. Data > Filter displays rows that meet chosen criteria; it does not count duplicate values. Add a helper formula such as the EXACT and SUMPRODUCT example above, then filter the helper column for TRUE.

Is Excel’s Remove Duplicates feature case-sensitive?
No. It does not offer a case-sensitive comparison setting, so values that differ only by uppercase and lowercase letters can be treated as duplicates. Make a backup before using it because it removes rows from the selected data.

Can COUNTIF detect duplicates while ignoring capitalization?
COUNTIF is case-insensitive, so it can find ordinary repeated text but cannot separate “Maple” from “MAPLE.” For a capitalization-sensitive test, use EXACT in a formula such as the SUMPRODUCT examples.

How do I keep the first duplicate and mark the rest?
Use the running-range formula =IF(A2="","",SUMPRODUCT(--EXACT($A$2:A2,A2))>1) with the list starting in A2, then fill it down. Filter the result column to TRUE to see later exact-case occurrences.

Can I highlight case-sensitive duplicates instead of filtering them?
Yes. Apply a conditional-formatting rule to the data range using the full-range EXACT and SUMPRODUCT formula. It highlights every instance in a repeated exact-case group but leaves the rows visible.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.