How to filter a list in Excel to provide a list of unique values
Often you’ll have a big list that you want to filter duplicates out of. It could be because you’re copying content from a number of sources that may overlap, or you’re just rattling off a list of something as fast as you can and don’t want to go back to see if what you’re thinking of is already in the list. Either way, you’ll want a unique list of all the values. This is simple in Excel, and the tutorial below is for Microsoft Excel 2010. The steps are similar for other versions.
How to remove duplicates from a list in Excel
Start with a list of data.
Highlight the set of data you want to filter, then open the ribbon and click on the Data tab. There is a large filter button, but we want to click on the advanced button next to it.
The dialog box below comes up. Change the first radio to Copy to another location. Make sure the List range does not include your headers, then click in the Copy to: field. You can either type the top cell of where you want your new list to appear, or just click on the cell while you’re in the field. Check off the Unique records only checkbox and click OK.
The resulting data set will show as you expected it to.
Counting the occurrences of your unique list
As an additional step, you may want to know how many times each unique item showed up in the list. This would be useful if you’re getting a raw list of data and trying to show patterns or create a graph. Simply add another column and use the formula shown below. Place the formula in the first cell of your new column, next to the first unique value. Use the range of your full set where the formula references A1:A100, and use your unique column cell as the $B2 reference. Drag the formula down to match your list of unique values, and the result will be the count you’re looking for.