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.
To set this up, just follow these steps:
- Select C2:D17.
- 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).
- Click the Format button.
- On the Patterns tab, choose yellow, and click OK. In Excel 2007, on the Fill tab, select yellow under Background Color and click OK.
- 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.
- Click OK again to return to your worksheet.
Miss an Excel tip?
Check out the Microsoft Excel archive and catch up on other Excel tips.