To find duplicate names in Excel, select the name cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. For a count beside each name, with names in A2:A100 and the first result in B2, enter =COUNTIF($A$2:$A$100,A2); any result above 1 marks a repeated name.
What Excel Counts as a Duplicate
Excel compares the contents of cells, not whether two people are the same person. If the same text appears more than once in the chosen range, Excel can flag it as a duplicate. That makes the built-in feature useful for a quick scan of a contact list, attendee sheet, or customer export.
A matching name does not always mean a duplicate record. Two different people can share a name, while one person may appear under slightly different spellings. “Jordan Lee” and “Jordan Leigh,” for instance, are not an exact match to Excel, even if they refer to the same person. Conversely, two people named “Jordan Lee” will look identical if name is the only information in the selected cells. Treat a duplicate flag as a prompt to investigate, not proof that a row should be deleted.
Excel’s standard duplicate checks are generally not case-sensitive: names with different capitalization, such as “MORGAN” and “Morgan,” are treated as matches. Extra spaces or punctuation can affect whether entries match, so it is worth checking how the source data was entered before drawing conclusions.
Highlight Repeated Names with Conditional Formatting
For most lists, highlighting is the fastest approach because it marks repeats in place and leaves the data unchanged. In Microsoft Excel for Microsoft 365 on Windows:
- Select the name cells, such as A2:A100. Exclude the header so that a heading such as “Name” is not included in the check.
- Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- In the dialog, leave Duplicate selected, choose a formatting style, and select OK.
Excel applies a format to entries that occur more than once in the selected range. The selection matters: if you include unrelated columns, Excel checks those cells too, and a value in another field could be highlighted. If you want to check the whole name column, select only that column’s data cells or use a specific range.
Conditional formatting is a visual indicator, not a separate copy of the results. It updates when the underlying values change, and you can change or remove the rule through Home > Conditional Formatting > Manage Rules. On Excel for Mac, the Conditional Formatting controls are also on the Home tab, though the dialog layout can differ. In Excel for the web, use the Home tab’s Conditional Formatting menu; some dialog options may be arranged differently from the Windows desktop app.
Use COUNTIF to Count Each Name
A formula is more useful when you need to sort, filter, or report on duplicates. With names in A2:A100 and a count beside the first name in B2, enter =COUNTIF($A$2:$A$100,A2) in B2 and fill it down through B100. A result of 1 means the name appears once in the range; a result of 2 means it appears twice, and a larger number gives the total number of matching entries.
For example, if A2:A6 contains Ana, Bo, Ana, Chen, and Bo, the formula returns 2 for each Ana entry, 2 for each Bo entry, and 1 for Chen. The dollar signs keep the range fixed as you copy the formula down, while A2 changes to A3, A4, and so on. To label rows rather than show a count, with names in A2:A100 and the label formula in B2, use =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique"). For the example list, it labels Ana and Bo as Duplicate and Chen as Unique.
COUNTIF is not case-sensitive, so it will not distinguish names based only on capitalization. It also counts a name’s occurrence within the range you specify. If you count the entire column, the result may include a heading or other text elsewhere in that column; a defined data range is usually clearer and easier to audit.
Show a List of Names That Repeat
Sometimes you want a compact list of distinct names that occur more than once, rather than highlights or a count on every row. In Microsoft 365 or Excel 2021 and later, with names in A2:A100 and the output formula in C2, enter =UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1)). It returns each repeated name once and spills the results into cells below C2. With Ana, Bo, Ana, Chen, and Bo in the source range, the output is Ana and Bo.
Make sure the cells beneath the formula are empty so Excel has room to display the spilled results. If Excel shows a spill error, check for existing values or merged cells in the output area. This formula identifies repeated text values using the same case-insensitive matching behavior as COUNTIF. It is not available in older perpetual Excel versions such as Excel 2019; use conditional formatting, a helper column, or a PivotTable instead.
Find Duplicates When Names Are Not Consistent
Small formatting differences are a common reason a list appears to have no duplicates. Leading or trailing spaces, inconsistent spacing between words, punctuation, and spelling variations can prevent an exact match. For example, “Avery Chen” and “Avery Chen ” may look alike on screen but contain different text. A name entered as “Avery Chen” has two spaces between the words.
One cleanup option is to make a normalized helper column before checking for repeats. With raw names in A2:A100 and a cleaned version beginning in B2, enter =TRIM(A2) and fill it down. TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one. Then run Conditional Formatting or COUNTIF on the cleaned values. Keep the original column if you need to preserve the imported data or review what changed.
TRIM does not fix misspellings or every unusual character. Data copied from websites or other systems can contain nonbreaking spaces, which may look like ordinary spaces but behave differently. Punctuation and name order can also vary: “Lee, Jordan” and “Jordan Lee” will not become equivalent just by removing extra spaces. For large or recurring imports, agree on a consistent name format or compare additional fields, such as an email address or customer ID, before treating entries as the same person.
Check Duplicate Rows Before Removing Anything
If your goal is to remove repeated records, first decide what makes a record a duplicate. Selecting only the name column in a removal operation could delete two legitimate people who happen to share a name. For a contact table, a unique email or ID may be a safer matching field. If a duplicate means that every field in a row is identical, select all relevant columns when checking or removing duplicates.
To remove duplicates from a table in Excel for Windows, select a cell in the data, choose Data > Remove Duplicates, select the columns Excel should compare, and choose OK. Excel reports how many duplicate values were removed and how many unique values remain. Selecting more columns makes the test more specific: two rows with the same name but different email addresses are duplicates by name alone, but not duplicates across both columns.
Removal changes the data, so make a copy of the worksheet or workbook first if you may need the original rows. You can also use the count formula or conditional formatting to review candidates before removing anything. Remove Duplicates is not the same as highlighting: it deletes matching rows according to the selected columns rather than merely marking them. On Mac, the Remove Duplicates command is available from the Data tab, although its dialog may look different.
Choose the Method That Fits the Task
Each approach answers a slightly different question. Use a quick highlight when you need to spot repeats without changing the data. Use a count when you need to sort or filter records, and use a dynamic list when you need a shorter report of the repeated names.
- Conditional Formatting: Best for a quick visual check. It preserves the source data and updates as values change.
- COUNTIF helper column: Best for row-by-row counts, labels, sorting, and filtering. It works in older Excel versions as well as Microsoft 365.
- UNIQUE with FILTER: Best for a compact list of distinct repeated values in Excel 2021 or Microsoft 365.
- Remove Duplicates: Best only when you have decided which columns define a duplicate and intend to delete matching rows.
If the list is large or refreshed regularly, a repeatable cleanup process may be more reliable than checking it manually each time. A PivotTable can summarize how often each name appears, while Power Query can support repeatable data preparation. These options require a little more setup, so a conditional formatting rule or COUNTIF column is often simpler for a one-time list.
Common Problems and How to Check Them
If a name you expect to be flagged is not highlighted, first confirm that both entries are inside the selected range. Next, check for stray spaces, punctuation, spelling differences, or nonprinting characters. A formula that refers to A2:A100 will not find a match in A101 unless you extend the range.
If an entry is highlighted unexpectedly, check whether the same text occurs elsewhere in the selected cells, including hidden rows or cells below the visible list. Also confirm that the rule applies to the intended range: choose Home > Conditional Formatting > Manage Rules and inspect the Applies to field. If your count formula shows 1 for every row, make sure its range includes all the data and remains fixed as the formula is filled down.
For names that must match exactly, consider checking a stable identifier instead of relying on spelling and capitalization. If capitalization itself is meaningful for your task, ordinary COUNTIF is not a case-sensitive test; use a deliberately designed exact-match method and verify it on sample data before applying it to the whole list.
Frequently Asked Questions
Does Excel have a built-in duplicate finder?
Yes. Select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values in the selected range without deleting them.
How do I find duplicate names without deleting them?
Use Conditional Formatting or a COUNTIF helper column. Both identify repeated names while leaving the original rows in place, so you can review the results before deciding what to do.
Can Excel highlight duplicates automatically?
Yes. A conditional formatting rule updates when values in its applied range change. If you add rows outside that range, extend the rule’s range or apply it to an appropriately sized range or table.
Why is Excel not finding a duplicate name?
The entries may differ because of spaces, spelling, punctuation, or characters copied from another source, or one entry may be outside the selected range. Compare the underlying text and check the rule or formula range.
How do I find duplicates based on name and email?
Select or compare both the name and email columns when checking for duplicate records. Matching on both fields is more specific than checking names alone, but confirm that those fields together reflect your definition of a duplicate.
Related
- Excel Custom Number Format: Show Numbers in Thousands
- Excel Shortcut to Select the Rest of a Column Down
- Excel Shortcut to Select an Entire Column
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Can You Add a Dropdown List in Excel? Yes, Here’s HowYes. In Excel for Microsoft 365 on Windows, select the cell or cells, choose Data > Data Validation…
- Outlook Not Letting You Create Rules? Causes and FixesIf Outlook will not let you create a rule, first check which Outlook version and account you are…
- Create One Named Range from Multiple Cell Ranges in ExcelYes. In Excel for Microsoft 365 on Windows, you can create one defined name that refers to several…
- How to Print Timestamps from Google Sheets ClearlyTo print timestamps from Google Sheets, format the cells as date and time, select the sheet or range…
- How to Make a Budget Tracker in Google SheetsMake a monthly budget tracker in Google Sheets by listing expense categories, entering planned and actual amounts, and…
- Excel Freeze Panes: Change Them While Scrolling?No—Excel does not change the Freeze Panes boundary automatically as you scroll. To move it, unfreeze the current…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.