Software

Generate a list of unique entries in an Excel column

When you need to get rid of duplicates in a column, Excel's advanced filtering options will do the trick. This quick walk-through shows how it's done.

When you support Excel users, one of the most common questions you'll hear is, "I've got a list with a thousand entries in a column, and many of those are duplicates. How do I generate a list of the unique entries in that column?"

There are at least two good answers to that question. The first answer is to go to Data | AutoFilter and then click the drop-down list for the column in question. Doing so lets you see the list of unique entries onscreen. If seeing the list satisfies your need, you're finished.

The second answer is the one to use if you want to have a list of the unique entries you can copy and paste elsewhere. To generate such a list, you'll use Data | Filter | Advanced Filter. To demonstrate how it works, we'll use the data in Column B from the sample sheet shown in Figure A.

Figure A

duplicate entries

  1. Click on the column letter to select the entire column that contains your data and then copy it by pressing Ctrl + C, going to Edit | Copy, or clicking the Copy button on the Standard toolbar. (Select the whole column because you'll need the column header.)
  2. Paste that data into a column away from your source data range or in a new sheet. After you paste the data, it will still be selected. However, if you inadvertently deselect it, just make sure the cell pointer is located anywhere in the data you pasted before you proceed. Note: You don't have to select all the data or sort it first for this tip to work.
  3. Go to Data | Filter | Advanced Filter.By default, Excel will suggest filtering the list "in-place." There's nothing wrong with that, but I recommend copying the unique records to another location, so you can compare the two lists side by side.
  4. Select the Copy To Another Location option (Figure B), select the Unique Records Only check box, and type B1 in the Copy To field.
  5. Click OK, and Excel will copy the unique entries from the source column into the new location. It will even sort those entries in alphabetical order, as shown in Figure C.

Figure B

advanced filter

Figure C

Pasting unique entries

7 comments
gerard.slater
gerard.slater

Sounds like a good tip. Q : Is this possible in a pivot table? I have numerous cases of these list in a spredsheet of data and wanted to use a pivot table to do the calculation. Is this possible?

jmiller544
jmiller544

Thanks Jeff!! Many user "Tips & Tricks" aren't worth reading even once but this one I can really use... Good Job!!!!

johnmcd765
johnmcd765

Does not work for Excel in Office XP. Doesn't remove all duplicates or sort.

rita.bailey
rita.bailey

This worked well for me, except that it did not alphabetize the unique list - perhaps because I'm working off of Excel 2003?

rapell
rapell

This is the kind of thing a user will actually want you to help with. Overtime I have found that the easy things are actually the hard ones, and yet user want the easy thing, not a VBA script to do the task. Can you do this for more than one column at a time? I intended to ask about pulling data with its corresponding value but am sure subtotals would do that well enough. Thanks for the tip