Filtering Columns for Unique Values

Summary: If you have a worksheet column containing lots and lots of data, you may want to extract the unique data it contains. Using the advanced filtering capabilities of Excel, this task is much easier than you'd think. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)

It is not unusual to acquire or develop data tables that have duplicate values in a column. If you want to see only the unique values, without the duplicates, you want to filter your data table. Excel makes this rather easy for must scenarios. For instance, let's say you have a data table in which you have part numbers in column A. If you want to filter the list so you see only unique part numbers, you can follow these steps:

Select one of the cells in the list of part numbers.
Choose Filter from the Data menu, and then choose Advanced Filter. Excel displays the Advanced Filter dialog box. (Click here to see a related figure.)
I always like to choose the Copy to Another Location option.
In the Copy To field, specify the cell where you want the list of unique, filtered values to be copied.
Make sure the Unique Records Only check box is selected.
Click on OK.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2754) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Make Home Buying Less Stressful! Why make home buying harder than it needs to be? Put your mind at ease—discover all the questions you need to ask to make the best buying decision.

Check out Buying a Home Checklist today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Filtering Columns for Unique Values