How to remove duplicates from a table

when you land data in a table, it’s easy to remove duplicates.

Let’s go through some examples.

In this first worksheet, I have a table with a list of US cities and states.

Some of the entries are duplicates.

In a table with headers, the header checkbox will also be checked.

The states column now contains a complete list of unique state names.

Now let’s look at some data with more columns.

This table contains 30 rows, some of which are exact duplicates.

If I only want a list of unique cities, I check cities only.

For example, to extract a list of unique states…

I first enter “State” as a header elsewhere on the worksheet.

Then I bring up the Advanced Filter dialog and select “Copy to another location”.

For List Range, I use the State column.

And, for the destination, I use the cell below the header.

Finally, I check “unique records only”.

When I click OK, Excel builds a separate list of unique state names.