Highlight Values Not in an Allowed List

Excel Formulas › Conditional Formatting

All versionsCOUNTIF

Flag entries that aren’t on an approved list — invalid codes, unknown products, typos. A COUNTIF rule lights up anything missing from your reference list.

Quick formula: with an allowed list in $E$2:$E$10, select your data and add:

=COUNTIF($E$2:$E$10, A1) = 0

COUNTIF returns 0 when the value isn’t found in the list — that’s the “not allowed” case to highlight.

Functions used (tap for the full reference guide):

COUNTIF

The example

Entries not on the approved list are flagged.

	A	B
1	Entry	Status
2	Apple	ok
3	Banaan	not allowed

The formula

Flag what’s missing from the list:

=COUNTIF($E$2:$E$10, A1) = 0 // 0 occurrences = not allowed

How it works

COUNTIF checks membership:

COUNTIF(list, value) counts how many times the value appears in the allowed list.
A count of 0 means it’s not on the list — that’s what to highlight.
Lock the list range with $ so every cell checks the same list.
Flip to > 0 to instead highlight the values that are allowed.

Prevent the bad entry entirely with Data Validation: a List rule blocks anything off the list as it’s typed. Use CF to audit existing data, validation to stop new mistakes — they pair well.

Try it: interactive demo

Live demo

Allowed: Apple, Pear, Plum. Type entries.

Variations

Highlight the allowed

Flip the test:

=COUNTIF($E$2:$E$10, A1) > 0

Block on entry

Data Validation → List.

Count the offenders

How many invalid:

=SUMPRODUCT(--(COUNTIF(list, data)=0))

Pitfalls & errors

Lock the list. Use $E$2:$E$10 absolute, or each row checks a shifting range.

Case-insensitive & spaces. COUNTIF ignores case but not stray spaces — “Apple ” won’t match “Apple.” TRIM the data if needed.

Wildcards in the list value. A list entry containing * or ? is treated as a wildcard by COUNTIF.

Practice workbook

📊

Download the free Highlight Values Not in an Allowed List practice workbook

A not-in-list CF rule against an allowed list, the allowed and count variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I highlight values not in a list in Excel?

Add a formula CF rule =COUNTIF($list, A1) = 0. COUNTIF returns 0 when the value isn't in the allowed list, flagging it.

How do I highlight the allowed values instead?

Flip the test to > 0: =COUNTIF($list, A1) > 0.

How do I stop invalid entries being typed?

Use Data Validation → List against the allowed range; it blocks off-list entries on input, while CF audits existing data.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: COUNTIF