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:

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

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:

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

Posted in: Microsoft Office