How to copy only unique records?

In this Excel tutorial you will teach yourself how to copy only unique records.

To copy just distinct list of values follow these steps.

Data set

Prepare list of data.

copy only unique records data table list

Go to the ribbon to the Data tab. Click Advanced button.

click advanced button

Filter to apply

Advanced filter window pops up. Fill fields in:

  • tick Copy to another location
  • List range is your data table. $A$1:$A$14 here
  • Criteria range are criteria you like. It depends what you want to copy
  • Copy to is the cell where you want to paste a header of your table
  • tick Unique records only

copy only unique records Advanced Filter


Your unique list is copied.

Unique values copied

This is a good way to copy only distinct values from your list. Excel will do it with just several your clicks.

Remove duplicates

In Excel, you can also copy only unique records by using the Remove Duplicates feature. Here's how to do it:

  1. Select the range of data that you want to copy and press Ctrl + C to copy it.
  2. Go to a new sheet in the same workbook or a new workbook and press Ctrl + V to paste the data.
  3. Go to the Data tab and click the Remove Duplicates button.
  4. In the Remove Duplicates dialog box, select the columns that contain the data that you want to check for duplicates.
  5. Click OK. Excel will remove any duplicate records and keep only the unique records in the paste area.

Note that the Remove Duplicates feature only checks for duplicates within the selected columns. If you want to check for duplicates across multiple columns, you need to select all relevant columns in the Remove Duplicates dialog box.

You can also use formulas or scripting to copy only unique records.