Highlight Duplicate Names in Excel: A Step-by-Step Guide

To highlight duplicate names in Excel for Microsoft 365 on Windows, select the cells containing the names, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a highlight style and select OK. Excel marks names that occur more than once in the selected range, making repeated entries easier to review without changing the data.

What You’ll Need

An Excel workbook with the names entered in worksheet cells.
A clear idea of which cells to check. For example, decide whether to compare one name column or a whole list that includes a header.
Optional: a highlight color that stands out from the worksheet’s existing formatting.

The steps below use Excel for Microsoft 365 on Windows. The same conditional-formatting rule is available in many recent desktop versions. In Excel for Mac, use the Home tab and the Conditional Formatting menu; the labels are similar, although their placement can vary. Excel for the web also supports conditional formatting, but some rule-editing options may be presented differently.

Step-by-Step

Check the names and choose the range. Find the column or block of cells you want Excel to examine. If names are in A2:A50 and A1 contains the heading “Name,” select A2:A50, not the heading. Including a header can make it part of the comparison, while selecting just the data keeps the rule focused. If your list is in one continuous column, click the first name and drag to the last one, or click the first cell and use Shift-click on the last cell. For separate, non-adjacent ranges, apply the rule to each range individually to keep the setup simple.
Select the cells to check. With the range selected, confirm that the highlighted selection includes every name you want compared and excludes unrelated columns. The built-in Duplicate Values rule compares the cell values in the selected range. If you select only a few rows, Excel will not flag matches that appear outside those rows. For a growing list, you can select a larger reasonable range, such as A2:A500, but avoid selecting an entire worksheet column unless you have a reason to: a smaller range is easier to manage and may be more efficient.
Open the duplicate-values rule. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The Duplicate Values dialog opens. Its first menu usually offers Duplicate and Unique. Leave Duplicate selected to mark names that appear multiple times. Choosing Unique instead would highlight entries that occur only once, which is the reverse of the usual goal.
Choose the appearance. In the second menu, select one of the preset formats, such as a light red fill with dark red text. You can also choose Custom Format to set a fill, font, border, or number format that suits your sheet. Pick a style with enough contrast to remain readable, especially if the worksheet already uses colored fills to convey other information. The formatting is visual only: it does not delete, sort, or alter the names.
Apply the rule. Select OK in the dialog. Excel immediately formats each cell whose name has another matching value within the selected range. For example, if “Jordan Lee” appears in A2 and A8, both cells are highlighted. A name appearing three times will normally have all three occurrences highlighted; the rule identifies repeated values, not just the later copy.
Review the highlighted entries. Scan the marked cells and confirm that the repetitions are genuine. A duplicate may be an accidental second entry, but it may also be valid—for example, two different people can share the same name. Conditional formatting finds matching cell contents; it cannot tell whether the records refer to the same person. Check other details, such as an ID, email address, or department, before deciding what to do with a match.
Adjust or remove the rule if needed. To change its color or range, select a cell in the formatted area and choose Home > Conditional Formatting > Manage Rules. In the rules manager, choose the relevant rule, edit it, or inspect its “Applies to” range. If you want to clear the highlighting, select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Clearing a rule removes its formatting behavior, not the names themselves.

For more control, use a formula-based conditional-formatting rule. This is useful when blank cells should not be highlighted or when you need to define the comparison range precisely. With names in A2:A50, select A2:A50, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(A2<>"",COUNTIF($A$2:$A$50,A2)>1), choose Format, set the appearance, and select OK. The formula checks each selected cell against the fixed list and excludes empty cells. For example, if “Jordan Lee” occurs in A2 and A8, COUNTIF returns 2 for either cell, so both meet the greater-than-1 test and are highlighted. Keep A2 relative so the rule checks each row; the dollar signs keep the comparison range fixed.

Common Mistakes

