Excel Tip - Create Unique Entries from List of Duplicate Data

You have hundreds if not thousands of data entries in an Excel spreadsheet column, but how can you create a separate list showing only the unique entries and not all the duplicates?

To create this unique list, we will use the example data in the column below – Figure A.

  • Select the entire column of data or select a range of data that you want to extract the unique entries.
  • From the ribbon bar select Data, then click Advanced from the Sort & Filter group.
  • From the Advanced Filter dialog box, ensure the following options are selected – Figure B:
    • Action = Copy to another location
    • List range = the full list of duplicated data
    • Copy to: = the new cell location
    • Unique records only = ticked
  • Click OK.

The unique list of entries will be created in the new column location – Figure C.

Pin it