To find duplicate names in Excel, select the name cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. For more control, use COUNTIF to flag repeats, count occurrences, or ignore blank cells before reviewing the results.
Highlight duplicate values with the built-in rule
For a quick visual check in Microsoft Excel for Microsoft 365 on Windows, select the cells containing names, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, keep Duplicate selected, choose a highlight style, and click OK. Excel marks each value that occurs more than once in the selected range, making repeated entries easy to scan without changing the data.
For example, if names are in A2:A100, select A2:A100 before applying the rule. Selecting only the data cells avoids formatting the header. The same general command is available in Excel for Mac; the dialog’s appearance can vary slightly. If the names are in a formatted Excel table, select the table’s name column rather than the whole worksheet.
- Best for: quickly spotting repeated cell values.
- It does not: remove duplicates, correct spelling, or identify near-matches such as “Jon Smith” and “John Smith.”
- To change or remove the highlight: use Home > Conditional Formatting > Manage Rules, or choose Clear Rules to remove the formatting rule.
Use COUNTIF to flag or count repeated names
A formula is useful when you want a separate result column, need to filter the list, or want a rule that excludes empty cells. With names in A2:A100, enter this formula in a conditional formatting rule applied to A2:A100:
=COUNTIF($A$2:$A$100,A2)>1
To create the rule, select A2:A100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter the formula, choose a format, and confirm. The dollar signs keep the checked range fixed as Excel evaluates each row; A2 remains relative so the rule checks the current cell. Any name found at least twice is highlighted, including its first occurrence as well as later ones.
To show the number of times each name appears, use a helper column. With names in A2:A100 and the first count formula in B2, enter:
=COUNTIF($A$2:$A$100,A2)
Fill the formula down column B. A result of 1 means that value appears once; 2 or more indicates a repeated value. You can then turn on filters with Data > Filter and filter the count column to show values greater than 1. COUNTIF is not case-sensitive, so names that differ only by capitalization are generally counted together.
COUNTIF also counts blank cells when its criterion refers to a blank cell. If several empty rows are included in the range, a basic conditional-formatting formula can therefore flag them as repeats. To exclude blanks, with names in A2:A100 and the conditional formatting rule applied to A2:A100, use:
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)
Check duplicates using first and last names
If first names and surnames occupy separate columns, decide what counts as a duplicate before comparing. Two people named Alex Lee may be different records, while a repeated combination of first name and surname may be the issue. You can check both columns together instead of treating a repeated first name alone as a duplicate.
With first names in A2:A100 and last names in B2:B100, enter this formula in C2 and fill it down to identify repeated first-and-last-name combinations:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1
TRUE means the same pair appears more than once in the two columns. If you use a single combined name column, joining first and last names can help with display, but it is not always a safe way to identify people: spacing, missing components, and different name orders can cause confusion. When the workbook contains a unique customer, employee, or student ID, use that identifier to confirm whether two rows refer to the same person. A shared name alone does not prove that records describe one individual.
Clean spaces and invisible characters before comparing
Names that look identical can be stored differently. A trailing space in “Maya Chen ”, a nonbreaking space copied from a web page, or a control character from an imported file can make a direct comparison behave unexpectedly. Conversely, a cleanup step can make two entries match when they differ only in such formatting noise.
With the original name in A2, enter the following cleanup formula in B2 and fill it down. It replaces a common nonbreaking space with a regular space, removes many nonprinting control characters, and trims leading and trailing spaces while reducing repeated ordinary spaces:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
Then apply the duplicate rule or COUNTIF formula to the cleaned values in column B, adjusting the range to match the rows. Keep the original column until you have checked the results. CLEAN does not remove every possible Unicode character, and TRIM is not a universal name-normalization tool; unusual imported text may need separate investigation. Do not automatically remove punctuation, accents, or middle names unless that matches the rules for your data, because those details can distinguish legitimate records.
Review results before removing duplicates
Highlighting duplicates is different from deleting them. Excel’s Remove Duplicates command removes rows, so review the records and preserve a backup before using it. On Windows, select the data and choose Data > Remove Duplicates. In the dialog, check the columns Excel should compare, then confirm. If your data has headers, make sure My data has headers is selected.
The selected columns determine what Excel considers a duplicate. If you select only the Name column, rows with the same name may be treated as duplicates even when their email addresses or IDs differ. If you select every column, two rows with the same name but different values elsewhere will usually remain because the complete rows are not identical. Choose columns that reflect the actual record-matching rule, not simply the easiest column to select.
- Save a separate copy of the workbook before removing rows, especially if the file is important or shared.
- Sort or filter the highlighted records and compare relevant fields such as email, date, or ID.
- Decide which record to keep when repeated rows contain different information; Excel cannot determine which entry is correct.
- After removal, check Excel’s result message and verify that the remaining row count and records make sense.
Excel’s Undo command may reverse a recent removal, but it is not a substitute for a backup, particularly after other edits or after closing the workbook. Excel for Mac has a Remove Duplicates command as well; the ribbon layout can differ by version, but look for it on the Data tab.
Exact duplicates, similar names, and false matches
A standard duplicate check compares cell values; it does not understand identity. “Sam Taylor” and “Samuel Taylor” are different text, even if they refer to one person. “Ana Ruiz” and “Ana Ruis” may be a typo, but Excel will not infer that. On the other hand, two different people can genuinely share exactly the same name. Treat highlighted entries as candidates for review rather than proof that a record should be deleted.
When you need to find likely spelling variants, start by cleaning spaces and standardizing consistent fields such as capitalization or punctuation only when appropriate. Then compare other identifiers—such as an email address, phone number, or internal ID—and review possible matches manually. Fuzzy matching is a different task from finding exact duplicate names and may require a specialized tool or carefully defined rules. Do not merge records just because their names look similar.
Choose the method that fits your task
The right approach depends on whether you are exploring a list, preparing a report, or cleaning a dataset. Conditional formatting is usually the fastest way to inspect a short list because it leaves the source values untouched. A COUNTIF helper column is more useful when you need to sort or filter repeated values, exclude blanks, or keep a repeat count for an audit. COUNTIFS is appropriate when a duplicate means a repeated combination of fields.
For a one-time cleanup, Remove Duplicates can be efficient after you have chosen the comparison columns and saved a copy. For recurring imports, retain a helper column or repeatable data-cleaning steps so the same rule is applied consistently each time. If a workbook is used to make decisions about people, include a human review step and use a reliable identifier where available.
Common problems and practical checks
- The header is highlighted: exclude the header from the selection or ensure the formula range begins on the first data row.
- Nothing is highlighted: check that the applied range and formula references start on the same row, and check for extra spaces or hidden characters.
- Blank rows are marked: use the AND formula above to exclude empty cells, or narrow the range to the rows containing data.
- Different people are flagged: identical names are not unique identifiers. Check another field before deleting a record.
- A duplicate is missed: spelling differences, initials, spacing, or alternate name formats may make entries non-identical. Clean cautiously and review likely variants.
Also check whether the data has been imported as text or contains formulas that return empty strings. A cell that looks empty may not be truly empty, and a displayed name can differ from its underlying value. Click a questionable cell and inspect the formula bar before changing the comparison. After making corrections, reapply or refresh your review and confirm the final list rather than assuming the formatting alone has fixed the data.
Frequently Asked Questions
How do I highlight duplicate names in Excel?
Select the name cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a format and click OK. The rule highlights repeated values but does not delete them.
How do I find duplicate names without counting blank cells?
Use a conditional formatting formula that checks both that the current cell is not empty and that its COUNTIF result exceeds 1. For names in A2:A100, apply =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) to that range.
Can Excel find duplicates using first and last name together?
Yes. Use COUNTIFS to test both columns in the same row against the full first-name and last-name ranges. This flags repeated pairs instead of flagging everyone who shares only a first name.
Does Remove Duplicates delete every row with a repeated name?
No. It keeps one instance of each set of values defined by the columns you selected and removes the other matching rows. Check the selected columns carefully, because selecting only Name can remove records that differ in other important fields.
Why does Excel miss names that look the same?
Extra spaces, nonbreaking spaces, hidden characters, and spelling differences can make two displayed names compare differently. Clean common whitespace problems, then compare the values again and verify questionable matches using other fields.
Related
- Excel Unmerge Cells and Fill Every Cell with Its Data
- Excel Undo Shortcut: Ctrl+Z and How It Works
- Excel UK Number Format: Dates, Currency and Separators
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Weekday From Date: Formulas That WorkTo get the weekday for a date in Excel, use =WEEKDAY(A2,2) when the date is in cell A2;…
- Excel Count If Not Blank: Formulas and Common TrapsTo count cells that are not blank in Excel, use =COUNTA(A2:A100), replacing the range with your data. If…
- VLOOKUP Formula to Compare Two Sheets in Excel EasilyTo compare values on two Excel sheets by a shared ID, enter this formula on the first sheet,…
- Excel OFFSET Height: How the Argument Works in FormulasIn Excel, OFFSET’s height argument sets how many rows the returned reference includes; it does not control how…
- Excel INDEX Function: Chinese Data, Syntax, and ExamplesIn Excel, INDEX returns a value from a specified position in a range, and it works with Chinese…
- Excel LEFT Function for Dates: What It ReturnsExcel’s LEFT function can take a date, but it does not read the date as it appears on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.