Excel: Create a Unique List of Customers with a Pivot Table

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 need to create a unique list of customers from a large list.

Strategy: You can build a pivot table report with Customer in the Row area of the layout. Because the pivot table creates a summary report, the first column of the table will include the unique list of customers. Here's what you do:

  1. Select Insert, Pivot Table. Click OK in the Create PivotTable dialog.
  2. Click the Customer field in the PivotTable Field List dialog.

That's it. You are done. You can now copy the customers from column A of the new sheet and use Paste Values to put the unique list of customers wherever you need it.

  1. Four clicks to create a unique list of customers.