How to find and highlight duplicate names that match both first and last names in Excel?

In this tutorial, we'll walk you through two effective methods for identifying and highlighting duplicate names in Excel. You'll learn how to find duplicates when first and last names are in separate columns - either within a single range or across two different ranges. Whether you're working with a small dataset or comparing multiple lists, these techniques will help you quickly spot and manage duplicate entries.

Case 1: Find duplicate names in a range where the first name and last name are in separate columns

Case 2: Find duplicate names in two ranges where the first name and last name are in separate columns

Case 1: Find duplicate names in a range where the first name and last name are in separate columns

If the first name and last name are in separate columns, you need to combine them first, then find the duplicate names.

In the next column of the first names and last names, type this formula =E2&" "&F2, drag fill handle down to combine all names.

Now you can find the duplicate names.

Method 1: Using Conditional Formatting

Select the combined names, then click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Select the format you want to highlight duplicates from the values with list.

Click OK.

Method 2: Using Select Duplicate & Unique Cells tool

Select the combined names, click Kutools > Select > Select Duplicate & Unique Cells.

In the popping dialog, check Duplicates (Except 1 st one), or All duplicates (Including 1st one) as you need, then check Fill backcolor or Fill font color to select a color to highlight the duplicate values. You can check Select entire rows option to select the entire row.

Click Ok, dialog pops out to remind you the number of duplicates. Click OK to close it.

Duplicates (Except 1st One)	All duplicates (Including 1st One)

Case 2: Find duplicate names in two ranges where the first name and last name are in separate columns

If you want to compare two ranges as below screenshot shown, you can do as these

Method 1: Using array formula

In Sheet 6, select the cell next to the names, type this formula =IF(ISNUMBER(MATCH(A2&B2,Sheet7!A$2:A$100&Sheet7!B$2:B$100,0)),"","X"), press Shift + Ctrl + Enter keys. Then drag fill handle down to fill the formula to cells.

As below screenshot shown, the blank results indicate duplicate values, X’s are the unique values.

Tip: In the formula, A2, B2 is the first name and last name, Sheet7 is the sheet you want to compare to.

Method 2: Using Select Same & Different Cells tool

Click Kutools > Select > Select Same & Different Cells.

In the popping dialog, select the two ranges in the Find values in and According to boxes, check Each row option and Same values option, go to specify the Fill backcolor and Fill font color as you need.

Click Ok, a dialog pops out to remind you the number of selected rows, click OK to close it. Now the duplicate names are highlighted.