Formatting Issues

Fix Excel Conditional Formatting Not Working on Some Dropdown Rows

Maira Mehtab Sep 20, 2026 868 views

Question details

The user has applied conditional formatting to a dropdown list, but the color formatting is only triggered for three out of the five dropdown options despite the formulas appearing correct.

Product: Excel
Device & OS: not provided
Scenario: Applying color-coding to a worksheet using a Data Validation dropdown list combined with specific conditional formatting rules.
Observed behavior: Only a portion of the conditional formatting rules work. The last two dropdown selections fail to trigger the expected cell color changes, leaving the cells unformatted.

Before you start

Ensure that your conditional formatting rules are applied to the correct cell ranges and that there are no conflicting priority rules overriding your dropdown list formatting in the Conditional Formatting Rules Manager.

Solution 1Recommended

Remove Trailing Spaces and Invisible Characters from Source Data

Hidden spaces at the end of text strings often cause exact-match conditional formatting rules to fail because the dropdown value no longer perfectly matches the rule criteria.

When a dropdown list is populated from a range of cells or manually typed values, accidental spaces (e.g., typing 'Completed ' instead of 'Completed') prevent Excel from matching the text exactly. This results in the conditional formatting rule being completely ignored for those specific items.

Locate Dropdown Source Data

Navigate to the range of cells where your dropdown list values are stored, or open the Data Validation menu if you typed the values manually.

Edit the Malfunctioning Options

Select the cell for the dropdown option that isn't formatting properly. Click into the formula bar at the top or press F2 to edit the cell's contents.

Delete Hidden Spaces

Look for and delete any extra blank spaces before or after the text string, then press Enter to save the changes.

Use the TRIM Function (Optional for Large Lists)

If you have a long list of source data, create a helper column and use the formula =TRIM(A1) to automatically strip all trailing and leading spaces. Copy the results and paste them as values over your original list.

Test the Dropdown

Return to your main worksheet and reselect the problematic option from your dropdown list to verify that the conditional formatting color now applies correctly.

Quick Length Check: You can quickly check if a cell has hidden spaces by using the =LEN(A1) formula. If the returned number is larger than the visible characters in the word, you have hidden spaces.

WPS Office Direct Solution

Easily Manage Dropdown Lists and Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet offers an intuitive interface for setting up data validation and conditional formatting, ensuring your data visualization works flawlessly without hidden character issues.

1. Set Up Dropdown Lists: Open your document in WPS Spreadsheet, select your target cells, go to the 'Data' tab, and click 'Validation' to assign your dropdown source.
2. Apply Conditional Formatting: Highlight the dropdown cells, navigate to the 'Home' tab, and click 'Conditional Formatting'.
3. Define Color Rules: Choose 'Highlight Cells Rules' > 'Equal To' to assign specific background colors to your dropdown text selections.
4. Ensure Clean Data: Use the built-in TRIM function on your source data list to guarantee no hidden spaces interrupt your color rules.

Fully compatible with Microsoft Excel (.xlsx) formats and conditional formatting rules.Intuitive Data Validation tool for creating error-free dropdown lists.Built-in TRIM and CLEAN functions to easily format text data and remove invisible spaces.Lightweight, fast, and completely free to use for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule not applying to specific text?

This usually happens because the text in the cell doesn't exactly match the text defined in the formatting rule. Leading spaces, trailing spaces, or hidden characters (like non-breaking spaces) will break the exact match requirement, causing the rule to fail.

How can I automatically remove invisible characters from my Excel data?

You can use the TRIM() function to remove standard extra spaces. For stubborn non-breaking spaces (often copied from web pages), use the CLEAN() function or a combination formula like =TRIM(SUBSTITUTE(A1, CHAR(160), CHAR(32))).

Do conditional formatting rules care about uppercase and lowercase letters?

No, standard conditional formatting rules in Excel and WPS Spreadsheet are not case-sensitive. Entering 'Pending' or 'pending' will both successfully trigger the same text-based formatting rule.

How do I check if my conditional formatting rule range is set up correctly?

Go to Home > Conditional Formatting > Manage Rules. Check the 'Applies to' range to ensure it fully covers your dropdown cells, and verify the formula or text criteria in the 'Format' column to ensure there are no typos.

Document & File Issues