How to Fix a Formula Working in a Cell But Not in Conditional Formatting
Question details
The user needs to fix an issue where a custom formula functions correctly in a standard worksheet cell but fails to trigger when used as a rule in Conditional Formatting.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Applying custom formula-based rules in conditional formatting, especially those involving named ranges or functions like FIND, INDIRECT, ADDRESS, ROW, and COLUMN.
- Observed behavior
- The formula evaluates correctly and returns TRUE or FALSE in a standard cell, but fails to apply the correct formatting when pasted into the Conditional Formatting rule.
Before editing your rules, ensure that the active cell you selected when creating the conditional formatting rule matches the top-left cell referenced in your formula, as misaligned relative references will break the rule.
Verify and Correct Named Formulas in Name Manager
Ensure that named ranges or formulas used in conditional formatting correctly define the formula logic rather than pointing to a static cell value.
When using functions like FIND, INDIRECT, or ADDRESS within named formulas, assigning the name directly to a specific cell (like B1) might cause it to evaluate the cell's current static value instead of dynamically calculating the formula across the formatted range.
Navigate to the 'Formulas' tab on the top ribbon and click on 'Name Manager'.
Find and select the named formula that you are using for your conditional formatting rule.
Look at the 'Refers to' field at the bottom of the dialog box. Ensure it contains the actual formula logic (e.g., =FIND(...)) instead of a static cell reference (e.g., =$B$1).
Edit the reference to reflect the correct formula, click the checkmark icon to save the changes, and close the Name Manager.

Adjust Relative and Absolute Cell References
Correct the locking of rows or columns in your conditional formatting formula to ensure it evaluates each cell in the range accurately.
Easily Manage Conditional Formatting Rules with WPS Spreadsheet
WPS Spreadsheet provides an intuitive interface for managing complex conditional formatting rules, Name Manager, and advanced formulas, helping you avoid common reference shifting errors.
- 1. Open your document: Launch WPS Spreadsheet and open the document containing the data you want to format.
- 2. Select the data range: Highlight the target cells, then navigate to the 'Home' tab and click on 'Conditional Formatting'.
- 3. Create a new formula rule: Select 'New Rule' from the dropdown and choose 'Use a formula to determine which cells to format'.
- 4. Enter formula and apply: Type your relative or absolute formula, click 'Format' to set your desired cell highlighting, and click 'OK' to apply.

Frequently Asked Questions
Why do functions like ROW() or COLUMN() behave differently in conditional formatting?
In conditional formatting, ROW() and COLUMN() evaluate based on the top-left cell of the applied range. If the rule is applied to an offset range, the formula's relative starting point shifts, which can cause unexpected formatting results.
How can I test a conditional formatting formula before applying it?
You can test the formula directly in a helper column next to your data. Enter the formula in the first row of the helper column, drag it down, and verify if it correctly returns TRUE or FALSE for the corresponding cells before adding it to the Rules Manager.
Why does INDIRECT() fail in conditional formatting sometimes?
INDIRECT() is a volatile function. If the text string inside INDIRECT refers to a dynamic named range, relies on complex string concatenation that evaluates poorly in memory, or points to an external workbook that is closed, conditional formatting will fail to evaluate it properly.




