Fix Excel Conditional Formatting Not Identifying Duplicate Values
Question details
The user is trying to highlight duplicate serial numbers using conditional formatting, but the duplicates are not being identified and an error occurs.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying a conditional formatting rule to a dataset to automatically identify and highlight duplicate serial numbers.
- Observed behavior
- Duplicate values are not highlighted, and an error appears. The issue cannot be diagnosed directly because the provided workbook does not contain the actual sample data.
Ensure you are working on a spreadsheet that actually contains the data you want to test. Check that the column with serial numbers doesn't have trailing spaces or inconsistent data types, as these are the most common causes for exact matches failing.
Clean Data and Remove Hidden Spaces
Often, duplicate values aren't recognized because one entry has trailing spaces or invisible characters that make it technically unique.
Excel treats '12345' and '12345 ' as two completely different values. Using the TRIM function helps standardize your data so conditional formatting can accurately detect duplicates.
In an adjacent blank column, type the formula =TRIM(A2) (assuming your first serial number is in cell A2) and press Enter.
Drag the fill handle down to apply the TRIM formula to all the serial numbers in your dataset.
Copy the new column of trimmed values, right-click the original serial number column, and select 'Paste Special' > 'Values' to overwrite the old data.
Select the cleaned data range, go to the Home tab, click 'Conditional Formatting' > 'Highlight Cells Rules' > 'Duplicate Values', and click OK.

Ensure Consistent Number and Text Formatting
Excel treats numbers stored as text differently from pure numbers. Making the formatting consistent across the column resolves identification errors.
Verify Conditional Formatting Range and Rules
Errors can appear if the formatting rule is applied to an invalid cell range or if conflicting rules exist.
Highlight Duplicate Values Easily in WPS Office
WPS Spreadsheet provides a highly compatible and intuitive way to manage conditional formatting, ensuring duplicate values like serial numbers are accurately highlighted without complex troubleshooting.
- 1. Open your Spreadsheet: Launch WPS Office and open your workbook containing the serial numbers.
- 2. Select the Data: Highlight the column or range of cells you want to check for duplicates.
- 3. Apply Duplicate Highlighting: Navigate to the Home tab, click on 'Conditional Formatting', select 'Highlight Cells Rules', and choose 'Duplicate Values'.
- 4. Choose Format: Select a color fill (like Light Red Fill with Dark Red Text) to highlight the duplicates and click OK to instantly view the results.

Frequently Asked Questions
Why does conditional formatting ignore my exact duplicates?
This usually happens because the cells have different underlying data types (e.g., one is formatted as text and the other as a number) or because there are invisible spaces at the beginning or end of the text.
How do I remove hidden spaces to fix the duplicate values rule?
You can use the =TRIM() function in a helper column to remove extra spaces. Once trimmed, copy the results and use 'Paste Special > Values' over your original data.
Can conditional formatting highlight duplicates across multiple sheets?
Standard conditional formatting rules do not work directly across multiple sheets. You need to use a custom formula with the COUNTIF function combined with named ranges, or consolidate the data into one sheet first.
Why do I get an error when applying conditional formatting?
Errors typically occur if your 'Applies to' range contains invalid cell references (like #REF!), or if a custom formula used within the formatting rule contains syntax errors. You can fix this by checking the Conditional Formatting Rules Manager.




