How to Create a Drop Down List with Unique Values in Excel (4 Methods)

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.

How to Create a Drop Down List with Unique Values in Excel: 4 Ways

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

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.

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))

Formula Breakdown:

The UNIQUE function extracts the unique values from the range D5:D14.
The FILTER function returns those unique cell values from the Country column that are not null or empty.
The SORT function sorts the unique values of the Country column in an alphabetical A-Z order.

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.

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, move to a specific number of rows down, then to a specific number of columns right, and then extract out a section from the data set having a specific height and 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.

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.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

Drop Down List with Unique Values.xlsx

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 leave a comment below. Have a great day!!!

<< Go Back to Create Drop-Down List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!