How to Extract Unique Values from a Column in Excel (2023)

In Microsoft Excel, extracting unique values from a column is a crucial task that often arises when analyzing datasets. Whether you need to find unique data for analysis or remove duplicate values, Excel provides several methods to extract unique values from a column. In this tutorial, we will explore six easy methods to accomplish this task, along with suitable examples and illustrations.

Method 1: Using Advanced Filter for Unique Values in Excel

The most commonly used method to find unique values from a column in Excel is by using the Advanced Filter command. This method allows you to create a new list of unique values in a separate location. Here's how you can do it:

1. Select any cell from the column.
2. Go to the Data tab.
3. Click on Advanced.
4. In the Advanced Filter dialog box, select "Copy to another location."
5. Choose a cell to paste the new values.
6. Checkmark the box "Unique records only."
7. Click on OK.

By following these steps, you will successfully create a new list of unique values in a column in Excel.

Method 2: Filter for Unique Values in a Column

Another efficient way to extract unique values from a column is by filtering the list or column. This method also utilizes the Advanced command from the Excel Data tab. However, it replaces the entire column with unique values. Here's how you can do it:

1. Select any cell from the column.
2. Go to the Data tab.
3. Click on Advanced.
4. In the Advanced Filter dialog box, select "Filter the list, in-place."
5. Checkmark the box "Unique records only."
6. Click on OK.

By following these steps, you will see that only the unique values in the column are displayed, while the duplicate values are hidden.

Method 3: Remove Duplicates from a Column in Excel

If your column contains duplicate values, you can easily remove them to extract the unique values. Here's how you can do it:

1. Select any cell from the column.
2. Go to the Data tab.
3. From the Data Tools group, click on Remove Duplicates.
4. In the Remove Duplicates dialog box, checkmark the box "Data" and "My data has headers."
5. Click on OK.

By following these steps, Excel will remove the duplicate values, leaving only the unique values in the column.

Method 4: Formulas to Find Unique Values from a Column

Excel provides various formulas that can be used to extract unique values from a column. These formulas can be useful for future reference and analysis. Let's explore two commonly used formulas:

4.1 Using IF and COUNTIF Functions

In this method, we combine the IF function and the COUNTIF function to extract unique values. The formula pastes a value in the cell if it is unique. Here's how you can do it:

1. Create a new column.
2. Type the following formula in Cell D5: =IF(COUNTIF(B$5:B5,B5)=1,B5,"")
3. Press Enter.
4. Drag the Fill handle icon over the range of cells D6:D13.

By following these steps, you will obtain a column that contains only the unique values in Excel.

4.2 Using INDEX and MATCH Functions

This method is similar to the previous one, but it utilizes the INDEX function and the MATCH function. Additionally, the IFERROR function and the COUNTIF function are used for further assistance. Here's how you can do it:

1. Create a new column.
2. Type the following formula in Cell D5: =IFERROR(INDEX($B$5:$B$13, MATCH(0,COUNTIF($D$4:D4, $B$5:$B$13), 0)),"")
3. Press Enter.
4. Drag the Fill handle icon over the range of cells D6:D13.

By following these steps, you will obtain a column that contains the unique values from the Excel cell.

Method 5: Use of UNIQUE Function in Excel

Another essential method to find unique values in a column is by using the UNIQUE function. This function returns a list of unique values in a range or a list. It can be used to extract both unique and unique distinct values, as well as compare columns or rows. Please note that the UNIQUE function is available in Excel 365 and 2021. Let's explore some examples of using the UNIQUE function:

5.1 Extract Unique Values from a Column

To extract unique values from a column, you can use the UNIQUE function. Here's how you can do it:

1. Create a new column.
2. Type the following formula in Cell D5: =UNIQUE(B5:B13)
3. Press Enter.

By following these steps, Excel will extract all the unique values from the column into the new column.

5.2 Extract Unique Values that Have Only One Occurrence

If you want to find unique values that occur only once in the column, you can modify the UNIQUE function. Here's how you can do it:

1. Create a new column.
2. Type the following formula in Cell D5: =UNIQUE(B5:B13,,TRUE)
3. Press Enter.

By following these steps, Excel will display only the unique values that have a single occurrence in the column.

5.3 Unique Values from Multiple Columns

You can also use the UNIQUE function on multiple columns that contain unique rows. This function returns the target columns in an array argument. Here's how you can do it:

1. Type the following formula in Cell E5: =UNIQUE(B5:C13)
2. Press Enter.

By following these steps, Excel will extract the unique values from multiple columns and display them in the new column.

5.4 Sort Unique Values in Alphabetical Order

To extract unique values from a column in alphabetical order, you can use the SORT function along with the UNIQUE function. Here's how you can do it:

1. Create a new column.
2. Type the following formula in Cell D5: =SORT(UNIQUE(B5:B13))
3. Press Enter.

By following these steps, Excel will extract and sort the unique values from the column in alphabetical order.

5.5 Unique Values Based on Criteria

If you want to find unique values based on specific criteria in Excel, you can combine the UNIQUE function with the FILTER function. Here's how you can do it:

1. Create two new columns.
2. Type the following formula in Cell D5: =UNIQUE(FILTER(B5:C13,C5:C13<30))
3. Press Enter.

By following these steps, Excel will display the unique values based on the criteria you specified.

Method 6: VBA Macros for Unique Values in a Column

For those familiar with VBA macros, you can use this method to create a new column with unique values. This method is similar to the Advanced filter method mentioned earlier. Here's how you can do it:

1. Press Alt+F11 to open the Visual Basic Application.
2. Click on Insert > Module.
3. Type the provided VBA code.
4. Save the file.
5. Press Alt+F8 to open the Macro dialog box.
6. Select the UniqueColumn macro and click on Run.

By following these steps, the VBA code will create a new column with unique values in Excel.

In conclusion, Excel provides several methods to extract unique values from a column. Whether you prefer using the Advanced Filter command, filtering the list, removing duplicates, or utilizing formulas and functions, Excel offers versatile options to meet your unique data analysis needs. By following the steps outlined in this tutorial, you can easily extract and analyze unique values in Excel columns.