How to Compare Excel Columns with Long Decimal Numbers
Question details
Find a quick and reliable method to compare two columns of numbers where differences only exist in insignificant decimal places.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing datasets containing numerical values with long floating-point decimals, where minor discrepancies beyond a certain decimal place should be ignored.
- Observed behavior
- Standard comparison formulas (like A2=B2) return false mismatches because they evaluate the full-precision underlying values, even if the displayed cell formatting makes the numbers look identical.
Determine the maximum number of decimal places that are significant for your analysis so you can adjust the rounding formula accordingly.
Use an Auxiliary Column with a ROUND and IF Formula
Use the ROUND function nested inside an IF statement to compare the rounded difference of two cells, effectively ignoring insignificant decimals.
Excel stores numbers up to 15 significant digits. Simply formatting a cell to display fewer decimals does not change its underlying value, which causes direct comparisons to fail. Calculating the difference between two cells and rounding that difference provides an accurate comparison.
Insert a new blank column next to the two data columns you want to compare.
Select the first cell of the new column (e.g., C2) and type the formula: =IF(ROUND(A2-B2,5)<>0,"Difference!","").
Change the number '5' in the formula to reflect the exact number of decimal places that matter for your specific dataset.
Press Enter to execute the formula, then click and drag the fill handle at the bottom-right corner of the cell down to evaluate the rest of your data.

Easily Compare Complex Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and math functions like IF and ROUND, making numerical data comparison fast and highly accurate.
- 1. Open your dataset: Launch WPS Office and open your spreadsheet document containing the numerical columns.
- 2. Apply the comparison formula: Select a blank adjacent cell and input your =IF(ROUND(A2-B2, 5)<>0, "Difference", "") formula.
- 3. Drag to fill: Double-click the small square fill handle at the bottom-right of the cell to instantly compare all rows in your sheet.

Frequently Asked Questions
Why do numbers that look identical fail a direct comparison check?
Cell formatting only changes the visual display of a number. Excel comparisons check the underlying stored value, which retains up to 15 decimal places of precision. A minor floating-point difference will cause a standard equals (=) comparison to return FALSE.
Can I use the EXACT function to compare these columns?
No. The EXACT function strictly evaluates the underlying values and is designed for text strings. It will not ignore trailing insignificant decimals, making it unsuitable for this specific scenario.
How do I permanently remove insignificant decimals from my data?
You can use the ROUND function directly on the source data columns to generate new, permanently rounded values. Alternatively, you can enable the 'Set precision as displayed' option in the Advanced settings, which permanently alters all stored values in the workbook to match their visible formatting.




