How to Find Repeated Names in Excel with COUNTIF Formula

To find repeated names in Excel, put names in A2:A100, then enter =COUNTIF($A$2:$A$100,A2) in B2 and fill it down. Each result shows how many times that name appears in the list; any result greater than 1 marks a repeated name. Before you start, decide whether you want to count repeated rows, distinct names that repeat, or extra copies beyond each name’s first occurrence—these are different totals.

What You’ll Need

A list of names in one column, with one name per cell. The steps below assume the names are in A2:A100 and row 1 contains a heading.
A blank column beside the list for the count or duplicate flag. If your list is longer or shorter, adjust the formula ranges to fit.
Microsoft Excel for Microsoft 365 on Windows. The COUNTIF and IF formulas used here also work in many earlier Excel versions, Excel for Mac, and Excel for the web, though some ribbon details can vary slightly.

Step-by-Step

Check and prepare the list. Make sure each name is in its own cell in a single column. Keep the heading out of the formula range: if the heading is in A1 and the first name is in A2, use A2 as the start. Remove completely empty rows within the list if practical, and check that the values are really names rather than formulas returning unexpected spaces. Save a copy before making major changes, especially if you plan to sort or edit the source data.
Choose what “number of repeated names” means for your task. A list with Ana, Ben, Ana, Ana, and Dee contains one distinct repeated name: Ana. It has three rows belonging to a repeated-name group, because all three Ana entries match. It also has two extra copies beyond the first Ana. Excel can calculate each of these totals, but the answer differs depending on which meaning you need. If you need to review individual names first, start with a count beside every row.
Count each name’s occurrences. With names in A2:A100 and the count helper column in B, select B2 and enter =COUNTIF($A$2:$A$100,A2). Press Enter. The dollar signs keep the full list range fixed as you copy the formula, while A2 changes to A3, A4, and so on. A name appearing once returns 1; a name appearing three times returns 3. In the example list, each Ana row returns 3, while Ben and Dee each return 1.
Fill the count formula down the list. Select B2, then drag the small fill handle at the lower-right corner of the cell down to B100. You can also copy B2, select B3:B100, and paste. In Excel for Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. If the list is an Excel table, entering the formula in the first data row may automatically fill the rest of that table column. Check the last few rows to make sure the formula reached the bottom of your data.
Flag repeated entries so they are easy to scan. With names in A2:A100 and their counts in B2:B100, enter =IF(B2>1,"Repeated","") in C2, then fill it down through C100. The word Repeated appears beside every row whose name occurs more than once; cells for names appearing once stay blank. This is a visual label, not a new count. You can filter column C to show only Repeated, or sort the table by the count column to bring the larger groups together.
Get the number of rows that belong to repeated-name groups. With occurrence counts in B2:B100, enter =COUNTIF(B2:B100,">1") in an empty cell. The result is the number of list rows whose name occurs more than once. For Ana, Ben, Ana, Ana, and Dee, it returns 3, because the three Ana rows each have a count greater than 1. This does not mean there are three distinct duplicate names; it means three entries are part of a repeated group.
Count each distinct repeated name once, if that is the number you need. With names in A2:A100, enter this formula in D2 and fill it down through D100: =IF(A2="",0,IF(AND(COUNTIF($A$2:$A$100,A2)>1,COUNTIF($A$2:A2,A2)=1),1,0)). The first condition excludes blank cells. The next checks that the name occurs more than once in the full list; the final check gives a 1 only to its first appearance. For Ana, Ben, Ana, Ana, and Dee, D contains a single 1 for Ana and zeros for the other rows. With the names in A2:A100 and these first-occurrence markers in D2:D100, enter =SUM(D2:D100) in an empty cell to get the distinct repeated-name total, which is 1 in this example.
Count extra copies beyond the first, if useful. With names in A2:A100, enter =IF(A2="",0,IF(COUNTIF($A$2:A2,A2)>1,1,0)) in E2 and fill down. This marks each occurrence after a name’s first appearance. With these markers in E2:E100, enter =SUM(E2:E100) in an empty cell. The example list returns 2: the second and third Ana entries are extra copies. Use this measure when you want to know how many duplicate entries would be removed while retaining one copy of each name.
Verify the result before using it. Pick a name you know appears more than once and check that its count is correct. Then check a name that appears once and confirm it returns 1. Compare the total you calculated with the meaning you selected: repeated rows, unique repeated names, or extra copies. If the list changes, update the formula ranges or use an Excel table so new rows are included automatically.

Common Mistakes

Counting the wrong thing: A repeated name can appear on several rows, but it is still one distinct name. The row total counts every matching row; the distinct total counts each repeated name once; the extra-copy total excludes its first appearance. Label your result clearly so another person does not mistake one measure for another.

