Find Unique Values in a Column in Excel (6 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 the 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.


Download Practice Workbook


6 Methods to Find Unique Values in a Column in Excel

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 from the Data tab.

Take a look at the following dataset:

Using Advanced Filter for Unique Values in Excel

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.

Using Advanced Filter for Unique Values in Excel

  • Then, click on OK.

Using Advanced Filter for Unique Values in Excel

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

Read More: How to Extract Unique Items from a List in Excel (10 Methods)


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.

Filter for Unique Values in a Column

  • Then, click on OK.

Filter for Unique Values in a Column

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.

Remove Duplicates from a Column in Excel

  • 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.

Remove Duplicates from a Column in Excel

  • Then, click on OK.

Remove Duplicates from a Column in Excel

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:

=IF(COUNTIF(B$5:B5,B5)=1,B5,"")

Formulas to Find Unique Values from a Column

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

Formulas to Find Unique Values from a Column

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:

=IFERROR(INDEX($B$5:$B$13, MATCH(0,COUNTIF($D$4:D4, $B$5:$B$13), 0)),"")

Formulas to Find Unique Values from a Column

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

  • Then you will see the following:

Formulas to Find Unique Values from a Column

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

Read More: How to Get Unique Values from Range in Excel (8 Methods)


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 easy-going 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.

Note: The UNIQUE function is available in Excel 365 and 2021.

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

  • At first, create a new column.

  • Now, type the following formula in Cell D5:

=UNIQUE(B5:B13)

excel Unique Values from a Column

  • Next, press Enter.

excel Unique Values from a Column

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:

=UNIQUE(B5:B13,,TRUE)

  • After that, press Enter.

excel Unique Values from a Column

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:

=UNIQUE(B5:C13)

  • After that, press Enter.

excel Unique Values from a Column

Read More: How to Find Unique Values from Multiple Columns in Excel


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:

=SORT(UNIQUE(B5:B13))

excel Unique Values from a Column

  • Finally, press Enter.

excel Unique Values from a Column

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 from a column based on criteria in Excel. 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:

=UNIQUE(FILTER(B5:C13,C5:C13<30))

excel Unique Values from a Column in Excel

  • Then, press Enter.

Extract Unique Values from a Column in excel

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


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 showed earlier. It creates a new list in a new column with unique values.

πŸ“Œ Steps

  • First, press Alt+F11 to open 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.

VBA Macros for Unique Values in a Column in excel

  • Then select UniqueColumn and click on Run.

VBA Macros for Unique Values in a Column in excel

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.


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.

Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo