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.


The Countif function gives how many times a specific number appears in the range, regardless of its position. That is why cells D6, D12, D14 and D16 appear in yellow. The number is repeated, but not at left, which I believe is the intention. Change the number in D7 to 390000, and the property above is painted, giving a false positive. If you wish to compare the listed versus the selling price in the same line, go the cell C2 and write the following formula in the conditional format box: =$C2=$D2. Then copy the format to the rest of the range.


Countif has its uses, but your expression is the shorter and simpler to put into the conditional format box for this example. The countif function could work with the correct argument - countif(c2:d2,c2)>1. No need for any absolute references. I guess there is often more than one way to get the job done.


... about "more than one way". Of course it depends on your knowledge level of the program, but also on what you intend to do with the worksheet. For example, in this instance a new column with a formula with something like =IF(B2=C2,"Same","") would allow you to count the "sames" and know how many you sell at the asked price. Or instead of "Same" you could simply write the formula =B2-C2 which would allow to know how much money you are making (or losing). The possibilities are almost endless and that's the fun of it.

Editor's Picks