## Highlight missing values between to columns

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 applied to B3:B7:

The conditional formatting formula applied to D3:D7:

### Explaining CF formula applied to B3:B7

The COUNTIF function counts the number of time the value in cell B3 exists in cell range D3:D7.

Cell |
COUNTIF |
Result |

B3 | COUNTIF($B$3:$B$7, B3) | 0 |

B4 | COUNTIF($B$3:$B$7, B4) | 1 |

B5 | COUNTIF($B$3:$B$7, B5) | 1 |

Now if the COUNTIF function returns 0 (zero) that means that the value is not found in the other cell range and the logical expression returns TRUE.

COUNTIF($D$3:$D$7, B3)=0

becomes

0=0

and returns TRUE.

Cell B3 is highlighted, the value is not found in the other list.

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

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

(Excel 97-2003 Workbook *.xls)

Compare two lists of data: Highlight common records

Overview In this blog post you will learn how to: Create a countifs function Create a conditional formatting formula and […]

Compare two lists of data: Highlight records occurring in only one list

Overview There are two lists in this example: Sheet: List 1 Sheet: List 2 Create named ranges Select A2:A13 on […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Highlight dates in a date range

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

Identify missing numbers in a column

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Array Formula in D6 =SMALL(IF(ISERROR(MATCH($E$2+ROW(OFFSET($B$2, 0,0, […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Identify missing numbers in a range

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form