Fix Excel Conditional Formatting Not Applying to Specific Text Values
Question details
The user's conditional formatting rule fails to highlight or format cells containing specific text like 'unknown', despite working properly for dates and blanks.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying conditional formatting rules to highlight specific text values in a worksheet.
- Observed behavior
- Cells containing the word 'unknown' are not formatted as expected, while other rules in the same range work fine. The issue may resolve temporarily upon recalculating or reopening the workbook.
Before editing rules, press the F9 key to force a manual recalculation of the workbook, as automatic calculation might be paused or temporarily glitching.
Verify Text Exactness and Remove Extra Spaces
Ensure the conditional formatting rule matches the text exactly and that the target cells do not contain hidden spaces.
Conditional formatting rules looking for specific text can fail if the cells contain invisible leading or trailing spaces. Making sure the text rule accurately targets the cell's contents is the most common fix.
Highlight the range of cells where the conditional formatting is failing.
Navigate to the Home tab on the ribbon, click Conditional Formatting, and select Manage Rules.
Select the rule for the text 'unknown' and click Edit Rule. Ensure the rule type is set to 'Format only cells that contain', the dropdown is set to 'Specific Text', and the next dropdown is 'containing' or 'beginning with' rather than requiring an exact full-cell match if hidden spaces exist.
If using an exact match, double-click a failing cell and check for spaces after the word 'unknown'. Remove any extra spaces manually or use the TRIM function in a helper column to clean your data.

Check Rule Order and the 'Applies to' Range
Conflicts with other formatting rules or an incorrect cell range can override your text-based rule.
Restart the Workbook and Recreate the Rule
Resolve temporary Excel workbook glitches by restarting the application and recreating a fresh rule.
Highlight Specific Text Easily in WPS Spreadsheet
WPS Spreadsheet offers a highly compatible and intuitive conditional formatting tool to effortlessly highlight specific text, duplicates, and blanks without calculation glitches.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the data.
- 2. Select the data range: Highlight the cells or columns you want to format based on text values.
- 3. Apply text-based formatting: Navigate to the Home tab, click Conditional Formatting, and choose Highlight Cells Rules > Text that Contains.
- 4. Enter your criteria: Type 'unknown' (or any target text) into the dialog box, pick a highlight color from the dropdown, and click OK.

Frequently Asked Questions
Why does my conditional formatting work for numbers but not specific text?
Text matching requires exact character strings. Invisible leading or trailing spaces, non-breaking space characters, or case-sensitivity settings can cause a text rule to fail even when number rules work perfectly.
What does 'Stop If True' mean in conditional formatting?
The 'Stop If True' checkbox prevents lower-priority rules from being applied to a cell if a higher-priority rule's condition is met. If an upper rule triggers on your cell, the text formatting below it will be ignored.
Can I format a cell based on a partial text match?
Yes. You can select 'Text that Contains' under Highlight Cells Rules, or use a custom formula rule with the SEARCH function to find partial matches without requiring an exact cell match.
Why is my conditional formatting randomly disappearing?
This usually happens when data is copied and pasted over the formatted range, which pastes the source formatting and overwrites your rules. It can also occur due to minor calculation glitches, which can often be fixed by pressing F9 to recalculate.




