Imagine you have a table with millions of rows containing contact details….okay so maybe not millions :), but its a long list!  How do you know that the data you have in there isn’t duplicated?

There is an easy way of checking and removing any duplicate data without having to manually go through the list yourself.

Firstly click anywhere within the table, then make sure you’re in the Data tab.

There are a couple of ways to deal with duplicated data, it all depends whether you want to hide it or remove it.  Also, you must remember that, in order for Excel to recognise it as duplicate data, the information has to be identical – for example J. Bloggs and J Bloggs would not be classed the same as the full stop is missing.  It has to be the same!

HIDE

To hide any duplicates, go to the Sort & Filter section of the Data tab, and click Advanced.

A new dialogue box will open, and your table will automatically be highlighted as the List range.  Tick the Unique records only box, and leave the “Filter the list, in-place” ticked, then press OK.

This is basically instructing Excel to show the unique data only in the same place as the existing table i.e. get rid of any duplicated entries.  You’ll notice that J. Bloggs in row 13 is now hidden and the table is only showing unique information.

REMOVE

To completely remove the data, Excel 2007 has a feature called (surprisingly :)) Remove Duplicates.

Again, this is in the data tab of the ribbon, make sure you’ve selected any cell within the table and click Remove Duplicates.

This opens a new dialogue box where you can select which columns you want included in the search for duplicates.  In this example, we’ll leave all selected, and click OK.

Immediately, if there any duplicates, another alert will come up saying how many duplicate values were found and removed.

And you’ll notice that J. Bloggs in row 13 has been completely removed.

One more thing to bear in mind when removing duplicates is that Excel removes the displayed information, and not what is necessarily shown in the formula bar.  For example, the date may be displayed as 10/10/2011 in one cell and 10-Oct-11 in another, although its the same date, they are displayed differently therefore Excel won’t recognise them as being duplicates.

If you have any questions about using this feature, please feel free to ask in the comment section below – thanks 🙂