Pin Me

Copying Only Unique Values in Excel 2013

written by: •edited by: Tricia Goss•updated: 2/23/2016

Several Excel functions and tools require knowing the unique values contained in a dataset. The simplest solution is Excel 2013’s Advanced Sort & Filter feature that can copy only unique values from a long list of records, so you can paste them to a location of your choosing.

  • slide 1 of 6

    1. Select the Data tab and click Advanced in the Sort & Filter group.

  • slide 2 of 6

    2. Enter the List Range that contains the data you need, including the header. Rather than manually typing the range, use the range selection button in the List Range field: click the button, drag your mouse cursor across the data and then click the button again to automatically enter the range. You want to include the header, because Excel will assume the first entry in the selected text is the header, which could copy duplicates if the first two values are the same.

  • slide 3 of 6

    3. Select Copy to Another Location and enter the desired cell reference of the output in the Copy to field. This location should not have any data that would be deleted by the procedure.

  • slide 4 of 6

    4. Check Unique Records Only and click OK.

  • slide 5 of 6

    5. View the results starting in the cell you previously selected in the Copy to field.

  • slide 6 of 6

    If you don’t need the header, you can safely delete it now.

    Note that the list is not automatically sorted, as you can see by the incorrectly ordered G and F at the bottom of the example. However, you can click Sort in the Sort & Filter group to sort the output.