Formatting Issues

Fix Excel Conditional Formatting Not Identifying Duplicate Values

Algirdas JasaitisAlgirdas Jasaitis Nov 11, 2026 869 views

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.

Fix Excel Conditional Formatting Not Identifying Duplicate Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Use the TRIM Function

In an adjacent blank column, type the formula =TRIM(A2) (assuming your first serial number is in cell A2) and press Enter.

2
Apply to the Entire Column

Drag the fill handle down to apply the TRIM formula to all the serial numbers in your dataset.

3
Replace Original Data

Copy the new column of trimmed values, right-click the original serial number column, and select 'Paste Special' > 'Values' to overwrite the old data.

4
Reapply Conditional Formatting

Select the cleaned data range, go to the Home tab, click 'Conditional Formatting' > 'Highlight Cells Rules' > 'Duplicate Values', and click OK.

Clean Data and Remove Hidden Spaces
Use WPS Spreadsheet

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. 1. Open your Spreadsheet: Launch WPS Office and open your workbook containing the serial numbers.
  2. 2. Select the Data: Highlight the column or range of cells you want to check for duplicates.
  3. 3. Apply Duplicate Highlighting: Navigate to the Home tab, click on 'Conditional Formatting', select 'Highlight Cells Rules', and choose 'Duplicate Values'.
  4. 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.
Seamless compatibility with Microsoft Excel (.xlsx and .xls) formatsOne-click duplicate value highlighting with intuitive menusBuilt-in smart tools to easily clean hidden spaces and format textFree, lightweight, and fast to load large datasets
microsoft office alternative - wps office

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.