How to Get Unique Values in Excel

One of the most common questions I come across is how to get a list of unique values from a column.  There are many ways to accomplish this; probably the easiest is to use the Advanced Auto Filter.

For Excel 2003, select your column you want to filter and then click Data -> Filter -> Advanced Filter.  You may get a message that Excel cannot determine which row in your list contains column labels.  This means that Excel can’t find your headers.  An easy way to get around this is to bold your row that has headers.  This will let Excel know that the bolded row is your header row.

You can either filter the list in place or copy the unique values to another area in your worksheet.

To copy the list in place leave everything as is and select the Unique records only check box at the bottom and click OK.

To copy the unique values to another location, select the Copy to another location option, enter in a Copy to range and select the Unique records only check box at the bottom then click OK.

Then you will be able to see the unique values in your column.

To get to the advanced auto filter in Excel 2007/2010, you would go to the Data tab and select Advanced from the Sort & Filter section.  The rest of the steps remain the same.

If you've any question, problem, suggestion and feedback than please comment below.

Have a nice day!