Fix Excel Conditional Formatting Missing Duplicate Values
Question details
A conditional formatting rule designed to highlight duplicate values is failing to identify all duplicates in an Excel shared workbook.

- 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 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.
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.
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.
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.
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.

Repair Fragmented Conditional Formatting Ranges
Pasting data, inserting rows, or deleting rows in a shared workbook can fragment the 'Applies to' range of your conditional formatting rules, causing them to skip certain cells.
Disable Legacy Workbook Sharing and Re-apply Rules
Legacy shared workbooks in Excel can sometimes corrupt conditional formatting rules during background multi-user synchronization.
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. Open your file in WPS Office: Launch WPS Spreadsheet and open your existing .xlsx workbook.
- 2. Select your data range: Highlight the column or specific cells where you want to identify duplicate entries.
- 3. Apply duplicate highlighting: Navigate to the Home tab, click on 'Conditional Formatting', select 'Highlight Cells Rules', and click 'Duplicate Values'.
- 4. Confirm settings: Choose your desired highlight color from the pop-up dialog and click OK to instantly highlight all duplicates.

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.




