Fix Excel Incorrectly Highlighting Long Values as Duplicates
Question details
Users experience false positives when using the built-in Duplicate Values conditional formatting rule on long text strings.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying conditional formatting to highlight duplicate entries in a dataset containing long text strings or complex identifiers.
- Observed behavior
- Excel incorrectly identifies distinct long text values as duplicates if they share the exact same first 15 characters and have an identical string length.
Identify the columns containing long text strings (such as serial numbers, URLs, or complex IDs) that require accurate duplicate checking before applying custom formula solutions.
Use a Custom SUMPRODUCT Formula for Exact Matching
Bypass Excel's built-in 15-character limit by using a custom conditional formatting formula that evaluates the entire text string.
Because Excel's default Duplicate Values rule and standard COUNTIF function often restrict comparisons to the first 15 characters, utilizing the SUMPRODUCT function forces the calculation engine to evaluate the complete contents of the cell.
Highlight the cells containing your long text values (for example, A2:A100) where you want to check for duplicates.
Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.
In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.
In the formula bar, input the formula =SUMPRODUCT(--($A$2:$A$100=A2))>1 (adjusting the cell references to match your actual data range).
Click the Format button, choose your desired fill color to highlight the duplicates, and click OK twice to apply the rule.
Modify the Data with Distinguishing Characters
Alter the long text values so that the built-in duplicate rule can easily differentiate them within its default 15-character limit.
Accurately Highlight Duplicates with WPS Spreadsheet
WPS Office provides robust conditional formatting and advanced formula capabilities, allowing you to bypass strict evaluation limits and accurately manage massive datasets without freezing or crashing.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your long text data.
- 2. Select the data range: Highlight the specific column or cells you need to check for duplicates.
- 3. Access Conditional Formatting: Navigate to the Home tab, click on Conditional Formatting, and choose New Rule.
- 4. Apply custom formula: Select the formula option and input =SUMPRODUCT(--($A$1:$A$100=A1))>1 to enforce a full-string comparison.
- 5. Set color and save: Click Format to choose a highlight color, then click OK to immediately identify true duplicates.
Frequently Asked Questions
Why does Excel only check the first 15 characters for duplicates?
Excel's calculation engine limits numeric precision to 15 significant digits based on the IEEE 754 specification. To optimize processing performance, certain built-in features, including the Duplicate Values conditional formatting rule, apply a similar 15-character evaluation limit to text strings.
Does the COUNTIF function have the same 15-character limit?
Yes, depending on the Excel version and data type, functions like COUNTIF and SUMIF may also truncate text evaluations to 15 characters when handling numeric-like strings, or max out at 255 characters. Using SUMPRODUCT or the EXACT function circumvents this.
Can I use a helper column instead of conditional formatting?
Absolutely. You can create a helper column adjacent to your data and enter the formula =SUMPRODUCT(--($A$2:$A$100=A2))>1. This will output TRUE for actual duplicates, allowing you to easily filter, sort, or delete the identified rows without relying on conditional formatting colors.
