Function Problems

Fix Excel Conditional Formatting Marking Different Long Numbers as Duplicates

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to prevent conditional formatting from incorrectly highlighting different long numbers (like 16-digit IDs or credit card numbers) as duplicate values.

Product: Excel
Device & OS: not provided
Scenario: Applying conditional formatting to highlight duplicate values in a column containing very long numerical strings.
Observed behavior: Excel treats different numbers as identical if their first 15 digits are the same, triggering a false duplicate warning. Adding a letter before the numbers stops the warning by forcing Excel to read them as text.

Before you start

Check the length of the numbers in your dataset. Because Excel has a strict 15-significant-digit limit for numeric values, any digits past the 15th are rounded to zero, causing different long numbers to look identical to the system.

Solution 1Recommended

Store Long Numbers as Text

Formatting your cells as Text before entering the data prevents Excel from treating the entries as numeric values, thereby bypassing the 15-digit precision limit.

Spreadsheet programs use the IEEE 754 standard, which limits numeric precision to 15 digits. By storing identifiers like credit card numbers or serial numbers as text, you preserve the exact characters entered without rounding.

Select the target column

Click the column letter at the top of your worksheet to highlight all the cells where you plan to enter the long numbers.

Change the cell format

Right-click the highlighted column and select 'Format Cells'. Under the 'Number' tab, choose 'Text' from the category list and click 'OK'.

Re-enter the data

Type or paste your long numbers into the formatted column. Alternatively, you can type an apostrophe (') directly before the number (e.g., '12345678901234567) to force text formatting on a cell-by-cell basis.

Data Loss Warning: If Excel has already rounded your long numbers to end in zeroes, formatting them as text now will not restore the lost digits. You must change the format to Text first, and then re-import or re-type the original numbers.

Solution 2

Use a Custom Formula for Duplicate Detection

Instead of using the default 'Highlight Duplicate Values' rule, apply a custom conditional formatting formula that evaluates the exact text values in the range.

Powerful Data Management

Easily Highlight Duplicates in Long Numbers with WPS Spreadsheet

WPS Office offers robust and highly compatible spreadsheet tools. You can easily format long IDs as text or apply custom formulas to accurately identify duplicates without rounding errors, providing a seamless alternative for complex data tasks.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your spreadsheet file containing the long numerical identifiers.
2. Format cells as Text: Right-click your data column, select 'Format Cells', and choose 'Text' to ensure no digits are truncated.
3. Apply Conditional Formatting: Go to Home > Conditional Formatting > New Rule, and use the custom formula to accurately highlight duplicates.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Intuitive interface for applying advanced conditional formatting rulesBuilt-in text formatting tools to securely handle long IDs and financial numbersFree, lightweight, and fast to launch on Windows, Mac, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my 16-digit number to end with a zero?

Excel adheres to the IEEE 754 specification for storing floating-point numbers, which retains a maximum of 15 significant digits. Any digits typed past the 15th position are automatically converted to zero to prevent calculation errors.

How can I stop Excel from rounding my credit card numbers?

You must tell Excel to treat the numbers as text rather than mathematical values. Do this by formatting the blank column as 'Text' before entering the data, or by typing an apostrophe (') immediately before the first digit.

Will the custom formula fix the duplicates if Excel already rounded the numbers?

No. If the numbers have already been rounded and the extra digits were replaced by zeroes, the unique data is permanently lost in those cells. You must re-enter the correct data after formatting the column as text, then apply the conditional formatting formula.

Excel & Data Problems