Excel Formulas

Compare and highlight differences in Excel columns

Let’s say you have List A and List B, both starting in A2 and B2

In C2, enter this:

=IF(COUNTIF($B$2:$B$14, A2)=0, A2, "")

Drag-copy that down column C as far as needed.

If it appears in List A, but not in List B, it shows in Col C.

A Ridiculously easy and fun way to compare 2 lists

  1. Select cells in both lists (select first list, then hold CTRL key and then select the second)
  2. Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values
  3. Press ok.
  4. There is nothing do here. Go out and play!

Excel how to find values in 1 column exist in the range of values in another



Compare each cell from column A with each cell from column B and column C


Count unique values in a column in Excel


Used as part of conditional formatting.

IF F2 is (<,>,=) H2 then colour cell as defined in conditional formatting.


Finding missing values

Look at cell A2 and compare it with sheet Week_4 cells A2-H25,000.  If A2 exists enter the value of column 4 into this cell.  If not enter “Nay There”.

=IFERROR(VLOOKUP($A2,Week_4!$A$2:$H$25000,4,FALSE),"Nay There")