How to Remove Duplicate Items in Excel List

There’s a built-in tool in Microsoft Excel that makes it easy to remove all the duplicate values from a list. You can remove duplicates based on the items in one column, or multiple columns.

Video: How to Remove Duplicates in Excel List

The video below shows the steps for removing duplicate items from an Excel worksheet list, using Excel’s built-in Remove Duplicates tool.

The video timeline is below the video, and you can get the sample Excel file on my Contextures site.

Video Timeline

In the video, I start with the steps for working with a 1-column list. After that, duplicates are removed from a 2-column list. Then, in the third example, I use a multiple-column list.

  • 0:00 Excel List With Duplicate Items
  • 0:21 Make Backup Copy of Excel List
  • 0:42 Use Remove Duplicates Tool
  • 1:24 Excel List – Two Column Duplicates
  • 1:58 Select Columns
  • 2:50 Excel List – Multiple Column Duplicates
  • 3:42 Excel List – Multiple Column Example 2

Warning: It’s important to make a backup of your Excel workbook file, or at least the original data list, before you start removing duplicate values.

Remove Duplicates – One Column

In the video above, the first example has a list with one column, and the first cell is the list’s heading.

When you use Excel’s Remove Duplicates tool, the duplicate items are permanently removed.

That’s why it’s good practice to make a backup copy of the data, before you start!

Confirmation Message

In the screen shot below, the Remove Duplicates confirmation message shows

  • how many items were removed from the list
  • how many unique values remain in the list

There’s only an OK button though – you can’t cancel the process at this point.

  • Tip: You can use the Undo command, immediately after clicking OK, if something went horribly wrong!
Remove Duplicates confirmation message
Remove Duplicates confirmation message

Remove Duplicates – Two or More Columns

In the video, the second example has a list with two columns, and the first row has the list’s headings.

When you use Excel’s Remove Duplicates tool, if there are multiple columns, you can:

  • select all columns, to check for duplicate rows
  • select two or more columns, to check for duplicate combinations
  • select a single column, and ignore values in the other column

Two Columns Selected

In the screen shot below, the list has two columns. In the Remove Duplicates dialog box, I’ve selected both columns.

When I click OK, this is what will happen:

  • In row 3, the first instance of the Carrot – 1.50 will be kept
  • All other instances of Carrot – 1.50 will be deleted.
  • In row 5, the first instance of the Carrot – 1.77 will be kept
  • All other instances of Carrot – 1.77 will be deleted
remove duplicates based on multiple columns
remove duplicates based on multiple columns

First Duplicate Item is Kept

When you use the Excel Remove Duplicates tool:

  • the first instance of each item is kept
  • all subsequent duplicate items are deleted

For example, in the video’s multi-column example, I sort the list by the Date field, in Descending order (newest to oldest).

After the duplicates were removed:

  • the latest price for each item was kept in the list
  • all of the older instances were removed

More Remove Duplicate Tips

For more tips and warnings about this built-in tool, go to the Remove Duplicates page on my Contextures site.

For example, you’ll see why duplicate number remain in the list sometimes, for no apparent reason! Or, see how to remove duplicates with the Excel Advanced Filter feature.

You can also download the Remove Duplicates sample file on that page, to follow along with the video, and test the other examples.

__________________

How to Remove Duplicate Items in Excel List

How to Remove Duplicate Items in Excel List
How to Remove Duplicate Items in Excel List

__________________

Original source: https://contexturesblog.com/archives/2023/11/30/remove-duplicate-items-excel-list/?utm_source=rss&utm_medium=rss&utm_campaign=remove-duplicate-items-excel-list

Close Menu