Filter or remove duplicates from your Excel data

If you're wading through your Excel data and find a lot of redundant entries, you have two options to narrow your focus. You can filter and hide any duplicate rows, or just delete any repeated entries completely. Best of all, these work in both Excel 2007 and Excel 2010.

First, let's look at the less destructive path. In most cases, you want your data intact - you just want to home in on the unique values in your worksheet. Select the columns you want to filter, go to the Data tab, and click the Advanced button in the Sort & Filter section:

Filter - Advanced

Now check Unique records only and, if you like, select a destination to copy your results:

Unique records only

Whether you copy or filter in place, any duplicate records will be hidden - all you need to do is select the full sheet , right-click, and select Unhide to restore it.

Now if you want duplicate data removed entirely, look in the next section on the Data tab for Remove Duplicates:

Remove Duplicates

This opens up a dialog box that lets you choose which columns to include. Pick your targets and permanently delete any redundant data:

Select columns to remove duplicates


Comments (1)

  1. Dido says:

    waow! You just helped me a lot. I am very happy and very grateful. It was so useful as i have used it to filter over 8919 items in my excel spread sheet and i will continue using it as i do add items to it weekly.

    Once again, I truly appreciate. Lest I forget, please can i get someone who can really teach me on programming? Different types of programming please.

Skip to main content