To find duplicate names across Excel tabs, put the names in the same column on each worksheet and use a helper formula such as COUNTIF to check one tab against another; for a reusable check across many tabs, combine the lists with Power Query. Excel does not allow two worksheets in one workbook to have identical tab names, so this usually means finding repeated names in the cells on different sheets.
What “duplicate names in tabs” can mean
There are two different things a reader might mean by duplicate names in Excel tabs. A worksheet tab is the labeled tab at the bottom of the window, such as “East List” or “West List.” Excel requires worksheet names within the same workbook to be unique. If you try to create another sheet with a name already in use, Excel asks for a different name. You can still have worksheets with the same name in separate workbooks.
More often, people want to compare names typed into worksheet cells: for example, customer names on an “East List” sheet and a “West List” sheet. The methods below find duplicates in that data. Decide first whether you want to detect a name that appears on both tabs, repeats more than once on a single tab, or does either. That choice determines the right check.
Compare two worksheets with COUNTIF
A helper column is a straightforward way to check whether every name on one worksheet appears on another. This example assumes names are in A2:A100 on both sheets, named East List and West List. On East List, enter this in B2, then fill it down through B100:
=IF(A2="","",IF(COUNTIF('West List'!$A$2:$A$100,A2)>0,"Found on West List",""))
If A2 contains Maya Chen and that name appears anywhere in West List!A2:A100, the result in B2 is “Found on West List.” If it does not appear, B2 stays blank. The formula also leaves a blank result when A2 is empty, rather than labeling empty rows as matches. To check the other direction, put the corresponding formula in a helper column on West List and point COUNTIF at East List.
This checks whether a name occurs at least once in the other range. It does not report how many times it occurs there. If the name appears three times on West List, the result is still the same “Found on West List” message. For a simple yes-or-no cross-tab check, that is often enough.
Flag every repeated occurrence across two tabs
To flag a name whenever it occurs more than once across the two lists—including repeats within either list—use a combined count. With names in A2:A100 on both East List and West List, enter this in B2 on East List and fill down. Use the same check on West List, changing the row reference as needed:
=IF(A2="","",IF(COUNTIF('East List'!$A$2:$A$100,A2)+COUNTIF('West List'!$A$2:$A$100,A2)>1,"Duplicate",""))
For example, if “Maya Chen” appears once on each tab, the combined count is 2 and the formula returns “Duplicate” for both rows. If a name appears once in total, it remains unmarked. If it appears twice on East List and nowhere on West List, both East List rows are also marked. The formula compares text without distinguishing uppercase from lowercase, so “MAYA CHEN” and “Maya Chen” count as a match.
COUNTIF treats the asterisk (*) and question mark (?) as wildcard characters in its criteria. If names may contain those characters and you need them treated literally, verify those cases rather than assuming the basic COUNTIF check is an exact character-for-character comparison.
Highlight matches with conditional formatting
Once the helper column identifies matches, you can make them easier to scan. On East List, select A2:A100, then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Assuming the helper results are in B2:B100, use this rule formula:
=$B2="Duplicate"
Choose a fill or font style in the formatting options and confirm the rule. The dollar sign fixes the helper-column reference to column B, while the row number remains relative so Excel can check the appropriate helper result for each selected name. If you used the “Found on West List” formula instead, change the text in the rule to match that result. Conditional formatting changes the appearance of matching cells; it does not remove entries or change their values.
You can also filter the helper column to show only “Duplicate” or “Found on West List.” This is useful when the lists are long and you want to review the flagged records without scrolling through every row. Keep the original data unchanged until you have checked whether the matches are truly redundant.
Find repeats within one worksheet
If the question is whether the same name appears multiple times on a single tab, compare each cell with the full list on that sheet. With names in A2:A100 on the current worksheet, enter this in B2 and fill down:
=IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate",""))
If “Jordan Lee” appears in A8 and A23, the count for each of those rows is 2, so both return “Duplicate.” The formula flags all occurrences, not just the later one. If you want to highlight the original and later entries together, this behavior is helpful. If instead you want to identify only later repeats, a different running-count approach is needed.
A repeated name is not always a bad record. A customer can legitimately have multiple transactions, or two people can share a name. Treat the flag as a prompt for review, not proof that one row should be deleted. Compare an additional identifier—such as an account number or email address—before deciding that two records represent the same person.
Check several tabs with a combined list
For a small number of tabs, you can adapt the combined COUNTIF formula by adding another COUNTIF for each worksheet. With many tabs, a consolidated list is easier to maintain. In Microsoft 365 or Excel 2024, VSTACK can combine ranges, and UNIQUE and FILTER can return names that occur more than once. The following example assumes names are in A2:A100 on East List and West List; enter it in an empty cell outside those source ranges:
=LET(combined,VSTACK('East List'!A2:A100,'West List'!A2:A100),distinct,UNIQUE(FILTER(combined,combined<>"")),FILTER(distinct,COUNTIF(combined,distinct)>1,"No duplicates"))
The result spills into the cells below and lists each repeated name once. If “Jordan Lee” occurs three times in the combined ranges, it appears once in the result. If there are no repeats, the formula returns “No duplicates.” Leave enough empty space below the formula for the results to spill, or Excel may show a spill error. This formula uses VSTACK, which is available in Microsoft 365 and Excel 2024; it is not a formula for Excel 2021 or older perpetual versions.
To include another tab, add its range as another VSTACK argument. For a workbook where sheets or row counts change frequently, this formula can become cumbersome; it also checks only the ranges specified. Expand the ranges or use Excel Tables so that new records are included in a way suited to your workbook.
Use Power Query for repeatable comparisons
Power Query is a practical choice when you routinely compare larger lists or several worksheets. It can combine structured tables into one result and refresh that result after the source data changes. First, make each list an Excel Table using Insert > Table, with a clear header such as Name. For each table, select a cell and choose Data > From Table/Range to open it in the Power Query Editor.
- In each query, add a source label, such as East List or West List, so the final output can show where a record came from.
- In the editor, choose Home > Append Queries as New and append the list queries into one combined query.
- Select the Name column and choose Home > Group By. Group by Name and set the operation to Count Rows.
- Filter the count to values greater than 1. The remaining names are those that occurred more than once in the combined data.
After loading the result to a worksheet, use Data > Refresh All when the source tables change. Power Query is more setup than a formula for two short lists, but it avoids manually extending a long formula every time another source is added. Before relying on the output, check that the queries include the intended columns and that the source labels have not been mistaken for part of the name data.
Clean the names before deciding they match
Names that look identical on screen may contain extra spaces or inconsistent typing. “Jordan Lee” and “Jordan Lee ” can behave as different text values in exact comparisons because the second entry has a trailing space. Differences in middle initials, punctuation, accents, or spelling can also prevent a match. Conversely, the basic COUNTIF method is not case-sensitive, so capitalization alone does not distinguish two entries.
Inspect flagged records and, where appropriate, standardize the source data before comparing. You can remove leading and trailing spaces with Excel’s TRIM function in a temporary helper column. With a name in A2, enter =TRIM(A2) in the helper cell and fill down. This removes extra spaces at the beginning and end and reduces repeated spaces between words to single spaces. It does not correct misspellings or establish that two people with similar names are the same person.
For a dependable matching process, retain the original names and use a stable identifier when one exists. A name-only comparison is useful for spotting possible duplicates, but it can produce false matches when different people share a name and miss records when the same person’s name was entered differently.
Choose the method that fits the job
Use a simple COUNTIF helper formula when you have two tabs and want a clear match indicator. Use conditional formatting when visual review is the priority, or filter the helper results when you need a focused list. Use a combined dynamic-array formula for a quick summary of repeated values across a couple of fixed ranges in supported Excel versions. Choose Power Query when the task is recurring, involves multiple tables, or needs a refreshable combined output.
Whichever method you choose, make a copy of the workbook before deleting or merging records. A duplicate flag answers “Does this value appear more than once in the ranges I checked?” It does not determine which record is correct, whether two people are the same, or whether a repeated row should be removed.
Frequently Asked Questions
Can two Excel worksheet tabs have the same name?
No. Worksheet names must be unique within a workbook. You can use the same worksheet name in separate workbooks, but not for two tabs in one workbook.
How do I find a name that appears on two different sheets?
Use COUNTIF in a helper column on one sheet to check its name against the other sheet’s name range. A result such as “Found on West List” marks names that occur at least once in that range.
Does COUNTIF treat uppercase and lowercase as different?
No. COUNTIF is not case-sensitive for this text comparison, so “Alex Kim” and “ALEX KIM” are counted as matches. Extra spaces or spelling differences can still prevent a match.
Can I highlight duplicate names without deleting them?
Yes. Use a helper formula and apply Home > Conditional Formatting to the names, or use a conditional-formatting rule that refers to the helper results. Highlighting marks cells but leaves the data intact.
Why does my duplicate check miss names that look the same?
Check for leading or trailing spaces, spelling variations, punctuation, and different initials. You can use TRIM in a helper column to remove extra spaces, then review the cleaned values before changing the original data.
Related
- Excel Shortcut to Insert a Table Row on Windows and Mac
- Excel Shortcut to Insert a Copied Row
- Excel Shortcut for Inserting Rows Above or Below
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Clear Cache on the Outlook App: Safe Steps by DeviceTo clear the Outlook app’s cache, use the method for your device: on Android, open Settings > Apps…
- Remove a Page Break in Excel: Manual and AutomaticTo remove a manual page break in Excel for Microsoft 365 on Windows, select the cell immediately below…
- Excel File Too Large for No Reason? Find the Hidden DataAn Excel file that seems too large usually contains data or workbook features you cannot see at a…
- Excel Freeze Panes: Keep Rows and Columns VisibleTo freeze panes in Excel for Microsoft 365 on Windows, select the cell just below the rows and…
- Excel Not Highlighting Cells When Selected? FixesIf Excel is not visibly highlighting a selected cell, first press Esc to leave edit mode, then click…
- How to Make Columns in a Word Document, Step by StepTo make columns in a Word document, place your cursor in the text or section you want to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.