Fix Excel IF and Conditional Formatting Formulas Returning FALSE
Question details
The user needs to correct a conditional-formatting formula that returns FALSE when two cells appear to contain equal values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A comparison uses a value produced by an arithmetic formula, and the user wants conditional formatting to identify matching values reliably.
- Observed behavior
- The IF or conditional-formatting formula returns FALSE even though the displayed values in K14 and L14 appear equal, likely because of unnecessary IF syntax, quotation marks, or decimal precision differences.
Select K14 and L14 one at a time and inspect their full-precision values in the formula bar. Confirm whether the conditional-formatting rule is using a formula without quotation marks and identify the number of decimal places that should count as equal.
Use a direct equality formula in the conditional-formatting rule
Use this solution when the rule only needs to test whether K14 and L14 are equal; an IF function is not required.
A comparison such as =K14=L14 already returns TRUE or FALSE. In a conditional-formatting rule, enter the comparison directly and do not wrap it in quotation marks.
In Excel, select the cells that should receive the rule, including the range whose first row corresponds to K14 and L14.
Go to Home > Conditional Formatting > Manage Rules, select the relevant rule, and click Edit Rule. If needed, choose New Rule > Use a formula to determine which cells to format.
In the formula field, enter =(K14=L14) without quotation marks, then choose a format and click OK or Apply.
Verify that K14 and L14 are relative references unless every formatted row must compare to those exact cells. Test the rule by changing one value temporarily.
Round calculated values before comparing them
Use this solution when arithmetic results differ only because of hidden decimal digits or floating-point precision.
Compare rounded values and create reliable conditional formatting in WPS Spreadsheets
WPS Spreadsheets can open the workbook, simplify the equality test, round calculated values to the required precision, and apply the corrected formula through its Conditional Formatting tools.
- 1. Open the workbook: Launch WPS Office, open the spreadsheet, and select the cells or range that uses the conditional-formatting rule.
- 2. Edit the conditional-formatting rule: Go to Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule or create a formula-based rule.
- 3. Enter a direct comparison: Use =(K14=L14) without quotation marks when the stored values should match exactly.
- 4. Round the values when needed: If hidden decimal precision causes the mismatch, use =ROUND(K14,0)=ROUND(L14,0) or =ROUND(K14-L14,0)=0, changing 0 to the required decimal places.
- 5. Apply and save the file: Click Apply or OK, verify the formatting, and save the workbook in .xlsx format when continued Excel compatibility is required.
Frequently Asked Questions
Why does Excel return FALSE when K14 and L14 look the same?
The cells may contain different hidden decimal digits even though their displayed values match. Compare rounded values with =ROUND(K14,0)=ROUND(L14,0), or use the appropriate number of decimal places.
Do I need IF for an Excel conditional-formatting comparison?
No. The formula =K14=L14 already returns TRUE or FALSE, so it can be entered directly in the conditional-formatting formula field.
Should quotation marks surround the conditional-formatting formula?
No. Enter =(K14=L14) as a formula without quotation marks. Quotation marks can make the rule treat the expression as text instead of evaluating the comparison.
How can I compare whole numbers when the source values come from calculations?
Use =ROUND(K14,0)=ROUND(L14,0) to round both values to whole numbers, or use =ROUND(K14-L14,0)=0 to test whether their rounded difference is zero.
Will the formula adjust for other rows in the formatted range?
Relative references such as K14 and L14 normally adjust for each row when the rule is applied to a range. Confirm the Applies to range and use absolute references only when the comparison must always use fixed cells.
