Formatting Issues

Fix Excel Conditional Formatting Not Applying to Specific Text Values

Aamir Naveed AkramAamir Naveed Akram Nov 2, 2026 869 views

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.

How to Fix Excel Conditional Formatting Not Applying to 'Unknown' Values
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 you start

Before editing rules, press the F9 key to force a manual recalculation of the workbook, as automatic calculation might be paused or temporarily glitching.

Solution 1Recommended

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.

1
Select the target cells

Highlight the range of cells where the conditional formatting is failing.

2
Open the rules manager

Navigate to the Home tab on the ribbon, click Conditional Formatting, and select Manage Rules.

3
Edit the specific text rule

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.

4
Check cells for hidden spaces

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.

Verify Text Exactness and Remove Extra Spaces
Effortless Data Visualization

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. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the data.
  2. 2. Select the data range: Highlight the cells or columns you want to format based on text values.
  3. 3. Apply text-based formatting: Navigate to the Home tab, click Conditional Formatting, and choose Highlight Cells Rules > Text that Contains.
  4. 4. Enter your criteria: Type 'unknown' (or any target text) into the dialog box, pick a highlight color from the dropdown, and click OK.
Fully compatible with Microsoft Excel (.xlsx) conditional formatting rules.Intuitive interface for managing rule priorities and ranges seamlessly.Lightweight performance reduces calculation lags and formatting glitches.Free to use for everyday data analysis and visualization.
microsoft office alternative - wps office

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.