Find Duplicate Names in Excel: A Step-by-Step Guide

How-to basics · Updated November 20, 2026

To find duplicate names in Excel, select the name cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights every repeated name in the selected range. If you want a count, a filterable flag, or to highlight entire rows, use a COUNTIF formula instead. Before you begin, decide whether you mean identical cell entries or people who may have different spellings of the same name.

What You’ll Need

  • An Excel workbook with the names you want to check, ideally with one name per cell in a single column.
  • A header row, if the list has a column heading, so you can select the names without including the heading.
  • Microsoft Excel for Microsoft 365 on Windows for the ribbon paths below. Excel for Mac and Excel for the web have similar conditional formatting and formula options, though some dialogs may look different.

You do not need to install an add-in. Save a copy before removing or reorganizing records; finding duplicates is safe, but deleting rows can affect related data.

Step-by-Step

  1. Check how the list is arranged. Find the column containing the names and note the first and last data rows. For example, if A1 is the heading Name and the entries run from A2 through A100, the range to check is A2:A100. Do not include the heading, because it could be treated as a name. If the list is an Excel table, click its name column and check that the selection excludes the heading and any total row. When you select a range manually, click the first name cell, then hold Shift and click the last one, or drag down the column.
  2. Use Excel’s built-in duplicate highlighting for a quick visual check. With the name cells selected, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the Duplicate Values dialog, leave Duplicate selected, choose a formatting style, and click OK. The selected cells that have matching entries will be highlighted. This marks all occurrences of a repeated value, not just the later copy. To change or remove the highlight later, select the range and use Home > Conditional Formatting > Clear Rules; choose Clear Rules from Selected Cells if you only want to clear that selection.
  3. Review what Excel considers a match. The built-in rule compares cell values without regard to capitalization, so names such as “Jordan Lee” and “JORDAN LEE” are treated as duplicates. It does not decide that “Jon Smith” and “John Smith” are the same person, and it will not normally treat a name with an extra space as identical to the trimmed version. Scan the highlighted cells in context before deciding whether any records refer to the same person.
  4. Add a helper column if you need a result you can filter or count. Suppose names are in A2:A100 and you want a TRUE/FALSE flag beside each name. Enter this in B2: =COUNTIF($A$2:$A$100,A2)>1. Fill it down through B100 by dragging the fill handle or double-clicking it. This returns TRUE for every occurrence of a name that appears more than once, including the first occurrence; it returns FALSE for a name that appears once. The dollar signs keep the lookup range fixed while the reference to the name on the current row changes.
  5. Exclude empty cells if the list has blanks. In the same layout, with names in A2:A100 and the flag in B2, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) and fill down. This prevents blank cells from being flagged as duplicate names. The formula is useful when a range includes unused rows below the current list. If a cell appears empty but still gets flagged, it may contain a space or another invisible character rather than being truly blank; see Troubleshooting.
  6. Filter to the repeated entries. If the list has headers, click a cell in the data and choose Data > Filter. Use the drop-down arrow in the helper column’s heading and select TRUE, leaving only rows marked as duplicates. You can then inspect each group, compare any other columns such as email address or customer number, and decide which records need attention. Clear the filter afterward to restore the full list. On Mac, use the Data tab’s Filter command; in Excel for the web, the Filter command is also on the Data tab.
  7. Highlight entire records when that is easier to review. To color a whole row based on the name in column A, select the data rows, for example A2:D100, and go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With names in A2:A100 and the selected rows beginning at row 2, enter =COUNTIF($A$2:$A$100,$A2)>1, choose a format, and confirm it. Column A and the lookup range are anchored so the rule checks the name in each row while applying the format across the selected columns. To leave blank-name rows unhighlighted, use =AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1) instead. In Excel for Mac or the web, use the conditional-formatting rule option for a formula; the dialog layout can differ slightly.

For example, if A2 contains Maya Chen, A3 contains Luis Ortiz, and A4 contains Maya Chen, the built-in rule highlights A2 and A4. The helper formula returns TRUE beside both Maya entries and FALSE beside Luis. That is expected: duplicate detection identifies every member of a repeated group, not only the second entry.

Common Mistakes

  • Including the heading in the selection. Select only the name values. Including the header can make it part of the comparison and can also make later filtering less clear.
  • Assuming capitalization makes a different name. The built-in duplicate rule and COUNTIF are not case-sensitive. If capitalization is meaningful for your task, these methods do not separate entries based on case.
  • Confusing repeated text with duplicate people. Two people can share the same name. A name match is a reason to review the records, not proof that one record should be deleted. Compare a reliable identifier, such as an email address or account number, where appropriate.
  • Checking only the first match or only later matches. The built-in rule and the formula above flag all occurrences. That is useful for review, but it does not by itself tell you which row to keep.
  • Using Remove Duplicates too soon. Data > Remove Duplicates deletes records from the selected data range and is not just a highlighting tool. Do not use it until you have checked the records, selected the correct columns, and saved a copy. If you remove duplicates based on the name column alone, Excel can remove a row even when other details differ.
  • Overlooking extra spaces and spelling variations. A leading or trailing space can make an entry look the same to a person but behave differently in a comparison. Differences such as “Ann Marie” and “Anne-Marie” require review or a clearly defined cleanup rule; ordinary duplicate highlighting will not infer that they refer to one person.