Selecting the wrong range: A rule applied only to A2:A20 will miss a match in A21. Check the “Applies to” range in Manage Rules if results seem incomplete.
Including a heading or unrelated cells: A header can be compared like any other value, and checking multiple columns together can mark identical text in different fields. Select only the name cells you mean to compare.
Assuming similar-looking entries are identical: “Sam Lee” and “Sam Lee” contain a different number of spaces. A trailing space can also make a name look the same while its stored text differs. The built-in rule is based on cell values, not on whether two entries look approximately alike.
Treating every match as an error: Two people can have the same full name. Use another identifier to verify a record before deleting or combining anything.
Typing a formula for the wrong starting cell: In the custom formula example, the selection starts at A2, so the formula uses A2 as its relative reference. If your selected range starts at B5, update the relative cell reference to B5 and adjust the fixed comparison range to match your data.

Tips

Use a table for lists that grow: Converting a list to an Excel table can make it easier to manage as you add rows. Review the conditional-formatting rule after adding data to confirm that the new rows are included. Do not assume a rule’s range has expanded; check Manage Rules if you are unsure.
Keep the heading outside the rule: If the list has a title row, start the selection below it. This makes the rule easier to understand and avoids accidentally formatting the heading.
Choose a subtle but clear format: A pale fill with dark text often keeps names readable. Avoid relying on color alone when you share the sheet with people who may have difficulty distinguishing colors; a separate review column or filter can help communicate the issue.
Make a copy before cleanup: Conditional formatting is reversible, but deleting rows or editing records is not always easy to undo later. Save a copy before making substantial changes based on the highlighted results.
Use the built-in rule for a quick check: For ordinary exact duplicate highlighting, the preset rule is faster and less error-prone than writing a formula. Use the formula approach when you need to exclude blanks or control the checked range more carefully.
Refresh your understanding after edits: Conditional formatting responds when cell values change. If a highlighted name is corrected or a duplicate is removed, the appearance should update automatically as long as the rule still covers the cells.

Troubleshooting

Nothing is highlighted. Confirm that the selected range contains at least two cells with matching contents. Then open Manage Rules and check that the rule is enabled and its “Applies to” address covers the names. If you used the formula option, check that the formula’s first relative reference matches the top-left cell of the selected range and that the fixed range includes all entries.

Some apparent duplicates are not marked. Compare the underlying text carefully. Extra spaces, different punctuation, or a middle initial can make entries distinct. You can edit the cells to standardize them, or use a separate cleanup process before applying the rule. Avoid changing names automatically without checking the source, because small differences may be meaningful.

Cells are highlighted that you did not expect. Inspect the selected range and the “Applies to” range. The rule may cover more columns or rows than intended. Also check whether the same short name, such as “Alex,” appears in unrelated parts of the selected block. The rule only compares values; it does not understand which column represents a person’s full name.

The format is hard to see. Open Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule or Edit Format, depending on the dialog shown. Pick a stronger fill or font color. Also check whether another conditional-formatting rule is affecting the same cells; rule order and settings can influence which appearance is visible.

The formula rule highlights every cell or none. Make sure the formula begins with an equals sign, uses commas between arguments, and has balanced parentheses. In the example, the selected range begins at A2, the per-row reference is A2, and the fixed comparison range is $A$2:$A$50. If your data starts elsewhere, revise all three references to fit your layout. A mistyped quote or an incorrect “Applies to” range can also prevent the intended result.

Frequently Asked Questions

Does Excel highlight both copies of a duplicate name?
Yes. The Duplicate Values rule marks all cells in the selected range whose value occurs more than once, rather than highlighting only the second entry.

Can I highlight duplicates in just one column?
Yes. Select only the name cells in that column, excluding the heading, before applying the rule. Excel will then compare entries within that selection rather than across unrelated columns.

Does the rule distinguish uppercase and lowercase?
The standard duplicate-values rule is not a case-sensitive way to compare names. If capitalization differences must count as different values, use a carefully designed case-sensitive formula-based rule or normalize the data first.

Can I highlight duplicate names while ignoring blank cells?
Use the formula-based rule shown above. Its AND test excludes empty cells, while COUNTIF checks whether the name appears more than once in the specified range.

Will the highlighting change the names or remove duplicates?
No. Conditional formatting changes only how cells look. To remove or consolidate entries, review each match and use an appropriate data-cleanup method separately.

Can I use these steps in Excel for Mac or the web?
The duplicate-values conditional-formatting feature is available in recent Excel versions on Mac and in Excel for the web. Look under Home and Conditional Formatting; dialog layout and rule-management options can differ slightly from Windows.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.