Leaving the list range unfixed: If you omit the dollar signs around the full range in the row-by-row COUNTIF formula, the range can shift as you fill down. That can cause counts to be inconsistent. Use $A$2:$A$100 for the full list and leave the current-row reference, such as A2, relative.

Including the heading or unrelated cells: Including a header can produce misleading results if a name happens to match its text. Including a notes column or unrelated cells can also inflate counts. Set the range to the actual name cells only.

Assuming similar-looking names are identical—or different: COUNTIF is not case-sensitive, so Ana and ANA are treated as the same text. But Ana followed by an extra space may be treated as a different value from Ana. Conversely, names that are genuinely different, such as Anne and Annie, are not duplicates just because they look similar.

Deleting duplicates before counting: Excel’s Remove Duplicates command changes the data by deleting repeated rows. Use formulas or conditional formatting to inspect the list first; do not remove entries unless you have confirmed that is what you want and kept a backup.

Tips

For a quick visual check without adding a formula column, select the name cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format and select OK. This highlights duplicate values, but it does not give you a total or distinguish the number of duplicate groups from the number of repeated rows. The command is available in Microsoft 365 for Windows; the layout may differ slightly in Excel for Mac or Excel for the web.

Turn the list into an Excel table when names will be added regularly. Select a cell in the data and choose Insert > Table, confirming that the table has headers if it includes row 1. Formulas entered in a table column generally fill down automatically, and the table expands as you add rows. If you keep a fixed formula range instead, remember to extend its last row when the list grows.

Keep the original names unchanged in one column and put cleanup steps or helper formulas in separate columns. That makes it easier to compare results and correct a mistaken match. If you need to treat differences in capitalization or spacing as meaningful, decide that before counting; the standard COUNTIF method ignores capitalization but does not automatically remove extra spaces.

For a large list, filter the Repeated flag to show only matches, then review the names and their counts together. You can also sort by the count column from largest to smallest to find names that occur many times. Sorting changes the row order, so include all related columns in the sort rather than sorting just the names and separating them from other data.

Troubleshooting

The formula returns 1 for everything: Confirm that every row uses the same complete range, such as $A$2:$A$100. Check that the formula in B3 refers to A3 as its criterion, not A2. Also verify that the list really contains identical text; hidden spaces or different characters can make entries appear alike while Excel treats them as different.

The result is too high: Check whether the formula range includes a heading, notes, or blank-looking cells that contain spaces. For the row total, remember that a name appearing three times contributes three rows. If you wanted one count per repeated name, use the first-occurrence marker formula and sum that helper column instead.

The result is too low or misses new names: Confirm that your formula range reaches the final data row. If names were added below row 100, the examples that end at row 100 will not include them. Extend every relevant range consistently, or use an Excel table that expands with the data.

Names that look the same are not matching: Check for leading or trailing spaces, nonbreaking spaces copied from websites, and inconsistent punctuation. You can inspect a suspicious cell in the formula bar, or clean a separate copy of the data before counting. Avoid replacing the original column until you have checked that cleanup will not merge genuinely different names.

Excel displays an error after pasting a formula: Make sure the formula begins with an equals sign and uses straight quotation marks. The formulas here use commas as argument separators, which is the default in many Excel installations. Some regional settings require semicolons instead; if Excel rejects a formula with commas, use the argument separator configured for your locale. Do not change the formula’s logic or remove a closing parenthesis.

Frequently Asked Questions

How do I count how many times one specific name appears? With names in A2:A100 and the name to check entered in D2, enter =COUNTIF($A$2:$A$100,D2) in E2. The result is the number of matching cells. Change D2 to another name to check it.

Does COUNTIF distinguish uppercase and lowercase names? No. COUNTIF treats capitalization as equivalent, so Ana and ANA match. If capitalization must distinguish people, standard COUNTIF is not sufficient by itself; you would need a case-sensitive approach.

Can I count duplicates without adding a helper column? Yes, for a quick visual review, use the Duplicate Values conditional formatting rule. To calculate a reliable total—especially a total of distinct repeated names—helper columns make the logic easier to inspect and are usually clearer to maintain.

Will the formula count blank cells as a repeated name? The basic row-by-row COUNTIF returns a count for blank criteria too, so blank rows can appear to match other blanks. The distinct-name and extra-copy examples explicitly return 0 for an empty cell. Keep empty rows out of the source range where possible.

Can I use these steps in Excel for Mac or Excel for the web? Yes. COUNTIF, IF, AND, and SUM work in these versions too. The formulas are the same, but ribbon placement and appearance can vary. The Windows copy shortcut Ctrl+C is Command+C on Mac; Excel for the web also supports the formulas in a browser.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.