Get Unique Values from a Column in Excel

by

Unique values are those that appear on a list just once.

When working with data in Excel, you will often come across datasets that have duplicates in the column and you will have to extract the unique values or items from that columns

In fact, it is one of the most common data cleaning tasks, as it helps remove duplicates, thereby saving memory space as well as processing time.

More importantly, by removing redundancy, it keeps the data organized and makes it easier to make inferences.

In this tutorial we will show you three ways to get unique values from a column in Excel:

  • Using the Remove Duplicates tool
  • Using the Advanced Filter tool
  • Using the UNIQUE function (Excel 365 and later versions)

We will demonstrate each of the above methods by applying them to the following list of names:

Names Dataset with repetition / duplicates

Notice that the name John Smith appears three times in the above list, and the name Manuel Braun appears twice. So our aim here would be to only extract the unique names from this list (no repetitions).

Let’s see some simple ways to do this in Excel.

Using the Excel Remove Duplicates Tool to Get Unique Values from a Column

Excel has an in-built tool to instantly remove all the duplicate values and give you only the unique ones.

This is the simplest, quickest and so far, the most commonly used method to get unique values from a column.

Here are the steps to get unique values from our sample Names list:

  1. Select the range of cells containing your list. In our example, select the range A2:A9
  2. Click on the Data tab.
  3. Click on the Remove Duplicates button (from the Data Tools group).
Click on Remove duplicates
  1. This opens the Remove Duplicates window.
  2. Check all the settings and click on OK.
Select the names column
  1. You should see a message box informing you about how many duplicate values were removed and how many unique values remain in your list.
Prompt to tell you how many duplicate values have been found
  1. Click OK.

You should now be left with a list of only unique values. 

List of unique names in the column

Note that the names John Smith and Manuel Braun now appear just once in the list. 

Note: The Remove Duplicates tool directly applies changes to the selected data cells by deleting all the duplicate values. In other words, it affects your original data. So if you want to retain the original list, it’s a good idea to make a copy of the list and then apply the Remove Duplicates tool to it.

Also read: How to Generate Random Names in Excel

Using the Excel Advanced Filter Tool to Get Unique Values from a Column

The next method is also quite commonly used in removing duplicates.

It basically involves using an Advanced Filter tool to remove duplicates and display the list of unique values either in-place or in a new location.

Here are the steps to use the Advanced Filter to get unique values:

  1. Select the range of cells containing your list. In our example, select the range A1:A9. Make sure you select the column header along with the data.
  2. Click on the Data tab.
  3. Click on the Advanced button (from the Sort & Filter group).
Click on the Advanced option
  1. This opens the Advanced Filter dialog box.
Advanced Filter dialog box
  1. If you want to remove duplicates from the original list, then check the radio button next to ‘Filter the list, in-place’. If you want to retain the original list and display the unique values in a new range of cells, then check the radio button next to ‘Copy to another location’. In this case, you will need to enter in the field next to ‘Copy to’ the reference to the first cell where you want the filtered list to appear. In our case, we specified the cell reference $B$1.
Select Copy to another location
  1. Make sure that the box next to ‘Unique records only’ is checked.
Select Unique records only
  1. Click OK.

You should now see the list of only unique values from your original list in the cell location that you had specified.

All duplicate values have been removed
Also read: How to Count Unique Values in Excel (5 Formulas Methods)

Using the UNIQUE Function to get Unique Values from a Column (Excel 365)

The UNIQUE function has been added to the Excel functions arsenal, just recently.

So, you can use this method only on Excel 365 (at this point in time).

The UNIQUE function simply takes a range of cells (or an array) and returns a list of unique values from this list. It also gives you the option to return just the unique values from the list or the list of distinct unique values. 

It’s a dynamic function, so it returns an array of values that ‘spill’ onto neighboring cells.

The syntax for the UNIQUE function is as follows:

UNIQUE (array, [by_col], [exactly_once])

Here,

  • array is the array or range of cells from which you want to get the unique values
  • by_col is a TRUE/FALSE or 0/1 value that specifies whether the function should extract unique values by rows or columns. By default, the value is FALSE (which means it extracts by row).
  • exactly_once specifies if you want to extract values that are distinct and unique values or just unique. A value of FALSE (the default value) means that you want the list of values that appear at least once. A value of TRUE means that you want to remove all values that appear more than once (retaining only the values that don’t repeat).

Let us see how we can apply this function to get the unique values from our sample dataset:

  1. Click on an empty cell in a new column (Make sure the column is empty, allowing space for the resulting array to spillover). In our example, we select cell B2.
  2. Insert the following formula in the cell: =UNIQUE(A2: A9)
  3. Press the return key.

You should now see only the unique values from the selected range displayed in column B.

Unique Function in Excel 365

If you want to display only the values that appear exactly once in the list, then you can specify the third parameter is TRUE, as shown below:

=UNIQUE(A2:A9,,TRUE)

Here’s the result that you will get in this case:

Unique values that only appear once

Unlike the first two methods, the result of the UNIQUE formula is dynamic. This means that in case your original data changes, then the result of the UNIQUE function will automatically update.

In this tutorial, we showed you 3 ways to get unique values from a column in Excel.

The first method uses the simple ‘Remove Duplicates’ tool which applies changes to the original range of cells. 

The second method uses the Advanced Filter, which lets you choose if you want to apply the tool to the original values or to a copy of the original values. 

The third method uses the dynamic UNIQUE function, which is quite flexible and easy to use but is only available in Excel 365 at this time.

We hope this tutorial was helpful for you.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment