Formatting Issues

How to Highlight Duplicate Names with Different IDs in Excel

WPS Editor Sep 28, 2026 869 views

Question details

The user needs to identify and highlight rows where the same employee name appears with conflicting or different employee IDs in a dataset.

Highlighting Duplicate Names with Different Employee IDs in Excel

Product: Excel
Device & OS: not provided
Scenario: Auditing employee records or data entry files to find inconsistencies where a single person is assigned multiple distinct IDs.
Observed behavior: The user wants to apply a formula-based conditional formatting rule to automatically color-code these mismatched records.

Before you start

Ensure your data is organized in clear columns (e.g., Column A for Names, Column B for IDs) and remove any hidden trailing spaces from the names to prevent false mismatches.

Solution 1Recommended

Use COUNTIFS Formula in Conditional Formatting

Apply a custom formula rule to instantly highlight records with matching names but non-matching IDs.

By utilizing the COUNTIFS function within Conditional Formatting, you can instruct Excel to check the entire column for the current row's name and see if there are any instances where the ID does not match the current row's ID. If the count is greater than zero, the row is highlighted.

Select the Data Range

Highlight the range of your data, for example, A1:B100. Ensure that A1 is the active cell when you make the selection.

Open Conditional Formatting

Navigate to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Enter the Formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter: =COUNTIFS($A$1:$A$100,$A1,$B$1:$B$100,"<>"&$B1)>0

Apply Formatting

Click the 'Format' button, go to the 'Fill' tab, choose a highlight color (such as red), and click 'OK' twice to apply the rule.

Use COUNTIFS Formula in Conditional Formatting

Formula Explanation: The COUNTIFS formula checks two conditions simultaneously: it looks for exact matches in the Name column ($A$1:$A$100,$A1) and non-matches in the ID column ($B$1:$B$100,"<>"&$B1). The >0 triggers the highlight when mismatches exist.

Solution 2

Identify Multiple IDs Using a Pivot Table

Create a Pivot Table to count unique IDs per name if you prefer an analytical summary instead of in-cell highlighting.

Advanced Formatting with WPS Spreadsheet

Easily Highlight Data Inconsistencies with WPS Office

WPS Spreadsheet offers powerful conditional formatting tools fully compatible with Excel formulas, allowing you to highlight duplicates, find errors, and manage large datasets efficiently for free.

1. Open Your Data File: Launch WPS Spreadsheet and open the workbook containing your employee names and IDs.
2. Select the Target Range: Highlight the columns or cell range (e.g., A1:B100) you want to audit.
3. Access Conditional Formatting: Go to the 'Home' tab on the top menu, click on 'Conditional Formatting', and select 'New Rule'.
4. Apply Custom Formula: Select the formula option, input your COUNTIFS formula, set your preferred fill color, and click OK to instantly view mismatches.

Fully compatible with Microsoft Excel conditional formatting formulas like COUNTIFSIntuitive UI for creating and managing custom formatting rulesFree, lightweight, and fast spreadsheet solution for large data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong cells?

This usually happens due to incorrect absolute or relative cell references. Ensure your ranges (like $A$1:$A$100) are absolute with dollar signs, while the criteria referencing the active cell (like $A1) is relative for the row so it adjusts as the rule evaluates down the column.

How do I remove the red highlighting once the IDs are fixed?

You can clear the highlighting by navigating to Home > Conditional Formatting > Clear Rules, and then selecting 'Clear Rules from Entire Sheet' or 'Clear Rules from Selected Cells'. Alternatively, the highlight will automatically disappear once you correct the mismatched ID, since the formula conditions will no longer be met.

Is there a way to find special characters, such as trademark symbols, in column A?

Yes. You can use the Find and Replace tool by pressing Ctrl+F and typing or pasting the trademark symbol (™) into the Find box. To highlight them, you can create a new Conditional Formatting rule using the formula =ISNUMBER(SEARCH("™",$A1)).

Document & File Issues