Tips

  • Use a helper column for an auditable check. A conditional format can be easy to miss or may be cleared accidentally. A TRUE/FALSE column is visible, filterable, and can be checked alongside other record details. Label it something clear, such as Duplicate name?
  • Keep the range current. A formula that ends at row 100 will not check names added in row 101. Extend the range when the list grows, or convert the list to a table so it is easier to maintain. If you use a table, confirm that any conditional formatting rule’s Applies to range includes new rows.
  • Make a clean copy before normalizing text. You can use a separate cleanup column to remove ordinary leading and trailing spaces. With the original names in A2:A100, enter =TRIM(A2) in B2 and fill down; compare the cleaned values in column B rather than overwriting the originals immediately. TRIM removes extra regular spaces between words as well as leading and trailing regular spaces, so inspect results if spacing is meaningful. It does not remove every kind of nonprinting character.
  • Watch for wildcard characters in COUNTIF. COUNTIF treats an asterisk (*) and question mark (?) in the criterion as wildcards. This rarely affects ordinary names, but a name containing one of those characters can match more broadly than expected. If that applies to your data, verify matches carefully rather than trusting the helper flag alone.
  • Choose the simplest method that answers your question. Use the built-in rule for a quick visual scan, a helper formula to filter and count, or a formula-based conditional-formatting rule to highlight full records. You can use more than one method to cross-check an important list.

Troubleshooting

Nothing is highlighted. Confirm that you selected the actual name cells and that the duplicate rule is applied to the intended range. Check for small text differences, such as an extra space, punctuation, or a middle initial. Also check whether a filter or hidden rows are making part of the list difficult to see. Reapply the built-in rule to a small test range containing two identical entries to distinguish a selection problem from inconsistent source data.

Entries that look identical are not flagged. They may contain nonbreaking spaces, line breaks, or other invisible characters copied from a website or database. Try comparing a cleaned copy, but remember that TRIM handles ordinary spaces and may not remove every character. If necessary, inspect the source data or use Excel’s Find and Replace carefully on a copy. Do not run a broad replacement on the original list without checking what it will change.

Too many results are flagged. Check whether blank-looking cells actually contain spaces, and verify that the selected or formula range includes only the intended names. COUNTIF also treats * and ? as wildcards. If the flag formula is in a table or a different column layout, confirm that it refers to the name column and that the row reference changes for each record.

The helper formula shows an error or the same result on every row. Check that the formula begins with an equals sign, the parentheses are balanced, and the formula was entered in the intended row. The examples use comma separators; some regional Excel settings require semicolons instead. If you changed the formula’s range, make sure the end row covers the complete list. After correcting the first formula, fill it down again.

Formatting appears in the wrong rows. Open Home > Conditional Formatting > Manage Rules and inspect the rule’s formula and Applies to range. For a rule applied to A2:D100, the row reference should start at row 2 and move as the rule evaluates each row. A fixed reference such as $A$2 in the criterion would make every row check only the first name, which is usually not what you want.

Frequently Asked Questions

Does Excel highlight every copy or only the second one?
The built-in Duplicate Values rule highlights every occurrence of a repeated value in the selected range. The COUNTIF formula shown here does the same. This makes it easier to review the entire group, but you must decide separately which records, if any, should be changed.

Can I find duplicate names across two columns?
Yes. Put the names into one comparison range or create a combined list in a separate area, then apply the duplicate rule or COUNTIF to that combined list. If you need to compare corresponding rows in two columns rather than search both columns as one list, define that comparison carefully; the single-column examples do not make that distinction automatically.

Does Excel treat uppercase and lowercase names as different?
No. The duplicate rule and COUNTIF comparison are generally case-insensitive, so “MAYA CHEN” and “Maya Chen” count as a match. They will not tell you whether capitalization differences matter for a particular data system.

Can I remove the duplicates after finding them?
Yes, but review the records first and save a copy. Data > Remove Duplicates removes rows from the selected range; it does not simply hide or mark them. Choose the columns that define a true duplicate, because selecting only Name may remove distinct people who share a name.

Will the duplicate check update when I add names?
A conditional-formatting rule or formula only checks the range it covers. If you add rows outside that range, extend the range or update the rule and formula. An Excel table can make expanding data easier to manage, but check that the rule and helper formula include new entries.

Can Excel tell whether two different spellings are the same person?
Not with the basic duplicate rule or COUNTIF method. They compare cell values rather than identify people. Review likely spelling variations using additional information, and apply a consistent matching policy before editing records.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.