How to quickly remove duplicates in Excel

Our October 2nd blog post was about How to quickly find duplicates in Excel. This week’s post shows how to quickly remove duplicates in Excel.

Suppose, like last time, that you have a long list of 5406 movies as shown below, and suppose that you need to get a listing of the categories, but without all the duplicates.

To practice the steps below, feel free to download our Movies.xls file.

To get your unique list do the following:

  1. Create a new sheet by clicking the new sheet icon.

  2. Return to the original sheet.


  3. Highlight the category column.

  4. Copy the column by pressing Control C.


  5. Go to the new sheet and in cell A1 press enter to paste the data.

  6. With the data now highlighted in its own sheet, choose Data (tab), Data Tools (Group), Remove Duplicates (icon).

    The Remove Duplicates dialog box will be displayed.

  7. If necessary check the “My data has headers” option because our list starts with a column heading.


  8. Click OK.

    A dialog box appears telling you that duplicates were removed and the number of unique values that remain.

To learn more tips and tricks like these, please join our online newsletter community at Upon subscribing, you will receive a free copy of our Excel Functions Presentation.

This entry was posted in Excel. Bookmark the permalink.

Leave a Reply