The method I use does not remove any duplicates, it just tells me what distinct values there are in a column. In a staff list, for example, you probably have many duplicates in the department column and of course you want to keep them.

Two button clicks
If you need a list of all the unique deparment names, this can be achieved with advanced filtering or with a formula, but I find it quicker to first insert all the cells in a table and then create a pivot table from it.

This is done with two button clicks, one for the table and one for the pivot, and then I only have to drag the departments column (or any other column I want unique values for) to the ROWS area to see the unique values. The method works for all Excel versions.

23 Excel-tips
The Excel tip on finding unique column values is the 23rd Excel tip on kalmstrom.com. The tips are divided into four groups – Charts, Formulas, SharePoint and Various – so that it will be easier for you to find what you need, and they range from basic to advanced.

The tips were primarily created for kalmstrom.com Subscribers, as most kalmstrom.com Solutions have integrated Excel statistics reports where users can study how resources have been used, how time has been spent, how issues have been handled and so on, depending on product.

Welcome to browse!
Even if we introduced the Tips section for our subscribers, anyone may read and share them, and we see that they are used far beyond the kalmstrom.com Community. There is often a video demonstration, like the one below, and an instruction that step by step explains how to do what is shown in the video. For a more general discussion around the subject we refer to a blog post, like this one.