ASM Arman
While working with categorical information in Excel i.e., products of different categories, you might need to create a drop-down list with unique values from this categorical information for data validation. In this tutorial, I will show you how to create a drop-down list with unique values in Excel.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
4 Easy Ways to Create a Drop Down List with Unique Values in Excel
Let’s assume a scenario where we have an Excel file that contains information about the products that a country exports to different countries in Europe. We have the Product name, Category, and Country to which the product is exported. We will use this Excel worksheet to create a drop-down list with unique values. The image below shows that we have created a drop-down list with unique values from the Country column.
Method 1: Insert a Pivot Table to Create a Drop Down List with Unique Values in Excel
An easy way to create a drop-down list with unique values is to use a pivot table. Let’s see how we can create a drop-down list with unique values from the Category column.
Step 1:
- First, you have to select all the cells of the data range including the column headers.
- Then, click on PivotTable under the Insert.
- Now, a dialog box will appear like the following image. Excel will automatically select the data for you. The default location for the new pivot table will be a New Worksheet.
- Then, we will click OK.
- Now a new sheet will appear. It will have a box on the right side of the sheet titled PivotTable Fields. You will find it on the left side of the sheet if you are working with Excel 2007/2010.
- Then, we will drag the Category field to the Rows.
- Finally, Excel will create a pivot table like the one below.
Step 2:
- Next, we will select a cell to create a drop-down menu. For example, we have selected cell C3.
- Now, we will click on the Data Validation drop-down under the Data.
- Then, we will select the Data Validation from the drop-down.
- Now, a new window titled Data Validation will appear. We will select List from the Allow drop-down menu.
- Then, we will insert $A$4:$A$6 in the Source input box.
- After that, we will click on OK.
- Finally, we will see a drop-down list in cell C3 with the unique product categories.
Read More: Create Excel Drop Down List from Table (5 Examples)
Method 2: Create a Drop Down List with Unique Values by Removing Duplicates in Excel
Another efficient way is to remove the duplicates and create a drop-down list with the remaining unique values. We will create a drop-down list with unique values from the Country column using this method.
Step 1:
- We will copy the cells of the Country column including the column header pressing CTRL+C and paste them into column F pressing CTRL+V.
- Now, we will select all the cells in the new Country.
- Then, we will click Remove Duplicates under the Data.
- Now, a new window titled Remove Duplicates will appear. We will see that Country is selected under the Columns.
- Next, we will click on OK.
- Now, we will see that a pop box has appeared telling us that 6 duplicate values were found and removed while 4 unique values remain intact.
- Next, we will click on OK.
- Finally, we will see that the new Country column has only 4 unique countries in it.
Step 2:
- Next, we will select a cell to create a drop-down menu. For example, we have selected cell F10.
- Now, we will click on the Data Validation drop-down under the Data.
- Then, we will select the Data Validation from the drop-down.
- Now, a new window titled Data Validation will appear. We will select List from the Allow drop-down menu.
- Then, we will insert $F$5:$F$8 in the Source input box.
- After that, we will click on OK.
- Finally, we will see a drop-down list in cell F10 with only the unique countries.
Read More: How to Remove Duplicates from Drop Down List in Excel (4 Methods)
Similar Readings
- Create a Searchable Drop Down List in Excel (2 Methods)
- How to Create Drop Down List with Filter in Excel (7 Methods)
- Excel Drop Down List Not Working (8 Issues and Solutions)
- Auto Update Drop Down List in Excel (3 Ways)
- Multiple Dependent Drop-Down List Excel VBA (3 Ways)
Method 3: Use the UNIQUE Function to Create a Drop Down List with Unique Values in Excel
Steps:
- If you have access to Microsoft Office 365, you can use the UNIQUE function to remove the duplicate values from a column, range, or a list and create a list with only the unique values. We will write the following formula in cell F5 to do that.
=SORT(FILTER(UNIQUE(D5:D14),UNIQUE(D5:D14)<>0))
- Upon pressing ENTER, we will new Country column has only 4 unique countries in it. We can follow the same steps we have done above to create a drop-down list using the unique countries.
Read More: How to Create a Drop Down List with Unique Values in Excel (4 Methods)
Method 4: Use a Formula to Create a Dynamic Drop Down List with Unique Values
The easiest and most convenient way is to use a helper function to create a dynamic drop-down list that will update automatically every time we add new items to the column, range, or list. We will follow the below steps to do that.
Steps:
- First, we will follow the steps we have followed above to create a drop-down list. We will select List from the Allow drop-down menu.
- But the difference will be that this time instead of selecting the data range of the new Country column, we will insert the following formula in the Source input box.
=OFFSET($F$5, 0, 0, COUNTA($F$5:$F$100), 1)Formula Breakdown:
- The COUNTA function will count the number of cells in a range that are not empty.
- The OFFSET function will start from a particular cell reference, moves to a specific number of rows down, then to a specific number of columns right, and then extracts out a section from the data set having a specific height and a width.
- After inserting the formula, we will then click on OK.
- Finally, we will see a drop-down list in cell F10 with only the unique countries.
- But now if we insert a new row in our data range with a new country Italy, we will see that the drop-down list in cell H5 has automatically updated to include Italy as a new item in the list.
Read More: How to Create Dynamic Dependent Drop Down List in Excel
Quick Notes
🎯 The UNIQUE function is an exclusive function currently available only for Excel 365. So, it will not work in your worksheet if you do not have Excel 365 on your PC.
🎯 Read this article to learn more about how to create a dynamic drop-down list using the Excel OFFSET function.
Conclusion
In this article, we have learned how to create a drop-down list with unique values in Excel. I hope from now on you can create a drop-down list with unique values in Excel easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
Related Articles
- VLOOKUP with Drop Down List in Excel
- How to Fill Drop-Down List Cell in Excel with Color but with No Text
- How to Make a Drop Down List in Excel (Independent and Dependent)
- Edit Drop-Down List in Excel (4 Basic Approaches)
- How to Create a Drop Down List From Another Sheet in Excel (2 Methods)
- How to use IF Statement to Create a Drop-Down List in Excel
