Excel Unique Values: Filter for Unique Values (or Duplicates)

Are you a single child?  No brothers or sisters?  Spoiled by your parents?  Perfect, because I have just the Excel trick for you.  Filtering for unique values in Excel is a clever way to distinguish those singular pieces of data.  Not only does parsing for unique values have many practical uses when dealing with longs lists (from generating one-time buyers to getting rid of nonsensical, insanity inducing repeats), it also goes hand-in-hand with removing duplicates (an evil twin, for example).  The first thing we’ll learn is how to filter and remove, and then as an added bonus we’ll take a quick look at conditionally formatting unique or duplicate values.  You can learn more intermediate Excel functions in this awesome Excel mastery course.

I have a spreadsheet listing all of my customers’ purchases in the year 2013.  A lot of elbow grease goes into each client, and for that to result in one purchase is frustrating; I need to find out who my one-time buyers are and contact them personally.  If they were repulsed by my product, I need to know why; and if they loved it but haven’t returned, maybe all they need is a friendly reminder.  So, here is my spreadsheet:

And on it goes.  First, select the range of cells; in my case, column A (or make sure the active cell is in a table).  You will also want to make sure there are no blank cells in your range.  Then, select the Data tab and in the drop-down menu beside Filter, click Advanced Filter…:

Note: you can accomplish the same thing by going to Data in the Title bar and selecting Advanced Filter there.

In the Advanced Filter dialogue box, you have two options: 1) you can choose to Filter the list, in-place (just like it sounds, the list is filtered where it stands) or 2) Copy to another location.  Since I want my spreadsheet to remain intact, I’m going to copy to another location.  Now I have to specify where I want the list to appear (for whatever reason, I chose column G).  I can either enter the location manually, or collapse the dialogue box and select a cell/cell range on my spreadsheet.  Finally, I expand the dialogue box, click Unique Records Only (if you fail to do this, you will have set a world-record for longest method to copy/paste), and hit OK:

The unique values will now populate the selected range while my original data remains unaffected.  I can get almost the same results by removing duplicate values, but this does not give me the option to copy to another location.  Therefore, be sure to save your data elsewhere first.  I repeat: when you remove duplicate values, you are permanently deleting them.

Before proceeding, it’s a good idea to understand exactly what constitutes a duplicate value.  The value displayed in the cells must be the same (vs. the value stored in the cell).  Examples include the same values with a different number of decimal places, dates in different formats, etc.  It’s a rare case for Excel: what you see is what you get.

We begin in the same way: select your range of cells or make sure the active cell is in a table.  Beneath the Data tab, click Remove Duplicates:

This launches the following dialogue box, which tells you how many duplicate and unique values exist in your range:

 

You will notice that I already copied my data into column H for safe keeping.  If you selected multiple columns, you will have the option to Select All or to choose individual columns as you see fit.  Click Remove Duplicates and you’re finished.  As you can see, columns G and H display identical data:

Our last little trick involves conditionally formatting unique or duplicate values.  The location of Conditional Formatting has changed slightly through the years.  In Excel 2010, you will find it in the Home tab under the Styles group.  In 2011, it is still in the Home tab, but under the Format group (I’m using 2011).  Click Conditional Formatting, scroll over Highlight Cells Rules, and click Duplicate Values…:

 

This opens the New Formatting Rule dialogue box.  Several options are available.  You can choose a style for formatting, a color scheme, unique or duplicate values, and other options such as “Format only values that are above or below average.”  The only thing I am going to change are the values.  I want to highlight Unique values:

 

Now I’m done, and the results are pretty awesome.  I have my unique values in column G, my deleted duplicates in column H, and my highlighted unique values (without removing the duplicates) in column I:

 

Play around with the New Formatting Rule dialogue box to develop your own aesthetics, and then further your philosophy of design by learning how to innovate in style.  But before we get carried away, let’s do a brief review of important things to keep in mind:

• Filtering for unique values gives you the option of filtering on-location or copying to a new range
• Removing duplicate values permanently deletes them
• Duplicate values only exist when the values displayed in the cells are identical, not merely when what is stored in them is the same (dates, decimals, etc.)

You’re good to go.  If you’re like me and want to pin-point and personalize your marketing plan, have a look at this tactical guide to customer development.  Who needs brothers and sisters when you can have a thousand, loyal clients?