Using Advanced Sort & Filter in Excel to Copy Unique Values

Page content

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

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.

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.

4. Check Unique Records Only and click OK.

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

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.