Excel: Remove Duplicates

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I have a data set in which I would like to find every unique combination of customer and product.

  1. Find unique combinations of customer and product.

    Strategy: Although there are several ways to find unique values (advanced filters, pivot tables, Microsoft Query, COUNTIF), Microsoft added a new feature to Excel 2007 called Remove Duplicates.

    Remove Duplicates is a powerful feature-sometimes too powerful because it very quickly and destructively removes the duplicated rows.

    To use the Remove Duplicates command, follow these steps:

    1. Make a copy of your data. Copy it to a new range, a new worksheet, or a new workbook.
    2. Select one cell in your data set.
    3. Select Data, Remove Duplicates. Excel will display the Remove Duplicates dialog.
    4. Click Unselect All. Select Product and Customer.

  2. Choose which columns should be analyzed.
    1. Click OK. Excel will confirm how many duplicates were found and removed.

  3. Duplicates removed.

    Results: Excel will delete hundreds of rows of data! If you didn't make a copy in step 1 and you need that data, press Ctrl+Z to undo.