16 December 2014

Find Unique Values In Excel Columns

Excel icon There are several ways to learn how many unique values an Excel column contains, but I have a favorite. In a new Tips article I am showing how to find unique Excel column values by creating a pivot table.

Keep duplicates
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
Excel format as table button 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.
Excel summarize with pivot table button
23 Excel-tips
The Excel tip on finding unique column values is the 23rd Excel tip on 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 Subscribers, as most 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 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. 

Peter Kalmstrom
CEO and Systems Designer Business Solutions