Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to highlight values not in another list in Excel and Google Sheets
Topic:Conditional formattingExcel & Google Sheets
=COUNTIF($C$2:$C$6,A2)=0✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Invoice | Ref List |
| INV-001 | INV-001 |
| INV-002 | INV-003 |
| INV-003 | INV-005 |
| INV-007 |
=COUNTIF($C$2:$C$6,A2)=0→TRUE
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put your primary list in column A and your reference list in column C.
- 2Select the range in column A to check, e.g. A2:A20.
- 3Open Conditional Formatting, choose 'Use a formula', and enter =COUNTIF($C$2:$C$100,A2)=0. A count of 0 means the value is absent from the reference list. Set a highlight color and save.
Tips
- Always lock the reference list range with absolute references ($C$2:$C$100) so it does not shift as the rule is evaluated for each row.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if I want to highlight values that ARE in the other list instead?
Change =0 to >0: =COUNTIF($C$2:$C$100,A2)>0 will highlight only the values that do appear in the reference list.
More on Conditional formatting
See all →How-to
How to highlight duplicate values
Highlight duplicate cells automatically with Conditional Formatting using a COUNTIF rule — no formulas to copy manually.
How-toHow to highlight cells greater than a value
Use Conditional Formatting with a simple greater-than rule to automatically color cells that exceed a threshold value.
How-toHow to highlight the highest value in a range
Color the maximum value in a range automatically using a Conditional Formatting rule based on the MAX function.
How-toHow to highlight the lowest value in a range
Color the minimum value in a range using a Conditional Formatting rule built on the MIN function.
How-toHow to highlight blank cells
Use conditional formatting with ISBLANK to automatically highlight empty cells in your spreadsheet for easy visual identification.
How-toHow to highlight cells that contain specific text
Use conditional formatting with SEARCH or ISNUMBER(SEARCH()) to highlight any cell containing a specific word or phrase automatically.