Formatting Issues

How to Highlight Excel Cells When Data Validation Value Becomes Invalid

John WilsonJohn Wilson Nov 10, 2026 868 views

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.

How to Highlight Excel Cells When Data Validation Value Becomes Invalid
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target range

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.

2
Open Conditional Formatting

Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

3
Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

4
Enter the validation formula

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))`.

5
Set the highlight format

Click the 'Format' button, go to the 'Fill' tab, and choose a prominent color like red. Click 'OK' twice to apply the rule.

Use Conditional Formatting with the MATCH Formula
Dynamic Updates: Once applied, anytime you remove an item from your source list in H3:H17, any cell in your data range containing that removed item will instantly turn red.
Manage Data Validation with WPS Office

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. 1. Open your worksheet: Launch WPS Spreadsheet and open the file containing your data validation lists.
  2. 2. Access Conditional Formatting: Select the cells you want to monitor, go to the 'Home' tab, and click on 'Conditional Formatting'.
  3. 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. 4. Apply and format: Click 'Format' to choose your highlight color (e.g., Red fill), then click 'OK' to instantly flag invalid data.
100% compatible with Microsoft Excel (.xlsx) conditional formatting rules and formulas.Completely free and lightweight Office suite alternative.Intuitive interface for managing complex data validation and custom formatting rules.Seamless migration of your existing Excel workbooks without losing formatting.
microsoft office alternative - wps office

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.