I have two columns in Excel, and I want to find (preferably highlight) the items that are in column B but not in column A.

What's the quickest way to do this?

8 Answers 8

  1. Select the list in column A
  2. Right-Click and select Name a Range...
  3. Enter "ColumnToSearch"
  4. Click cell C1
  5. Enter this formula: =MATCH(B1,ColumnToSearch,0)
  6. Drag the formula down for all items in B

If the formula fails to find a match, it will be marked #N/A, otherwise it will be a number.

If you'd like it to be TRUE for match and FALSE for no match, use this formula instead:


If you'd like to return the unfound value and return empty string for found values

  • 7
    Then... Put this formula into Conditional Formatting in both lists and and use it to highlight (or whatever) the non-matching cells
    – Kije
    Dec 10, 2009 at 19:55
  • 7
    "Name a Range" doesn't appear for me? Tested on Excel 2010 and 2016.
    – KERR
    Jul 14, 2017 at 1:13
  • If, like @KERR and I, you don't have "Name a Range...", just use A:A or your range in lieu of ColumnToSearch in the formulae.
    – Michael
    Feb 13, 2018 at 18:21
  • 5
    "Name a Range" appears as Define Name... (Excel 2019)
    – m-smith
    Aug 12, 2019 at 13:55
  • 1
    Also "Name a Range" only appears if you select a subset of the column, clicking the column header to select the entire column doesn't let you name a range.
    – Quantic
    May 3, 2021 at 19:38

Here's a quick-and-dirty method.

Highlight Column B and open Conditional Formatting.

Pick Use a formula to determine which cells to highlight.

Enter the following formula then set your preferred format.

  • 1
    I used "=ISODD(COUNTIF(A:A,B1)=0)" to print true for '1' or false for '0'
    – Ramraj
    Sep 20, 2016 at 10:13
  • For unknown reasons the countif() method doesn't work reliably when specifying a multi-column array (e.g. =countif(B:C,A1)=0) - Excel 2016.
    – HamishKL
    Mar 1, 2021 at 4:08

Select the two columns. Go to Conditional Formatting and select Highlight Cell Rules. Select Duplicate values. When you get to the next step you can change it to unique values. I just did it and it worked for me.

  • if you have a value in column B repeated, this will not highlight them
    – magodiez
    Nov 13, 2015 at 16:18
  • 2
    Surely this is the simplest and most relevant answer? No formulas required.
    – KERR
    Jul 12, 2017 at 23:24
  • Nothing happens when doing this.
    – Overmind
    Oct 16, 2019 at 8:19
  • 1
    Easily the best answer. Simplest.
    – SexyBeast
    Feb 11, 2020 at 19:11

Took me forever to figure this out but it's very simple. Assuming data begins in A2 and B2 (for headers) enter this formula in C2:


Then click and drag down.

A cell with #N/A means that the value directly next to it in column B does not show up anywhere in the entire column A.

Please note that you need to change $A$287 to match your entire search array in Column A. For instance if your data in column A goes down for 1000 entries it should be $A$1000.

  • 6
    =MATCH(B2,$A:$A,0) worked for me.
    – nilgun
    Sep 6, 2016 at 10:37

See my array formula answer to listing A not found in B here:


Comparing two columns of names and returning missing names

  • This solution is good, but depends on the source columns being sorted alphabetically, or else it retrieves duplicates. Jan 4, 2017 at 8:36

My requirements was not to highlight but to show all values except that are duplicates amongst 2 columns. I took help of @brenton's solution and further improved to show the values so that I can use the data directly:

=IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), A2, "")

Copy this in the first cell of the 3rd column and apply the formula through out the column so that it will list all items from column B there are not listed in column A.

  • 1
    There is an error in the formula above =IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), A2, "") should be =IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), B2, "")
    – user374241
    Oct 1, 2014 at 17:48

Thank you to those who have shared their answers. Because of your solutions, I was able to make my way to my own.

In my version of this question, I had two columns to compare -- a full graduating class (Col A) and a subset of that graduating class (Col B). I wanted to be able to highlight in the full graduating class those students who were members of the subset.

I put the following formula into a third column:


This coded most of my students, though it yielded some errors in the first few rows of data.


in C1 write =if(A1=B1 , 0, 1). Then in Conditional formatting, select Data bars or Color scales. It's the easiest way.

Not the answer you're looking for? Browse other questions tagged or ask your own question.