"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

Extracting Unique Items From Data In A Column


Anytime you have a column of data that contains duplicate items and you need just a list of the unique items in that column, Excel has an option that allows you to easily do this, but few Excel users know about it.

Say for example that you have a list of hundreds, maybe even thousands of names and many of these names are in the list multiple times. You need to pull out a list of names with no duplicates. I've seen Excel users first sort the list to make it easier to manually copy the individual names. But this is still very inefficient and time consuming.

Excel's time-saving option for this task is called Advanced Filter and here's how it works.

  1. Select the list of data to be filtered (only one column wide). If your list doesn't have a heading, add one, or include the blank cell immediately above the list in your selection.
  2. From the Data menu, select Filter, then Advanced Filter.
  3. In the Advanced Filter dialog box, ensure that the 'List Range' is actually the cells you have selected. Often Excel will select, by default, the entire range of columns and rows surrounding your selection. To be sure, you can select your list again while the 'List Range' box is selected.
  4. Click to select the 'Unique Records Only' option.
  5. You can choose to 'Filter List In Place', or you can choose 'Copy to another location' and select a cell on the current sheet where you would like the unique items to be copied, leaving the original list untouched.
Of the Excel users who have discovered and use Advanced Filter, many are often frustrated that Excel, it appears, doesn't allow them to select a different sheet in which to paste the unique filtered records.

Here's a secret that I use to get around that problem.

  1. First select the sheet and cell where you want your extracted records to be copied.
  2. Select the Advanced Filter option.
  3. Click the sheet tab containing the list.
  4. Select the other options as explained above.


Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.