Excel 2013 – How to select unique values from column

The steps below detail how to take a column of values, some that are duplicates, and to list the unique values. There are two ways this can be done – replacing the existing column or creating a new column.

For example, our column looks like this:



Method 1: replace the current column

  1. Select the column of data
  2. Open the ‘DATA’ ribbon
  3. Click on the ‘Remove Duplicates’ button
  4. Excel2013-FilterDuplicate2
  5. If your column has a header, click the ‘My data has headers’ tick box
  6. Click ‘OK’
  7. Excel2013-FilterDuplicate3
  8. The original column will now be replace with only the unique values
  9. Excel2013-FilterDuplicate4

Method 2: create a new column

  1. Select the column of data
  2. Open the ‘DATA’ ribbon
  3. Excel2013-FilterDuplicate5
  4. Select ‘Copy to another location’
  5. Make sure that ‘List range’ and ‘Criteria range’ has the same value – if it doesn’t, copy the range from ‘List range’ to ‘Criteria range’
  6. Use the button next to the ‘Copy to’ field to select where to copy the unique values to
  7. Place a tick next to ‘Unique records only’
  8. Click ‘OK’
  9. Excel2013-FilterDuplicate6
  10. You will now have a new list of unique values.
  11. Excel2013-FilterDuplicate7