How to Find Unique Values in an Excel Column (6 Methods)

Method 1 – Using Advanced Filter for Unique Values

We’ll use the below dataset:

Using Advanced Filter for Unique Values in Excel

Steps

  • Open your Excel workbook.

  • Navigate to the Data tab.
  • Look at the dataset you want to work with.
  • Follow these steps:
    • Click on Advanced in the Sort & Filter group.

    • In the Advanced Filter dialog box, select Copy to another location.
    • Choose a cell where you want to paste the unique values.
    • Check the box for Unique records only.

Using Advanced Filter for Unique Values in Excel

    • Click OK.

Using Advanced Filter for Unique Values in Excel

You’ve now created a new list of unique values from the selected column.


Method 2 -Filtering for Unique Values in a Column

Steps

  • Select any cell within the column you want to filter.

  • Go to the Data Tab.
  • Click on Advanced in the Sort & Filter group.

  • In the Advanced Filter dialog box:
    • Choose Filter the list, in-place.
    • Check the box for Unique records only.

Filter for Unique Values in a Column

  • Click OK.

Filter for Unique Values in a Column

This method will display only the unique values in the column, effectively hiding any duplicate values.


Method 3 – Removing Duplicates from a Column

Steps

  • Select any cell within the column containing duplicates.

Remove Duplicates from a Column in Excel

  • Go to the Data tab.
  • From the Data Tools group, click on Remove Duplicates.

  • In the Remove Duplicates dialog box:
    • Ensure the box for Data is checked.
    • If your data has headers, check the box for My data has headers.

Remove Duplicates from a Column in Excel

  • Click OK.

Remove Duplicates from a Column in Excel

The result will show you how many duplicates were removed, leaving only the unique values in the column.


Method 4 – Formulas to Find Unique Values from a Column

You can use formulas to extract unique values from a column. These methods will also be useful in the future, so we recommend learning them to enhance your knowledge.

4.1 Using IF and COUNTIF Functions

Steps

  • Create a new column.

  • In cell D5, enter the following formula:

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

Formulas to Find Unique Values from a Column

  • Press Enter.
  • Drag the Fill handle icon over the range of cells D6:D13.

Formulas to Find Unique Values from a Column

You’ll see that the column now contains only the unique values in Excel.


4.2 Using INDEX and MATCH Functions

Steps

  • Create another new column.

  • In cell D5, enter the following formula:

=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

  • Press Enter.
  • Drag the Fill handle icon over the range of cells D6:D13.

  • This method allows you to easily extract unique values from an Excel cell.

Formulas to Find Unique Values from a Column


Method 5 – Using the UNIQUE Function in Excel

The UNIQUE function in Excel is an essential method for finding unique values in a column. It returns a list of distinct values from a specified range or list. You can use it to extract both unique and distinct values, as well as compare columns or rows.

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

 

5.1 Extracting Unique Values from a Column

Steps

  • Create a new column.

  • In cell D5, enter the following formula:

=UNIQUE(B5:B13)

excel Unique Values from a Column

  • Press Enter.

excel Unique Values from a Column

The result will be a list of all unique values from the specified column.


5.2 Extracting Unique Values with Only One Occurrence

Some values occur multiple times in the column, while others appear only once. These unique values with a single occurrence are particularly interesting. To find them, set the third argument of the UNIQUE function to TRUE.

Steps

  • Create another new column as per the below example.

  • In cell D5, enter the following formula:

=UNIQUE(B5:B13,,TRUE)

  • Press Enter.

excel Unique Values from a Column

You’ll see that only three unique values appear once in the column.


5.3 Extracting Unique Values from Multiple Columns

You can also use the UNIQUE function on multiple columns that contain unique rows. It returns an array of unique values from the specified columns.

For example, if you have data in columns B and C, follow these steps:

Steps

  • Enter the following formula in Cell E5:

=UNIQUE(B5:C13)

  • Press Enter.

excel Unique Values from a Column

The result will be a list of unique values from both columns B and C.


5.4 Sorting Unique Values in Alphabetical Order

You can extract unique values from a column in alphabetical order using the SORT function. Unlike the manual sort and filter command, this method automatically arranges the data for you.

Steps

  • Create a new column.

  • In cell D5, enter the following formula:

=SORT(UNIQUE(B5:B13))

excel Unique Values from a Column

  • Press Enter.

excel Unique Values from a Column

The result will be a list of unique values sorted alphabetically in the new column.


5.5 Finding Unique Values Based on Criteria

To find unique values based on specific criteria in Excel, combine the UNIQUE function with the FILTER function.

Example: Suppose you have a dataset with columns B (values) and C (ages). You want to find unique values where the age is less than 30.

Steps

  • Create two new columns.

  • In cell D5, enter the following formula:

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

excel Unique Values from a Column in Excel

  • Press Enter.

Extract Unique Values from a Column in excel

The result will display unique values from column B where the corresponding age in column C is less than 30.


Method 6 – VBA Macros for Unique Values in a Column

If you’re familiar with VBA (Visual Basic for Applications), you can create a new column with unique values using a VBA macro. This method is similar to the Advanced filter approach but automates the process.

Steps

  • Press Alt+F11 to open the Visual Basic Application.
  • Click on Insert and select Module.

  • Enter 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
  • Save the file.
  • Press Alt+F8 to open the Macro dialog box.

VBA Macros for Unique Values in a Column in excel

  • Select UniqueColumn and click Run.

VBA Macros for Unique Values in a Column in excel

Your VBA code will successfully create a new column with unique values in Excel.


Things to Remember

  • Understanding UNIQUE Function:
    • The examples provided here demonstrate basic usage of the UNIQUE function. For a more comprehensive understanding, consider reading the article we mentioned.
    • The UNIQUE function returns a list of distinct values from a specified range or list.
  • #SPILL Error:
    • Be aware that the UNIQUE function may display a #SPILL error if one or more cells within the spill range are not blank. Ensure that your data meets the necessary criteria for successful execution.
  • Data Overwriting:
    • If you want to preserve your original data, create a copy before applying the UNIQUE function. This way, you won’t accidentally overwrite your existing information.

Download Practice Workbook

You can download the practice workbook from here:


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo