Formatting Issues

How to Use Excel Conditional Formatting for Valid and Invalid Locations

Steve KSteve K Nov 11, 2026 868 views

Question details

The user needs to automatically highlight valid and invalid location codes entered into a spreadsheet template using conditional formatting.

How to Use Excel Conditional Formatting for Valid and Invalid Locations
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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the Reference List

Enter all valid location codes in a dedicated range, for example, from cell B1 to B50.

2
Select the Target Range

Highlight the cells where users will enter the location codes (e.g., cell A1 or range A1:A100).

3
Apply Formatting for Invalid Entries

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.

4
Apply Formatting for Valid Entries

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.

Highlight Valid and Invalid Locations Using MATCH Formulas
Absolute References: Make sure your reference list range uses absolute cell references (like $B$1:$B$50) so the matching logic remains stable across all the target cells.
Advanced Spreadsheet Tool

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. 1. Download and Install: Get WPS Office for free from the official website and open your spreadsheet file.
  2. 2. Access Conditional Formatting: Navigate to the Home tab and click on 'Conditional Formatting' to create your rules.
  3. 3. Apply Formulas: Use the 'New Rule' option to apply your ISNA and ISNUMBER formulas exactly as you would in Microsoft Excel.
Fully compatible with Microsoft Excel formulas like MATCH, ISNA, and ISNUMBER.Clear and intuitive conditional formatting rule manager.Free and lightweight spreadsheet tool for everyday data processing and template building.
microsoft office alternative - wps office

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.