Mark duplicate entries automatically with Excel's COUNTIF function

Sometimes, it's handy to get a quick view of data that matches up between two columns. By combining an Excel function and the conditional formatting feature, you can easily accomplish this.
When you need to quickly compare two columns of data for duplicates entries, you can use Excel's conditional formatting with the COUNTIF function. For example, suppose you want to know which properties' selling prices matched their list prices in the worksheet shown in Figure A.

Figure A

To set this up, just follow these steps:

  1. Select C2:D17.
  2. Go to Format | Conditional Formatting. In Excel 2007, click Conditional Formatting on the Home tab and choose New Rule (Figure B). Then, click Use A Formula To Determine Which Cells To Format (Figure C).

Figure B

Figure C

  1. Click the Format button.
  2. On the Patterns tab, choose yellow, and click OK. In Excel 2007, on the Fill tab, select yellow under Background Color and click OK.
  3. Choose Formula Is from the Condition 1 drop-down list and enter the following formula: =COUNTIF($C$2:$D$17,C2)>1. In Excel 2007, you'll enter the formula in the Format Values Where This Rule Is True text box.
  4. Click OK again to return to your worksheet.

Figure D shows the results. The COUNTIF function counts all the numbers that are repeated in the range C2:D17 and then shades all cells that have duplicate entries in yellow. As you can see, three properties sold for their list prices (that is, each property's selling price in column D is a duplicate of its list price in column C).

Figure D

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Editor's Picks