Note: This works when you’re working with multiple columns. First, open the Excel file you want to check. Then click the Data tab.
Click on Advanced from Sort and Filter options. Select Copy to Another Location under action menu.
Now highlight the range field to select the data you want to check for duplicate entries. Also select the area where you want to paste the filtered results, check the Unique Fields only option and click OK.
Excel displays the result in the area selected excluding any duplicate entry. Select a new sheet or workbook depending on your needs. The procedure is a bit different when you want to filter a single column. If you use the above procedure to find duplicate entries from a single column sheet, you’ll get the following error.
To find duplicate entries from a single column, use the conditional formatting feature in Excel. Click the Home tab and select Conditional Formatting.
Expand the Conditional Formatting menu and go to Highlight Cell Rules » Duplicate values.
The Duplicate Values field comes up. Change formatting of the duplicate entries. For example, I selected to fill the duplicate entries with red background and color. Click OK when done.
It will highlight the duplicate entries in the selected color. This makes them easier to distinguish from the other fields.
If you have a large Excel spreadsheet and want to easily find duplicate entries, this method is simple and easy to use. No third party plugin required. keving Comment
Δ
