How to Highlight Invalid Dependent Drop-Down Values in WPS Spreadsheets
Question details
The user needs to visually flag dependent drop-down cells when an existing entry no longer matches the current category list, such as Tomato moving from Vegetables to Fruit.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook uses dependent drop-down lists where column A selects a category and column B contains allowed values, but the master lists were changed after data entry.
- Observed behavior
- Previously valid values remain unhighlighted after the master list changes, making invalid category/value combinations difficult to spot.
Before applying the rules, make sure each category has a named range for its valid list, such as Fruit_List and Vegetables_List, and confirm which columns hold the category and dependent value.
Use formula-based conditional formatting to highlight invalid drop-down entries
Best for automatic, non-destructive visual flagging when you want WPS Spreadsheets to highlight any category/value combination that is no longer valid.
This method uses named ranges and a MATCH formula inside a conditional formatting rule. It does not change the existing cell values or data validation settings.
Select the cells in the dependent drop-down column, for example B2:B100.
Go to Home > Conditional Formatting > New Rule.
Select "Use a formula to determine which cells to format" so the rule can test each row against the category list.
Enter =AND($A2="Fruit",ISERROR(MATCH($B2,Fruit_List,0))) for the Fruit category, then click Format and choose a warning fill or font.
Create equivalent rules for Vegetables or other categories, replacing the category name and named range, and set the same Applies to range.
Use a helper column to identify invalid entries for review or filtering
Use this alternative when you also want to sort, filter, or count invalid rows instead of only highlighting them.
Highlight Invalid Dependent Drop-Down Values in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use formula-based conditional formatting to compare dependent drop-down values against category-specific named ranges. Invalid values are flagged automatically so you can review them without altering the underlying data.
- 1. Open the workbook in WPS Spreadsheets: Download and install WPS Office, then open the workbook that contains the dependent drop-down lists.
- 2. Open Conditional Formatting: Select the dependent value cells, then go to Home > Conditional Formatting > New Rule.
- 3. Create a formula rule: Select "Use a formula to determine which cells to format" and enter =AND($A2="Fruit",ISERROR(MATCH($B2,Fruit_List,0))).
- 4. Set the warning format: Click Format, choose a fill or font color, and confirm the rule.
- 5. Add rules for other categories: Repeat the process for Vegetables or other lists, then verify that invalid values are highlighted immediately.
Frequently Asked Questions
Why doesn't Excel automatically mark a drop-down value after the source list changes?
Data validation only restricts new entries, so existing cells are not rechecked when the master list changes. Formula-based conditional formatting lets you flag those now-invalid combinations.
Does this conditional formatting method work in WPS Spreadsheets?
Yes. WPS Spreadsheets supports formula-based conditional formatting, named ranges, and MATCH/ISERROR, so you can follow the same Home > Conditional Formatting > New Rule workflow.
How do I handle multiple categories such as Fruit and Vegetables?
Create a separate formula rule for each category, for example =AND($A2="Vegetables",ISERROR(MATCH($B2,Vegetables_List,0))), and apply all rules to the same cell range.
What happens if I edit the master list again?
The conditional formatting rule recalculates automatically, so any newly invalid combinations are highlighted as soon as you update the named range or list values.
