Find Unique Values in a Column in Excel (6 Easy Methods)

In Microsoft Excel, extracting unique values from a column is one of the crucial tasks. You will find yourself in various situations where you may need to find unique data to analyze your dataset. In this tutorial, you will learn every possible way to extract unique values in a column in Excel with suitable examples and proper illustrations.

Find Unique Values in a Column in Excel (6 Easy Methods)

In the following sections, we will provide you with six efficient ways to extract unique values from a column in Excel. Now, we recommend you learn and apply all these methods to your worksheets. I hope it will definitely enrich your Excel knowledge in the future.

1. Using Advanced Filter for Unique Values in Excel

Now, the most used way to find unique values from a column in Excel is using the Advanced command. You can find this in the Data tab.

Take a look at the following dataset:

Here, we are going to use the Advanced filter for unique values in a column.

📌 Steps

• First, select any cell from the column.

• Then, go to the Data Tab.
• After that, click on Advanced.

• Now, from the Advanced Filter dialog box, select Copy to another location. Then, select any cell to paste the new values. Next, checkmark the box Unique records only.

• Then, click on OK.

As you can see, we have successfully created a new list of unique values in a column in Excel.

2. Filter for Unique Values in a Column

Another efficient way is to filter the list or column. It is also using the Advanced command from the Excel Data tab. But the difference is it will replace your whole column with unique values.

📌 Steps

• First, select any cell from the column.

• go to the Data Tab.
• After that, click on Advanced.

• Now, from the Advanced Filter dialog box, select Filter the list, in-place. Next, checkmark the box Unique records only.

• Then, click on OK.

In the end, you will see it will only show the unique values in the column. Basically, it hid the duplicate values.

3. Remove Duplicates from a Column in Excel

Now, this method is pretty easy to understand. If your columns have duplicates, just remove those duplicates. After that, all you will have is the unique values in a column in Excel.

📌 Steps

• First, select any cell from the column.

• Then, go to the Data
• From the Data Tools group, click on Remove Duplicates.

• Now, from the Remove Duplicates dialog box, checkmark the box Data and My data has headers.

• Then, click on OK.

Finally, it will show you how many duplicates it found and how many unique values are in the column in Excel.

4. Formulas to Find Unique Values from a Column

Now, you can use formulas to extract unique values in a column. These methods will also come in handy in the future. So, we suggest you learn these formulas too to improve your knowledge.

4.1 Using IF and COUNTIF Functions

In this method, we are combining the IF function and the COUNTIF function together. Now, this method pastes a value in the cell if it is unique. But the orientation will not be like other methods. You can keep this in your arsenal to be a better analyst.

📌 Steps

• First, create a new column.

• After that, type the following formula in Cell D5:

• Then, press Enter.
• After that, drag the Fill handle icon over the range of cells D6:D13. Then you will see the following:

As you can see from the above screenshot, our column only contains the unique values in Excel.

4.2 Using INDEX and MATCH Functions

Now, the process of this method is similar to the previous one. Just we are changing the formula here. Mainly, we are mixing the INDEX function and the MATCH function.

Also, we are using the IFERROR function and the COUNTIF function for further assistance.

📌 Steps

• Create a new column.

• Then, type the following formula in Cell D5:

• Next, press Enter.
• After that, drag the Fill handle icon over the range of cells D6:D13.

• Then you will see the following:

In this way, you can extract the unique values from an Excel cell easily.

5. Use of UNIQUE Function in Excel

Now, another essential method to find the unique values in a column is using the UNIQUE function. The Excel UNIQUE function returns a list of unique values in a range or a list. It’s a very easygoing function that you can use to extract both unique and unique distinct values. It also helps to compare columns to columns or rows to rows.

In the following sections, we will show you five examples of the UNIQUE function to implement your worksheet.

5.1 Extract Unique Values from a Column

This one is a basic example of the UNIQUE function. We will find the unique values from the column.

📌 Steps

• First, create a new column.

• Now, type the following formula in Cell D5:

• Next, press Enter.

After that, it will extract all the unique values from the other column into this column.

5.2 Extract Unique Values that Has Only One Occurrence

Now, you can see from the column that we have some values that occurred multiple times. And some values occurred only once. These values can be called pure unique values. You can find those values by setting the third argument of the UNIQUE function to TRUE.

📌 Steps

• Create a new column like the following.

• Next, type the following formula in Cell D5:

• After that, press Enter.

As you can see, only three unique values appeared only once in the column.

5.3 Unique Values from Multiple Columns

Now, you can use the UNIQUE function on multiple columns that have unique rows in them. It actually returns the target columns in an array argument.

We are using this for the following dataset:

📌 Steps

• Type the following formula in Cell E5:

• After that, press Enter.

5.4 Sort Unique Values in Alphabetical Order

Now, you can extract unique values from a column in alphabetical order. We mainly perform this from the sort & filter command of Excel. But the SORT function does that easy for us. You don’t have to re-sort every time after you have extracted your data.

📌 Steps

• Create a new column first.

• After that, type the following formula in Cell D5:

• Finally, press Enter.

As you can see, we have successfully extracted and sorted the unique values from the column in Excel.

5.5 Unique Values Based on Criteria

Now, you can find the unique values based on criteria in Excel from a column. To perform this, we are mixing the UNIQUE function with the FILTER function in Excel.

Take a look at the following dataset:

From here, we are going to find the unique values that have an age less than 30.

📌 Steps

• At first, create two new columns like the following:

• Next, type the following formula in Cell D5:

• Then, press Enter.

Now, you can see the unique values based on criteria in Excel from the dataset.

6. VBA Macros for Unique Values in a Column

Now, if you are a VBA freak like me, you may find this one interesting. This method actually works like the Advanced filter method that was shown earlier. It creates a new list in a new column with unique values.

📌 Steps

• First, press Alt+F11 to open the Visual Basic Application.
• After that, click on Insert > Module.

• Now, type the following code:

Option Explicit
Sub UniqueColumn()
Dim last_row As Long
last_row = Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.Range("B2:B" & last_row).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("D2"), _
Unique:=True
End Sub

• After that, save the file.
• Now, press Alt+F8 to open the Macro dialog box.

• Then select UniqueColumn and click on Run.

As you can see, our VBA codes successfully created a new column with unique values in Excel.

💬 Things to Remember

✎ We showed some basic examples of the UNIQUE functions here. Make sure to read the article we mentioned to get a clear idea about this function.

✎ The UNIQUE function will show a #SPILL error if one or more cells are not blank in the spill range.

✎ If you don’t want to overwrite your original data, make a copy of that.

Download Practice Workbook

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to extract unique values in a column in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

<< Go Back to Unique Values | Learn Excel