Once you have 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. You’ll find the Remove Duplicates command on the Data tab of the ribbon, in the Data Tools group. Remove Duplicates will display a list of column headers below, with buttons at the top to quickly uncheck and check all columns. In a table with headers, the header checkbox will also be checked. In this case, with both City and State checked, Excel will remove rows where both the city and state are the same. If I only want a list of unique states, ignoring city values, I can just uncheck the checkbox for city. 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 just want to remove exact duplicates – In other words, – rows where all values are the same – I leave all columns checked. Notice, if I undo this, and make a very small change to a duplicate row, one less duplicate is removed because the row is now unique. If I only want a list of unique cities, I check cities only. Finally, if I want to extract a list of unique values to another location on the worksheet, I can use Advanced Filter, on the Data tab of the ribbon. For example, to extract a list of unique states… I first enter “State” as a header elsewhere on the worksheet. Then I open 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
