Being able to find duplicates in Excel is a very valuable skill to learn, as it enables you to quickly clean up data and troubleshoot problems. Sometimes you just need to locate the duplicates, other times you need to find and remove them. Regardless of your end-goal, this tutorial will teach you two different ways to find duplicates in Excel and how to quickly remove them (if you so desire).
1. Remove Duplicates Button
The most obvious way to find and remove duplicate values in Excel is to simply use the Remove Duplicates button (in the Data Tools group on the Data Tab). The functionality is actually pretty flexible, allowing you to select either a single column or a whole series of columns and then removing duplicates based on the criteria that you input. So, you can either remove duplicates from a single column (while leaving the rest of the table intact) or delete an entire row if it contains a duplicate value in one or more columns that you specify. So, if you just need to quickly clean up your data and completely remove any duplicate values the dataset, the Remove Duplicates button is the way to go.
2. Conditional Formatting
If you simply want to find duplicates in Excel (without necessarily deleting them), the best way to accomplish that is with Conditional Formatting. Simply use the Highlight Duplicate Values option. Once you’ve set the formatting, you can then apply a filter to the column with duplicate values and filter down to the duplicates. At that point, you can either edit, delete, or just ignore the duplicate values. The best part about using the Conditional Formatting method to find duplicates in Excel is that it’s dynamic! So, if you deciede to delete a few of your duplicates (or add in some more duplicates later), the Conditional Formatting will automatically update. Simply refresh the Filter to see the most current duplicates.
Learn to Find Duplicates in Excel and Much More!
Sign up now for our free email newsletter: Not only will you receive Excel tips and tricks directly in your inbox, you’ll also receive a free Gantt Chart template, which you can use to plan your next project.