Software

Let Excel 2007's new Remove Duplicates feature find unique values in your data

Avoid redundancy with Excel 2007's new Remove Duplicates feature and easily produce a list of unique values for more than one field.

It is the end of the month and you need to write a quick memo to report on last month's sales. You have a spreadsheet listing 100 invoices for the month, including the Invoice Number in Column A, the Customer Name in Column B, and the Product Name in Column C. Because most of your customers place orders several times a month, you need a quick way to generate a listing of each customer that ordered from you last month.

Before Excel 2007, you would obtain this data using either Excel's Advanced Filter or Pivot Table feature. With Excel 2007's new Remove Duplicates feature, you have an additional, even quicker, way to produce this list. Follow these steps:

  1. Select the Customer Name column.
  2. Copy the data to a blank section of the worksheet. (You should leave at least one blank column between the original data and the copied data.)
  3. Click inside any cell within the copied cells.
  4. Click the Data tab.
  5. In the Data Tools group, choose Remove Duplicates, then click OK.

Excel removes all duplicate customer names from the copied list. You can also use Remove Duplicates to produce a list of unique values for more than one field. For example, to generate a list of customers and the products they ordered, follow these steps:

  1. Select the Customer Name and Product Name columns.
  2. Copy the selected data to a blank area of the worksheet.
  3. Click inside any cell within the copied cells.
  4. Click the Data tab.
  5. In the Data Tools group, choose Remove Duplicates, then click OK.

Excel generates a list of each customer and the products ordered, excluding duplicate orders for the same product from the same customer. You can then use the Sort function to make the list easier to read by following these steps:

  1. Click any one of the cells in the Remove Duplicates results list.
  2. Click the Data tab.
  3. Click the Sort button.
  4. Click the My Data Has Headers check box.
  5. Click the Sort By drop-down arrow and select Customer Name, then click OK.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks