Formatting Issues

Fix Excel Conditional Formatting Missing Duplicate Values

Elise WilliamsElise Williams Oct 10, 2026 874 views

Question details

A conditional formatting rule designed to highlight duplicate values is failing to identify all duplicates in an Excel shared workbook.

How to Fix Excel Conditional Formatting Not Highlighting All Duplicate Values
Product
Microsoft Excel
Device & OS
not provided
Scenario
Multiple users are pasting data into a column within a shared workbook, and the duplicate highlighting rule intermittently stops working or misses specific cells.
Observed behavior
The conditional formatting rule highlights some duplicates but misses others even when the applied range covers the entire column. Recreating the rule temporarily fixes the issue, but the problem returns.
Before you start

Before making changes, verify that no other users are actively editing the shared workbook, and save a backup copy of your file to prevent accidental data loss during troubleshooting.

Solution 1Recommended

Clean Up Inconsistent Data Types and Hidden Spaces

Data pasted from external sources often contains hidden trailing spaces or inconsistent formats (e.g., numbers stored as text) that prevent Excel from recognizing identical values as duplicates.

Conditional formatting requires an exact match to highlight duplicates. If one user pastes '12345 ' (with a trailing space) and another types '12345', Excel considers them unique values.

1
Identify mismatched lengths

In an empty adjacent column, use the formula =LEN(A2) (assuming A2 is your first data cell) and drag it down. Compare the character lengths of the supposedly duplicate values.

2
Remove hidden spaces

If you find discrepancies caused by spaces, create a helper column using the formula =TRIM(A2). Copy the results, right-click the original column, and select Paste Special > Values to overwrite the data.

3
Fix numbers stored as text

Highlight the affected column, go to the Data tab, and click 'Text to Columns'. Without changing any settings, immediately click 'Finish'. This forces Excel to re-evaluate the column as standard numbers.

Clean Up Inconsistent Data Types and Hidden Spaces
Preventing Future Formatting Errors: Encourage users sharing the workbook to use 'Paste Special > Values' (Ctrl + Shift + V in some versions) instead of standard paste to avoid copying unwanted spaces and formats.
Seamless Excel Alternative

Effortlessly Manage Duplicates with WPS Spreadsheet

WPS Spreadsheet offers a highly stable, fully compatible environment for managing large datasets and shared workbooks. Its robust conditional formatting engine ensures that duplicate values are highlighted accurately without range fragmentation.

  1. 1. Open your file in WPS Office: Launch WPS Spreadsheet and open your existing .xlsx workbook.
  2. 2. Select your data range: Highlight the column or specific cells where you want to identify duplicate entries.
  3. 3. Apply duplicate highlighting: Navigate to the Home tab, click on 'Conditional Formatting', select 'Highlight Cells Rules', and click 'Duplicate Values'.
  4. 4. Confirm settings: Choose your desired highlight color from the pop-up dialog and click OK to instantly highlight all duplicates.
Fully compatible with Microsoft Excel (.xlsx) formats and conditional formatting rulesStable performance when processing complex data validation and large duplicate datasetsModern and intuitive interface that prevents accidental rule corruption during multi-user collaborationFree, lightweight, and fast alternative for daily data management tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel see two identical values as different?

Excel treats cells as different if one contains invisible characters (like leading, trailing, or non-breaking spaces) or if they have mismatched data types, such as one cell being formatted as text and the other as a number.

Does pasting data overwrite my conditional formatting rules?

Yes. Standard copy-pasting transfers the source cell's formatting to the destination, which can overwrite existing rules and fragment your conditional formatting ranges. To prevent this, always use Paste Special > Values.

How do I find hidden characters causing duplicate rule failures?

You can use the =LEN() function to check the character count of two seemingly identical cells. If the lengths are different, use the =TRIM() function to remove extra spaces or the =CLEAN() function to strip non-printable characters.