To find names that match exactly—including capitalization and spaces—in Excel for Microsoft 365, put your names in column A and use a helper formula beside them: with names in A2:A100, enter =SUMPRODUCT(--EXACT(A2,$A$2:$A$100))>1 in B2, then fill it down. TRUE marks a name that appears more than once with the same characters. You can also highlight those cells with Conditional Formatting. Before you start, decide whether differences in capitalization or extra spaces should count as different names; the exact-match method treats them as different.
What You’ll Need
- Excel for Microsoft 365 on Windows, with the names in one column. The steps also work in recent desktop versions that support the functions used here.
- A copy of the workbook or a spare worksheet if you plan to remove duplicates. Finding them is safe, but deleting rows can affect other data.
- A clear definition of an exact match. This guide uses a strict, case-sensitive comparison: Jordan Lee, jordan lee, and Jordan Lee are different text values.
Step-by-Step
- Check that each full name is in a single cell. Put one complete name per row in a column, such as A2:A100, with a header in A1. Keep related information—such as an email address or customer number—in adjacent columns rather than separating the names from their records. If first and last names are in separate columns, decide whether you need to check duplicate first names, duplicate last names, or duplicate full-name combinations; these are different checks. Avoid selecting an entire worksheet if your list is small, because that makes reviewing the results harder.
- Keep an unchanged copy of the source list. Duplicate checks do not change your data, but filtering, sorting, or deleting can. Copy the worksheet or save a separate workbook before cleaning a list. This is especially important if other columns contain information that must stay matched to each person. If you only need to identify duplicates, use a helper column and leave the original names untouched.
- Add a helper-column heading. In B1, enter a label such as Exact duplicate? This makes it clear what the TRUE and FALSE results mean when you filter or share the sheet. If column B already contains data, choose another empty column and adjust the cell references in the instructions. The formula range should include all the name rows you want to compare, including the current row.
- Enter the exact-match formula in the first data row. With names in A2:A100 and the formula going in B2, enter
=SUMPRODUCT(--EXACT(A2,$A$2:$A$100))>1. EXACT compares text character by character and is case-sensitive; SUMPRODUCT counts the matching results, and the formula returns TRUE when that count is greater than one. The dollar signs keep the comparison range fixed when you copy the formula. For example, if A2 and A7 both contain Jordan Lee, the result in B2 is TRUE. If A7 contains jordan lee instead, that capitalization difference means it is not an exact match. - Fill the formula down the list. Select B2 and drag the small square at its lower-right corner down to the last name row, or double-click that square if the adjacent data column is filled throughout. Each row should compare its own name against the same fixed range A2:A100. Check the final formula row: the row reference should change as you fill down, while the comparison range should remain fixed. If your list has more or fewer rows, update the range in the formula so it covers the complete list.
- Filter to show only duplicates. Select a cell in your data, then choose Data > Filter. Open the filter arrow in the helper-column heading and select TRUE, clearing FALSE if needed. Excel will display only rows marked as exact duplicates. Keep the entire table selected when applying the filter so the name rows stay with their related details. To see every row again, open the same filter menu and choose Select All, or turn off the filter from Data > Filter.
- Optionally highlight exact duplicates without a helper column. Select A2:A100, then choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format. With names in A2:A100, enter
=SUMPRODUCT(--EXACT(A2,$A$2:$A$100))>1in the formula box. Choose Format, select a fill or font style, and confirm the dialogs. The reference A2 is relative so Excel checks each selected row; the range is fixed so every name is compared with the full list. This rule highlights all occurrences of a repeated exact name, not just the second and later entries. To remove the formatting later, select the range and use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. - Review the matching rows before changing anything. A TRUE result means the same text occurs at least twice in the selected range, but it does not tell you whether the records represent the same person. Compare other fields, such as an email or ID, before deciding what to keep. If you want to remove repeated records, use Data > Remove Duplicates only after making a backup and selecting the relevant table columns. Choosing only the name column may remove a row while leaving a different person or record out of your list.
Common Mistakes
Using a case-insensitive check when you mean exact. Excel’s common COUNTIF-based duplicate checks treat uppercase and lowercase letters as equivalent. That can be useful for finding likely duplicate names, but it does not enforce the strict character-for-character rule used here. EXACT is case-sensitive, so Sam Rivera and sam rivera are not counted as the same value.
Missing spaces or other invisible characters. A space at the end of a name is easy to overlook, but it changes the text. A nonbreaking space copied from a website can also look like an ordinary space while behaving differently. The strict formula correctly treats these as different values, which may make the duplicate count lower than expected. Inspect the source cells or clean the data in a separate column before deciding that two entries should match.
Using the wrong range or relative references. If the range stops at row 80 while names continue to row 100, later entries are not included in the comparison. If you omit the dollar signs, the comparison range shifts as you fill the formula down. Keep the list range fixed, expand it when the list grows, and make sure the first reference points to the name on the current row.
Removing duplicates too quickly. Two identical names can belong to different people. A duplicate name is a repeated text value, not proof that the underlying records are duplicates. Do not delete records solely because the name is repeated; check identifying details and preserve a backup first.
Tips
- Use a Table for a list that will grow. Select the data and choose Insert > Table. A table helps keep filters and formatting attached to the data as you add rows. After extending the list, verify that the comparison range in your helper formula covers the new rows; a fixed A2:A100 range will not automatically include a name entered in row 101.
- Keep a separate cleaned version. If the purpose is to identify names regardless of letter case or accidental spaces, make a cleaned helper column rather than changing the original values. That lets you compare a normalized version while retaining the original spelling for review. Be cautious about removing spaces inside names, since those spaces may be intentional.
- Use sorting to review groups. After filtering TRUE results, sort the name column alphabetically while keeping the entire table selected. Repeated entries will be easier to compare alongside their other details. Clear the filter before sorting if you want the full list in order.
- Choose the appropriate strictness. A case-sensitive exact check is helpful when the precise stored text matters. For ordinary name cleanup, you may instead want a case-insensitive comparison that ignores accidental leading and trailing spaces. Decide which differences matter before you delete or merge records; different rules answer different questions.
- On Mac or Excel for the web, look for the equivalent controls. The formula works in Microsoft 365 for Mac and Excel for the web. The Conditional Formatting dialogs or ribbon layout may look different, but the rule is still a formula-based rule applied to the selected range. Keyboard shortcuts also vary by platform, so use the ribbon paths above if a Windows shortcut does not match your setup.
Troubleshooting
The formula returns FALSE for names that look identical. Check for trailing or leading spaces, repeated spaces between words, and characters copied from another program. Click each cell and inspect the formula bar, or compare the values in a wider column. Since the strict formula is meant to distinguish even small text differences, an invisible character can prevent a match. If you want to treat those values as the same, clean a copy of the data first rather than silently changing the original list.
Every row returns FALSE. Confirm that the formula is in the correct row and refers to the actual name column. For a list in A2:A100, the formula in B2 must compare A2 with $A$2:$A$100. Check that the names are inside that range and that the formulas have recalculated. If Excel displays the formula as text instead of a result, change the cell format to General, then re-enter the formula. A leading apostrophe before the equals sign can also make Excel treat it as text.
Some duplicates are not highlighted. Select the intended range and open Home > Conditional Formatting > Manage Rules. Check that the rule applies to the whole name range, that the formula begins with the first cell in that range, and that its comparison range includes every row. If you selected A2:A100, the formula should refer to A2 as its starting cell. Another conditional-formatting rule may override the appearance, so review the rule list and its order.
The results change after adding names. A formula that compares against A2:A100 cannot see entries below row 100. Extend the fixed range in each formula and update the conditional-formatting rule’s Applies to range as well. If you converted the list to a Table, do not assume a manually entered fixed range has expanded; check the formula and rule after adding rows.
Excel shows an error instead of a result. Check that the formula begins with an equals sign, that the parentheses are balanced, and that the cell is not formatted as Text. Microsoft 365 in English uses commas between function arguments. If your regional settings require semicolons, Excel may expect semicolons instead; use the separator accepted by your installation. The function names and logic remain the same.
Frequently Asked Questions
Does this find duplicates that differ only in capitalization?
Not with the EXACT formula. It is case-sensitive, so Riley Chen and riley chen are treated as different entries. This is useful when you mean a strict text match. If capitalization should not matter, use a case-insensitive method instead and review the results before changing names.
Does Excel’s built-in Duplicate Values rule find exact, case-sensitive matches?
It can quickly highlight repeated values through Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, but it is not the best choice when capitalization must be distinguished. Use the formula-based conditional-formatting rule in this guide when you need a case-sensitive check.
Can I check duplicates across two columns?
Yes, but first define what a duplicate means. If first and last names are split into separate columns, you need to compare the combination of both fields rather than either column alone. Be careful when joining fields: use a separator that cannot be confused with part of a name, and keep the original columns for verification.
Will this formula find duplicate names in another worksheet?
Yes. The comparison range can refer to a different worksheet, provided the formula points to the correct range. When referencing a sheet whose name contains spaces, Excel requires the sheet name to be enclosed in single quotation marks in the reference. Confirm the reference carefully and test the formula on a known repeated name before relying on the full results.
Can I delete the duplicate rows after finding them?
Only after checking that the rows represent the same record, not merely people with the same name. Make a backup, select the full data table, and use Data > Remove Duplicates with the columns that define a genuinely repeated record. If name alone is selected as the duplicate criterion, distinct people with the same name may be removed.
Related
- Fill Color in Excel Using Keyboard Shortcuts on Windows
- How to Fill Half a Cell in Excel with Color Using Shapes
- Extract Text from an Image in Excel with Data from Picture
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Keep Leading Zeros in Excel as Text or NumbersTo keep a leading zero in Excel, format the cell as Text before entering the value, or type…
- Remove a Dropdown List in Excel Without Losing DataTo remove a dropdown list in Excel for Microsoft 365 on Windows, select the cell or cells, go…
- Change Dark View in Outlook: Steps for Every VersionTo change Outlook to dark view, open its appearance or theme settings and choose Dark. In classic Outlook…
- Microsoft Word Styles: How to Use ThemMicrosoft Word styles are reusable sets of formatting for text, paragraphs, lists, and other document elements. To use…
- How to Add a Shared Mailbox in Microsoft OutlookTo add a shared mailbox in Outlook, first ask your Microsoft 365 or Exchange administrator to grant your…
- Power Query Basics: Import, Clean, and Transform DataPower Query is Excel’s data-import and transformation tool: it connects to files, databases, and other sources, then lets…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.