In past versions of Excel, there was a several-step process using a COUNTIF formula in Conditional Formatting to highlight duplicate values in your data.
Starting with Excel 2007, however, finding and highlighting your duplicate values suddenly became a lot easier to manage.
Follow the steps below to learn how:
- On the Home tab of your Ribbon, simply click Conditional Formatting.
- Click Highlight Cell Rules | Duplicate Values.
- Click OK.
Now be aware that this is not just for a quick look at the duplicate values in your worksheet. Once you have done this, you have applied Conditional Formatting to the range of cells you selected so that in the future, when you add more data, any duplicates will also be highlighted automatically. So the bottom line is, you can apply this formatting to any range of cells in which you do not want duplicate values.