Function Problems

How to Highlight Duplicate First and Last Names in Excel

Bushra Parveen Sep 27, 2026 869 views

Question details

The user needs to identify and highlight rows where the exact combination of a first name and a last name appears multiple times within an Excel dataset.

How to Highlight Duplicate First and Last Names in Excel

Product: Excel
Device & OS: not provided
Scenario: Cleaning up or analyzing a dataset where duplicate individuals (with matching first and last names across two columns) need to be visually identified without highlighting blank rows.
Observed behavior: Rows containing duplicate name entries are highlighted automatically using conditional formatting, successfully bypassing completely blank rows.

Before you start

Ensure your first names and last names are located in separate, clearly defined columns (e.g., Column A for First Name, Column B for Last Name) and that there are no hidden trailing spaces in the text.

Solution 1Recommended

Use COUNTIFS Formula in Conditional Formatting

The most reliable and recommended method to highlight duplicate pairs while ignoring blank rows.

By combining the AND function with COUNTIFS, you can instruct Excel to check both columns simultaneously. The formula counts how many times the combination appears in the specified columns and highlights the row if the count is greater than one. Importantly, checking that the cells are not empty prevents Excel from highlighting entirely blank rows.

Select your data range

Highlight the range of cells where your names are located. It is highly recommended to select the entire columns or the exact data block (e.g., A2:B100).

Open Conditional Formatting

Navigate to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule'.

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the COUNTIFS formula

In the formula bar, enter: =AND($A2<>"",$B2<>"",COUNTIFS($A:$A,$A2,$B:$B,$B2)>1). Adjust the column letters (A and B) and row number (2) if your data starts elsewhere.

Apply formatting

Click the 'Format' button, choose a distinct fill color under the 'Fill' tab, and click 'OK' twice to apply the rule.

Use COUNTIFS Formula in Conditional Formatting

Pro Tip: Using absolute column references (like $A2) ensures that both the first name and last name cells in the same row receive the highlight.

Solution 2

Use FILTER and CHOOSECOLS Array Formula (Office 365)

An alternative approach for modern Excel versions using dynamic array functions.

Manage Data Easily

Highlight Duplicate Names Instantly in WPS Spreadsheet

WPS Spreadsheet provides fully compatible and robust conditional formatting tools, making it incredibly simple to highlight complex duplicate data like matching first and last names.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your first and last name columns.
2. Apply conditional formatting: Highlight your data, navigate to the 'Home' tab, and click 'Conditional Formatting' > 'New Rule'.
3. Insert the formula: Select 'Use a formula to format cells', input the exact COUNTIFS formula used in Excel, pick a highlight color, and click 'OK'.

Fully compatible with Microsoft Excel conditional formatting and complex COUNTIFS formulas.Lightweight architecture ensures smooth performance even when analyzing thousands of rows.Features a familiar, easy-to-navigate interface so you can find data tools instantly.Completely free built-in tools for data cleanup, including a one-click 'Remove Duplicates' feature.

microsoft office alternative - wps office

Frequently Asked Questions

Why are blank rows getting highlighted as duplicates?

Because an empty cell matches another empty cell, Excel interprets multiple blank rows as duplicates of each other. By adding the criteria AND($A2<>"",$B2<>"") to your formula, you explicitly tell Excel to ignore cells that are completely blank.

Can I use this method to highlight duplicates across three or more columns?

Yes, the COUNTIFS function can handle multiple conditions. To check three columns (e.g., First Name, Last Name, and Date of Birth), simply expand the formula: COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)>1.

How do I remove the duplicates after highlighting them?

If you wish to delete the duplicates rather than just viewing them, go to the 'Data' tab and click 'Remove Duplicates'. Ensure you check the boxes for both your First Name and Last Name columns, then click 'OK' to delete the redundant entries.

Excel & Data Problems