How to Highlight Excel Cells When Data Validation Value Becomes Invalid
Question details
The user wants to automatically highlight cells containing values that are no longer valid after the data validation source list has been modified.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Existing entries in a spreadsheet were previously valid, but the source list has changed (e.g., an item was removed). The user needs a visual cue to identify these now-invalid entries.
- Observed behavior
- Excel's data validation only restricts new data entries. It does not automatically remove, flag, or recheck existing cell values when the source list is updated.
Identify the exact cell references for your data entry range and your data validation source list, as you will need both to create an accurate conditional formatting formula.
Use Conditional Formatting with the MATCH Formula
Apply a custom formula in the conditional formatting tool to check if the current cell values still exist in the updated source list.
Because standard Data Validation only triggers during data entry, you can pair it with Conditional Formatting to audit existing records. By using the MATCH function wrapped in ISNA, Excel can detect when a value in your data range no longer matches any item in your validation source list.
Highlight the range of cells where your data validation is applied (for example, B3:B14). If you have multiple non-adjacent columns, hold the Ctrl key to select them all.
Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.
In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.
In the formula bar, enter `=ISNA(MATCH(B3,$H$3:$H$17,0))`. Replace 'B3' with the first cell of your selected range, and '$H$3:$H$17' with the absolute reference of your source list. To add a specific secondary condition (like matching a value in A17), you can use a multiplier: `=(B3=$A$17)*ISNA(MATCH(B3,$H$3:$H$17,0))`.
Click the 'Format' button, go to the 'Fill' tab, and choose a prominent color like red. Click 'OK' twice to apply the rule.

Easily Highlight Invalid Data in WPS Spreadsheet
WPS Spreadsheet provides robust conditional formatting and data validation features that are perfectly compatible with Microsoft Excel. You can quickly set up formula-based formatting to audit your data and identify outdated entries.
- 1. Open your worksheet: Launch WPS Spreadsheet and open the file containing your data validation lists.
- 2. Access Conditional Formatting: Select the cells you want to monitor, go to the 'Home' tab, and click on 'Conditional Formatting'.
- 3. Create a New Rule: Click 'New Rule', choose 'Use a formula to determine which cells to format', and input your `ISNA(MATCH())` formula.
- 4. Apply and format: Click 'Format' to choose your highlight color (e.g., Red fill), then click 'OK' to instantly flag invalid data.

Frequently Asked Questions
Why doesn't Data Validation automatically clear invalid entries when the source list changes?
Data Validation is designed primarily to restrict new data input. It does not actively audit, modify, or delete existing data when the source list is updated, which prevents accidental data loss in historical records.
Can I use the 'Circle Invalid Data' feature instead of conditional formatting?
Yes. You can go to the 'Data' tab, click the drop-down arrow next to 'Data Validation', and select 'Circle Invalid Data'. However, this only applies temporary red circles that must be manually refreshed, whereas conditional formatting provides a permanent, dynamic highlight.
How do I apply this conditional formatting rule to multiple non-adjacent ranges?
Open the 'Conditional Formatting Rules Manager'. Find your rule and edit the 'Applies to' field. You can separate multiple ranges with commas or semicolons, such as `=$B$3:$B$14,$D$3:$D$14,$F$3:$F$14`, allowing a single rule to handle the entire worksheet.
Why is my entire column turning red after applying the formula?
This usually happens due to incorrect absolute and relative references. Ensure that the first argument in your MATCH function (e.g., B3) does not have dollar signs (`$`), so it remains relative, while the source list range (e.g., `$H$3:$H$17`) is absolute with dollar signs.




