Excel Formula to Find Duplicate Names in a Name List

Formulas & functions · Updated November 19, 2026

To find duplicate names in Excel, use =COUNTIF($A$2:$A$20,A2)>1 beside a list in A2:A20, then fill the formula down. It returns TRUE for every name appearing more than once, and FALSE for a name appearing only once. You can also use the same logic to highlight duplicates or adapt it to ignore blank cells.

What Excel considers a duplicate name

A duplicate is a value that occurs more than once in the range you check. The formula above identifies every occurrence of a repeated name, including the first one. For instance, if “Maya Chen” appears in rows 3 and 8, both rows are marked TRUE. That is useful when you want to review every matching record, rather than just the later copy.

Excel’s COUNTIF function compares text without distinguishing uppercase and lowercase letters. In the ordinary case, “Maya Chen” and “MAYA CHEN” are treated as duplicates. A space at the end of a name, however, can make it different: “Maya Chen” and “Maya Chen ” may not match. Excel also does not know that two differently spelled names belong to the same person. Duplicate detection finds repeated cell contents; it does not determine identity.

Decide what “duplicate” should mean for your task before acting on the results. You might want to find exact repeated text, match names regardless of capitalization, or treat names with extra spaces as equivalent. These are different checks, and cleaning the source data can change the result.

Use COUNTIF to mark every repeated name

Suppose names are in A2:A20, with a header in A1. Enter this formula in B2 and copy it down through B20:

With names in A2:A20 and the first formula beside A2, use =COUNTIF($A$2:$A$20,A2)>1. In an example where “Liam Patel” occurs in A4 and A11, the result in B4 and B11 is TRUE; a name appearing once returns FALSE.

COUNTIF counts the cells in the specified range that match the name in the current row. The greater-than comparison turns that count into a simple TRUE or FALSE result: a count of 2 or more is TRUE, while a count of 1 is FALSE. The dollar signs lock the search range as you copy the formula down, but A2 remains relative so the criterion changes to A3, A4, and so on.

For a more readable result, you can display words instead of TRUE and FALSE:

With names in A2:A20 and the formula starting in B2, use =IF(COUNTIF($A$2:$A$20,A2)>1,"Duplicate","Unique"). If “Liam Patel” occurs twice, each of those rows displays Duplicate; a name found once displays Unique.

To apply the formula, select B2, enter it in the formula bar or cell, and press Enter. Copy it down by dragging the fill handle at the lower-right corner of B2, or select B2:B20 and use Ctrl+D on Windows. On Mac, the equivalent fill-down shortcut is Control+D. The formulas themselves work the same in Excel for Microsoft 365 on Windows, Mac, and the web.

Mark only later occurrences

Sometimes you want to keep the first instance unmarked and flag only later repetitions. This is helpful when reviewing a list from top to bottom because it shows which rows repeat an earlier entry.

With names in A2:A20 and the formula entered beside the first name in B2, use =COUNTIF($A$2:A2,A2)>1. If “Liam Patel” first appears in A4 and again in A11, B4 is FALSE and B11 is TRUE. Copy the formula down the column.

Unlike the full-range version, the second range endpoint expands as the formula is filled down: the first row checks only A2, the next checks A2:A3, and later rows check all names seen so far. That running count is why the first occurrence is not flagged. Use this version when you want to identify extra entries, not mark every member of a duplicate group.

Highlight duplicates without a helper column

Conditional formatting can color duplicate names directly in the list. With names in A2:A20, select A2:A20, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula below, choose a format such as a fill color, and confirm the rule.

With A2:A20 selected and A2 as the active cell, use =COUNTIF($A$2:$A$20,A2)>1. If “Liam Patel” appears in A4 and A11, both cells are highlighted.

The relative reference A2 is important: Excel adjusts it for each cell in the selected range. If you select a different range, make the reference point to its top-left cell. For example, for a selection beginning at C5, use C5 as the relative criterion while keeping the full comparison range anchored. Conditional formatting changes how cells look; it does not alter or remove their contents.

On Mac and Excel for the web, conditional-formatting options are available, though the dialog layout can differ from Windows. The COUNTIF rule’s logic is the same. A helper column can be easier to audit because it shows a clear result for each row, while color alone may be harder to filter, copy, or review systematically.

Skip empty cells and clean inconsistent spacing

If the list contains blanks, the basic formula can count blank cells as repeated values. To avoid labeling empty rows as duplicates, add a check that the current cell is not empty.

With names in A2:A20 and the formula in B2, use =AND(A2<>"",COUNTIF($A$2:$A$20,A2)>1). A blank A2 returns FALSE; if a nonblank name occurs twice, both matching rows return TRUE.

