How to Use Excel Conditional Formatting for Valid and Invalid Locations
Question details
The user needs to automatically highlight valid and invalid location codes entered into a spreadsheet template using conditional formatting.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Validating data entry when a template supports multiple specific location codes.
- Observed behavior
- The goal is to apply specific formatting, such as green for valid codes and red for invalid ones, based on a predefined list of allowed locations.
Ensure you have created a reference list of all valid location codes in your worksheet (for example, in column B) before applying the conditional formatting rules.
Highlight Valid and Invalid Locations Using MATCH Formulas
Use custom formula rules in Conditional Formatting to check if entered values exist in a predefined reference list.
By combining the MATCH function with ISNUMBER and ISNA, you can dynamically verify if a cell's value is present in your reference list and apply distinct colors for correct and incorrect entries.
Enter all valid location codes in a dedicated range, for example, from cell B1 to B50.
Highlight the cells where users will enter the location codes (e.g., cell A1 or range A1:A100).
Go to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'. Choose 'Use a formula to determine which cells to format'. Enter the formula =ISNA(MATCH(A1,$B$1:$B$50,0)), click 'Format', select a red fill color, and click OK.
Create another new rule for the same target range. Choose 'Use a formula to determine which cells to format' and enter the formula =ISNUMBER(MATCH(A1,$B$1:$B$50,0)). Click 'Format', select a green fill color, and click OK.

Easily Manage Conditional Formatting with WPS Spreadsheet
WPS Spreadsheet provides a highly intuitive interface for setting up complex conditional formatting and data validation rules, helping you prevent data entry errors effortlessly.
- 1. Download and Install: Get WPS Office for free from the official website and open your spreadsheet file.
- 2. Access Conditional Formatting: Navigate to the Home tab and click on 'Conditional Formatting' to create your rules.
- 3. Apply Formulas: Use the 'New Rule' option to apply your ISNA and ISNUMBER formulas exactly as you would in Microsoft Excel.

Frequently Asked Questions
Can I use Data Validation instead of Conditional Formatting?
Yes. While Conditional Formatting highlights errors with colors, Data Validation physically prevents users from typing an invalid location. Go to Data > Data Validation, choose 'List', and select your reference range to create a drop-down menu.
Why is my conditional formatting applying to the wrong cells?
This usually happens if you use relative references instead of absolute references (using the $ symbol) for your reference list, or if the active cell selected when writing the formula doesn't match the first cell referenced in your formula (like A1).
Can I highlight the entire row based on the location code?
Yes. Select the entire data range instead of just the location column. Then, in your conditional formatting formula, lock the column reference for the target cell (e.g., use $A1 instead of A1) so the color format applies across the entire row.




