## Highlight missing values between to columns

*Article updated on December 22, 2017*

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? And vice versa? To do this I use conditional formatting.

To extract a list of missing values using excel formula, check this blog post What values are missing in List 1 that exists i List 2?

The conditional formatting formula in A2:A6:

=COUNTIF(List2, $A2)=0

The conditional formatting formula in B2:B6:

=COUNTIF(List1, $B2)=0

**Named ranges**

List1 (A2:A6)

List2 (B2:B6)

What is named ranges?

**How to customize the formula to your excel workbook**

Change the named ranges and change the cell reference $A2 in formula =COUNTIF(List2, $A2)=0 to the starting point of your list

**Download excel example fil****e**

lookup-between-two-lists-of-data-to-highlight-missing-data.xls

(Excel 97-2003 Workbook *.xls)

**Functions used in this blog post:**

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article