Top Nav

Microsoft Excel: Find Duplicate Entries the Easy Way

When working with large excel files, people tend to make mistakes and add duplicate entries in the worksheets. Here’s how to find duplicate entries in Excel spreadsheets the easy way.

Note: This works when you’re working with multiple columns.

First, open the Excel file you want to check. Then click the Data tab.

Excel Duplicate-1

Click on Advanced from Sort and Filter options.
Excel Duplicate-2

Select Copy to Another Location under action menu.

Excel Duplicate-3

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 Duplicate-4

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.

Excel Duplicate-5

To find duplicate entries from single column, use the conditional formatting feature in Excel. Click the Home tab and select Conditional Formatting.

Excel Duplicate-6

Expand the Conditional Formatting menu and go to Highlight Cell Rules >> Duplicate values.

Excel Duplicate-7

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.

Excel Duplicate-8

It will highlight the duplicate entries in the selected color. This makes them easier to distinguish from the other fields.

Excel Duplicate-9

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 plug in required.

, ,

5 Responses to Microsoft Excel: Find Duplicate Entries the Easy Way

  1. Tenika February 22, 2012 at 11:31 am #

    Remove Duplicates in the Data section of the ribbon is also a useful & simple way to remove duplicates from within one or several columns.

  2. No February 29, 2012 at 2:36 pm #

    ..an even easier way is to capture your database and run a pivot table report, selecting the column that has the suspected duplicates as the data target and choose “count”. Once its run – sort on the count results and all the duplicates will be at the top (or bottom) showing the number of times excel found the same entry.
    If you want you can the double click the count value result and a new sheet will open showing the guilt-ridden items.
    You can also use the same method if you need to combine more than one column, just concatenate the chosen columns eg (=B6&C6&F6) use this new column as the data target – ditto above. Make sure all the database is captured when using the pivot table.

  3. Abdul April 26, 2012 at 5:39 am #

    O I love this post . . . Thnx

  4. Tom May 8, 2012 at 4:29 am #

    Just an FYI…but the Excel Remove Duplicates function is flawed and cannot be relied upon when dealing with large amounts of data. Formatting gets in the way.

    • Dominique Pere August 14, 2012 at 8:45 am #

      I agree with you..this is one of the reason which has propelled us to build Podbox. A web-app acts as a complimentary application to support cleaning features from excel and any various softwares. Find duplicates entries and de-deplicate them without breaking a sweat with Podbox.

Leave a Reply