Extra spaces are a common reason names that look alike do not match. TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces to one. CLEAN removes many nonprinting characters. You can put cleaned names in a separate column and check that column instead of changing the original data.

With the original name in A2, enter this cleanup formula in B2 and fill it down: =TRIM(CLEAN(A2)). If A2 contains “ Maya Chen ” with ordinary spaces, B2 returns “Maya Chen.” Then apply the duplicate check to B2:B20, adjusting the formula’s range and row reference to that cleaned column.

TRIM and CLEAN do not remove every unusual character. Data copied from websites or other systems may contain a nonbreaking space, which can look like a normal space but remain in the cell. If visually identical names still fail to match after cleanup, inspect the underlying text or use Find and Replace carefully. Keep the original column until you have confirmed the cleaned results.

Make the comparison case-sensitive when needed

COUNTIF is normally the right choice for names because it ignores capitalization. In the unusual case where capitalization must distinguish two entries, use EXACT inside SUMPRODUCT. EXACT compares text case-sensitively.

With names in A2:A20 and the formula entered beside A2, use =SUMPRODUCT(--EXACT(A2,$A$2:$A$20))>1. If “Maya Chen” appears twice with the same capitalization, the result is TRUE; an entry written “MAYA CHEN” does not count as an exact case-sensitive match to it.

This stricter check is usually not appropriate for ordinary contact lists, since capitalization differences rarely mean two different people. It is useful only when the text’s case is meaningful under your rules. As with other checks, decide how blanks should be handled before interpreting the output.

Interpret counts carefully before removing rows

A TRUE flag tells you that a name occurs more than once; it does not tell you how many extra records to delete or whether the records are otherwise identical. Two people can share a name, and two records with the same name may contain different phone numbers, departments, or dates. Review related columns before removing anything.

To show the number of matching entries for each name, use the count directly rather than comparing it with 1.

With names in A2:A20 and the formula entered in B2, use =COUNTIF($A$2:$A$20,A2). If “Liam Patel” appears twice, each matching row displays 2; a name appearing once displays 1.

There is an important difference between counting duplicate rows and counting distinct names that are duplicated. If one name occurs three times, the count formula shows 3 in all three rows, but there is only one distinct name in the repeated-name group. A formula flag is best for locating rows; a distinct summary requires grouping or a separate summary method.

Excel’s Remove Duplicates command is a different tool from a formula. It deletes repeated records based on the columns you select and keeps the first occurrence. Before using it, make a copy of the data or ensure you can undo the change. If you select only the name column, Excel may remove a same-name row even when other details differ. If the entire record must match, include all relevant columns in the selection.

Common formula problems

  • The formula marks every row: Check that the formula uses the full list as its COUNTIF range and the current row’s name as its criterion. A range that shifts while copying can produce misleading results; lock the range with dollar signs.
  • Nothing is marked: Look for extra spaces, nonprinting characters, or a range that stops before the repeated entry. Confirm that both copies are inside the range being checked.
  • Blank rows look duplicated: Use the AND version that checks A2 is not empty, or exclude blank rows from the selected range.
  • Only later copies should be flagged: Use the expanding range version rather than the full fixed range.
  • Names with symbols behave unexpectedly: COUNTIF treats the asterisk and question mark as wildcard characters in criteria. This is rarely relevant to ordinary names, but can matter for text that literally includes those characters.

Also verify that the formula was filled through every row, and that the source list does not contain a header within the checked data range. Filtering the helper column for TRUE is a straightforward way to review marked rows without deleting anything.

Frequently Asked Questions

Does the duplicate-name formula find every copy or only the second one? The full-range COUNTIF formula marks every occurrence in a repeated group, including its first occurrence. Use the running-range version if you want only entries after the first to be marked.

Does COUNTIF treat uppercase and lowercase names as different? No. COUNTIF is not case-sensitive, so “ALEX KIM” and “Alex Kim” count as matching text. Use the EXACT and SUMPRODUCT approach only if capitalization must matter.

Can I highlight duplicate names instead of showing TRUE or FALSE? Yes. Select the name range and create a conditional-formatting rule using a COUNTIF formula. The rule colors matching cells but leaves their values unchanged.

Why does Excel miss two names that look the same? They may contain different spaces, hidden characters, or spelling differences. Try cleaning ordinary spaces with TRIM and nonprinting characters with CLEAN, then inspect any remaining mismatches.

Will the formula delete duplicate names? No. It only calculates a result or applies formatting. Removing rows is a separate action; review the other fields and make a backup before using Remove Duplicates.

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.