How to Compare Excel Columns When One Contains Formula Results
Question details
The user needs to accurately compare a column of numbers against a column of formula-generated results without floating-point calculation errors causing false mismatches.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two columns of numeric data to identify exact matches or differences.
- Observed behavior
- Standard comparison tools like Go To Special or exact match formulas fail to identify matching values due to hidden, microscopic decimal differences in the formula results.
Ensure both columns are formatted as numbers and determine the maximum number of significant decimal places your actual data requires (for example, 2 decimal places for standard currency).
Use the ROUND Function in a Helper Column
Round the formula results to a specific number of decimal places to eliminate floating-point precision errors before comparing.
Spreadsheet software sometimes stores calculated numbers with tiny, microscopic fractions due to floating-point arithmetic. Rounding the values manually ensures that the software compares only the meaningful digits of your numbers.
Insert a new blank column directly next to the column containing your formula-generated values.
In the first cell of the helper column, type the formula =ROUND(B2, 10), replacing 'B2' with your formula cell and '10' with the number of significant decimal places you want to evaluate.
Click and drag the fill handle at the bottom-right corner of the cell to copy the ROUND formula down the entire column.
Compare your original static column with this new rounded helper column using an exact match formula like =A2=C2 to accurately identify matches.

Calculate the Absolute Difference Using the ABS Function
Calculate the difference between the two cells and check if it falls within a microscopic, tolerable threshold.
Easily Compare and Calculate Data Using WPS Spreadsheet
WPS Spreadsheet provides powerful functions and high-precision calculation tools that make comparing data columns and handling complex formulas simple and efficient.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing the numeric columns you need to compare.
- 2. Apply rounding easily: Use the =ROUND() function in an empty helper cell to quickly standardize the decimal places across your datasets.
- 3. Highlight differences instantly: Select the columns and use 'Conditional Formatting' > 'Highlight Cells Rules' to visually spot matching or differing values in seconds.

Frequently Asked Questions
Why do Excel formulas produce tiny decimal differences?
Spreadsheet programs use the IEEE 754 standard for floating-point arithmetic. Since computers operate using binary numbers, certain standard decimal fractions cannot be represented with perfect precision, leading to microscopic rounding errors during calculations.
Can I use the EXACT function to compare these columns?
The EXACT function is designed to compare text strings with absolute precision. If applied to formula numbers with hidden floating-point errors, it will return FALSE because the underlying string of digits differs. You must round the values before using EXACT.
Does enabling 'Precision as displayed' fix this issue permanently?
Enabling 'Set precision as displayed' in the application options forces the spreadsheet to permanently change stored values to match their visual formatting. While it resolves comparison mismatches, it permanently deletes original underlying decimal data, which is risky for exact scientific or financial records.




