Fix Excel Conditional Formatting Not Detecting Different Values
Question details
Users need to resolve an issue where Excel's conditional formatting does not highlight cells even when the values appear to be different.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing a value calculated by a SUM formula against another value that is either stored as text or has microscopic hidden decimal differences.
- Observed behavior
- Conditional formatting fails to trigger the cell highlight rule because the underlying calculated values differ beyond the displayed decimal places or the data types do not match.
Before editing your conditional formatting rules, expand your column width and increase the decimal display to check for hidden floating-point numbers, and ensure both cells are formatted as Numbers rather than Text.
Use the ROUND Function in Your Conditional Formatting Rule
Applying the ROUND function inside your rule ensures that Excel compares the exact visible numerical values rather than being thrown off by microscopic floating-point decimal differences.
When Excel calculates formulas like SUM, it can sometimes produce floating-point arithmetic errors deep into the decimal places (e.g., 0.0000000001 differences). Because conditional formatting requires an exact match, these invisible differences cause comparison rules to fail. Wrapping your cell references in the ROUND function standardizes the comparison.
Highlight the cell or range of cells where you want the conditional formatting to apply (for example, click on cell M33).
Go to the Home tab on the Excel ribbon, click on Conditional Formatting in the Styles group, 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, enter a formula that rounds both values to a specific number of decimal places for an accurate comparison, such as: =ROUND(M33,2)<>ROUND($M$36,2)
Click the Format button, choose your desired Fill color or Font style to highlight the differences, click OK, and then OK again to apply the rule.

Convert Text Formats to Numbers Using the VALUE Function
If one of the values being compared is stored as text, conditional formatting will not recognize it as equal or accurately compare it to a calculated numerical SUM.
Use WPS Spreadsheet for Flawless Conditional Formatting
WPS Office offers a highly compatible and intuitive Spreadsheet application that handles complex conditional formatting and floating-point calculations with ease. It flawlessly supports advanced formulas like ROUND and SUM to help you manage data accurately.
- 1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing your data.
- 2. Access conditional formatting: Navigate to the Home tab on the ribbon and click on Conditional Formatting.
- 3. Manage your rules: Select Manage Rules to view, edit, or add the ROUND formula rule to fix your comparison logic.
- 4. Apply and save: Set your highlight color, apply the changes, and instantly see accurate data highlighting.

Frequently Asked Questions
Why does Excel think two identical numbers are different in conditional formatting?
Excel calculates and stores numbers up to 15 significant digits. When you use formulas like SUM, floating-point arithmetic can generate microscopic decimal differences (e.g., a difference of 0.00000000001) that aren't visible on the screen but cause strict exact-match rules to fail.
Can I just change the cell format to 'Number' to fix the decimal differences?
No. Changing the cell format only changes how the number is displayed to you, but the underlying decimal value remains unchanged in Excel's memory. You must use mathematical functions like ROUND or TRUNC within your formulas or conditional formatting rules to genuinely change the value being compared.
How do I check all conditional formatting rules currently applied to my worksheet?
Go to the Home tab, click Conditional Formatting, and choose Manage Rules. In the dialog box that appears, change the 'Show formatting rules for' dropdown from 'Current Selection' to 'This Worksheet' to see every active rule on your current sheet.




