If you can use a helper column, you can use the
MATCH function to test if a value in one column exists in another column (or in another column on another worksheet). It will return an Error if there is no match
To simply identify duplicates, use a helper column
Assume data in Sheet1, Column A, and another list in Sheet2, Column A. In your helper column, row 1, place the following formula:
Drag/copy this forumla down, and it should identify the duplicates.
To highlight cells, use conditional formatting:
With some tinkering, you can use this
MATCH function in a Conditional Formatting rule which would highlight duplicate values. I would probably do this instead of using a helper column, although the helper column is a great way to “see” results before you make the conditional formatting rule.
For Excel 2007 and prior, you cannot use conditional formatting rules that reference other worksheets. In this case, use the helper column and set your formatting rule in column A like:
This screenshot is from the 2010 UI, but the same rule should work in 2007/2003 Excel.