Calculation Issues

How to Compare Excel Columns with Long Decimal Numbers

Bushra ParveenBushra Parveen Nov 11, 2026 869 views

Question details

Find a quick and reliable method to compare two columns of numbers where differences only exist in insignificant decimal places.

How to Compare Excel Columns with Long Decimal Numbers
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.
Before you start

Determine the maximum number of decimal places that are significant for your analysis so you can adjust the rounding formula accordingly.

Solution 1Recommended

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.

1
Create an auxiliary column

Insert a new blank column next to the two data columns you want to compare.

2
Enter the comparison formula

Select the first cell of the new column (e.g., C2) and type the formula: =IF(ROUND(A2-B2,5)<>0,"Difference!","").

3
Adjust the decimal precision

Change the number '5' in the formula to reflect the exact number of decimal places that matter for your specific dataset.

4
Apply to all rows

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.

Use an Auxiliary Column with a ROUND and IF Formula
Customizing the Output: You can modify the "Difference!" text to any warning label you prefer, or change the empty quotes "" to "Match" to clearly label rows that are practically identical.
Data Analysis Tool

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. 1. Open your dataset: Launch WPS Office and open your spreadsheet document containing the numerical columns.
  2. 2. Apply the comparison formula: Select a blank adjacent cell and input your =IF(ROUND(A2-B2, 5)<>0, "Difference", "") formula.
  3. 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.
100% compatible with Microsoft Excel formulas, formatting, and functionsFast processing speeds for large datasets with complex decimal numbersFree, lightweight, and user-friendly interface for daily office tasks
microsoft office alternative - wps office

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.