Excel: Find Duplicates

The built-in Microsoft Excel feature to remove duplicates from a set of data is great, but what if you just want to find the duplicates and not actually remove them?

You can use Conditional Formatting to have Excel automatically find duplicates and highlight them for you. First, select the cells in which you’d like to find duplicate values. In most cases this is going to be entire rows or columns. Next, click the ‘Conditional Formatting’ button on the ‘Home’ tab of the ribbon. Go to ‘Highlight Cells Rules’, and then choose ‘Duplicate Values…’.

The ‘Duplicate Values’ window will come up, which allows you to make a couple of choices. First, choose ‘Duplicate’ or ‘Unique’ from the drop-down list. The next drop-down list lets you choose how you’d like the value to be marked. There are some preset options available to you, or you can choose ‘Custom Format…’ and set your own. When you’re done, click the ‘OK’ button and you’ll see that Excel has gone through and formatted the unique or duplicate values in the format that you have chosen.


Post a Comment