1. Select the range (in this case, A1:B18).
2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.
3. Select Formula Is from the first drop-down list box, and enter =COUNTIF($A$1:$B$18,A1»1 in the second box.
4. Click the Format button to bring up the Format Cells dialog box.
5. Select the Patterns tab, and specify a background color.
6. Click OK twice to return to your worksheet.
7. If the range contains any duplicate entries, they will be flagged with the background color you chose in Step 5.
You can adapt this technique to any other worksheet by changing the ranges used in the COUNTIF function. The first argument should be the full address of the range you selected–and make sure that you use the dollar signs to signify an absolute reference. The second argument should be the address of the upper left cell of your range, expressed as a relative reference (no dollar signs).
Special Thanks to PCWorld as the original source of the above documents.