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

If you want to Extract Unique Items from a List in Excel, this article will be very helpful for you. Here, we will walk you through 10 easy methods to extract unique items from a list.

Here, we describe each of the methods step by step so that you can Extract Unique Items from a List effortlessly. We have used Excel 365. You can use any available Excel version.

1. Using Excel Array Formula to Extract Unique Items from List

The following Product List contains ID No and Product Name. We can see, there is repetition in Product Name. We want to extract unique products from that list. We will use the Array Formula to extract unique items.

➤ To begin with, we will write the following formula in cell  E5.

This formula is a combination of INDEX, MATCH, and COUNTIF functions.

➤ After that, we have to press Enter.

➤ We have to Drag Down the formula with the Fill Handle tool. 

➤ Finally, we can see the unique items in the Unique Products Using Array Formula table.

2. Using Excel UNIQUE Function to Extract from List

We want to extract unique items from the following Product Name by using the UNIQUE Function.

➤ First of all, we will type the =UNIQUE in cell E5, and a UNIQUE Function will appear.

➤ We have to select an array, which is our Product Name, therefore, we select C5 to C12.

➤ After that, we have to give a comma, ”,”, and we have to double click on False-Return unique rows.

➤ We will close the bracket and press Enter.

➤ Finally, we can extract unique items in the Unique Product List Using UNIQUE Function table. We can also see the formula in the Formula bar.

3. Using Non-Array Formula of LOOKUP and COUNTIF Functions

We can use a non-array formula that consists of LOOKUP and COUNTIF too. Let’s see how this formula helps us to extract unique items from a list.

Here, we type the following formula in cell E5.

➤ Then, we will press Enter.

➤ We will Drag Down the formula with the Fill Handle.

➤ Finally, we can see the extracted unique items in the Unique Product Using Non-Array Formula table. We can also see the formula in the Formula bar.

4. Extracting Excluding Duplicates Using Excel Array Formula

In this method, we will extract unique items excluding duplicates by using the  Array formula.

➤ First, we will type the following formula in cell E5.

Here, E4:$E$4 is the first cell of the column we want to put out the extracting result, and the List is the range of selected cells from C5 to C12.

The two INDEX functions return the initial and final value from the location that was derived by the COUNTIFS and MATCH respectively.

➤ After that, we will press Enter.

➤ Then, we will drag down the formula with the Fill Handle tool.

➤ Finally, we can see the two unique products excluding duplication.

5. Extracting Unique Items from List Using Advanced Filter

You can use an Excel feature called Advanced Filter to extract unique items from a list. Let’s get to know how to do that.

➤ First of all, select the range of data you want to extract

➤ After that, click on the Data tab.

Here, we’ve selected the cells and explored the Data tab. You will find an Advanced option there (within the Sort & Filter group of commands).

➤ After that, an Advanced Filter window will appear.

➤ We will select Copy to another location

➤ We will give the location $E$4 in the box Copy to

➤ Make sure to click on Unique records only.

➤ Now, click on OK.

➤ Finally, we can see the unique items are extracted in the table Unique Product using Advanced Filter.

6. Extracting Case-Sensitive Unique Values in Excel

If we have case-sensitive distinct values like in the following List table, we can use an Array formula to extract unique items from that list.

➤ First of all, we have to type the following formula in cell D3.

➤ After that, we will press Enter.

➤ We will drag down the formula by using Fill Handle.

➤ Finally, we can see extracted case-sensitive unique values in the table Case Sensitive Distinct Values.

7. Using Pivot Table to Extract Unique Items from List

We can extract unique items from the following Product List by using the Pivot Table.

➤ First of all, we will select the range of datasets from which we want to extract unique items.

➤ Here, we select data range C4 to C12.

➤ After that, select the Insert tab from Ribbon.

➤ Then, select the Pivot Table.

➤ After that, we have to select Existing Worksheet.

➤ We have to give a location. Here, we select the location E4 to E12.

➤ Mark Add this data to the Data Model

➤ Click OK.

➤ Finally, we can see that when we mark the Product name in the Pivot Table, the extracted Unique Product appears in the Row Levels table.

8. Applying VBA Macro to Extract Unique Items from List

From the following Product List table, we want to extract a unique Product Name using the VBA Code.

➤ First of all, we will type ALT+F11 in our working sheet. Here, we are working on Sheet8.

➤ After that, a VBA Project window will appear.

➤ We have to double-click on Sheet8.

➤ A VBA editor window will appear.

➤ We will type the following code in the VBA editor window.

Sub ExtractUnique()
Rem Declare a Long type variable
Dim lsrow As Long
Rem Define the variable
lsrow = Cells(Rows.Count, "C").End(xlUp).Row
Rem Applying AdvancedFilter
ActiveSheet.Range("C4:C" & lsrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("E4"), Unique:=True
End Sub

Here, we have declared a Long type variable and inserted the last row within that. Then, apply several ActiveSheet methods to copy the range while keeping Unique as True.

➤ We will close the VBA editor window and will go to our active Sheet8.

➤ There we will type ALT+F8, and a Macro Name window will appear.

➤ We will click on Run.

➤ Finally, we’ll see the unique products in the Product Name table.

9. Highlighting Unique Items with Conditional Formatting

We want to highlight a Unique Product Name from the following Product List.

➤ First, we select the Product Name from C5 to C12.

➤ Then, we will go to the Home tab.

➤ Select Conditional Formatting.

➤ And then, select New Rule.

A New Formatting Rule window will appear.

➤ Select Use a Formula to determine which cells to format.

➤ Write the following formula in Format values where this formula is true box.

➤ Then, click on Format.

A Format Cells window will appear.

➤ Click on the Fill option.

➤ Select a color, here, we select blue.

➤ Then, click OK.

➤ Now, see the Preview, and click OK.

➤ Finally, we can see the highlighted unique Product Name.

10. Using Conditional Formatting to Extract Unique Items

In the following Product List table, we want to hide duplicate Product Name, and we want to show unique product names only.

➤ To do so, first of all, we have to select the Product Name from cells C5 to C12.

➤ After that, we will go to the Home tab in the Ribbon, and we have to Select Conditional Formatting.

➤ Then, select the New Rule.

A New Formatting Rule window will appear.

➤ We have to select Use a Formula to determine which cells to format.

➤ We write the following formula in Format values where this formula is true box.

➤ Then, click on Format.

A Format Cells window will appear.

➤ We will select the Font option.

➤ Then, we have to select a white Theme Color.

➤ Click on OK.

➤ We can see the Preview and click OK.

➤ Now, we can see that the duplicate product names get hidden, as they are colored white.

➤ Now, we want to sort unique products at the top of the list. Therefore, we have to right-click on any of the cells. Here, we right-click on cell C5.

➤ After that, we have to select the Filter option.

➤ Now, we have to select Filter by Selected Cells Font Color.

➤ Finally, we can see there is only the unique Product Name on the Product List table.

Download Workbook

Conclusion

Here, we showed you some simple, easy, and effective methods that will help you extract unique items from a list in Excel. We hope you will find this article helpful. If you have any queries or suggestions, please feel free to contact us in the comment section.

<< Go Back to Unique Values | Learn Excel