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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel drop down list unique values


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.

 Insert a Pivot Table to Create a Drop Down List with Unique Values in Excel

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

 Insert a Pivot Table

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

 Insert a Pivot Table

  • Finally, Excel will create a pivot table like the one below.

 Insert a Pivot Table to Create a Drop Down List with Unique Values in Excel

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.

 Insert a Pivot Table to Create a Drop Down List with Unique Values in Excel

  • Now, a new window titled Data Validation will appear. We will select List from the Allow drop-down menu.

 Insert a Pivot Table to Create a Drop Down List with Unique Values in Excel

  • Then, we will insert $A$4:$A$6 in the Source input box.
  • After that, we will click on OK.

 Insert a Pivot Table to Create a Drop Down List with Unique Values in Excel

  • Finally, we will see a drop-down list in cell C3 with the unique product categories.

 Insert a Pivot Table to Create a Drop Down List with Unique Values in Excel

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.

Create a Drop Down List with Unique Values by Removing Duplicates in Excel

  • Now, we will select all the cells in the new Country.
  • Then, we will click Remove Duplicates under the Data.

Create a Drop Down List with Unique Values by Removing Duplicates in Excel

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

Removing Duplicates in Excel

  • 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.
    Create a Drop Down List with Unique Values by Removing Duplicates in Excel
  • Finally, we will see that the new Country column has only 4 unique countries in it.

Removing Duplicates in Excel

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.

Removing Duplicates in Excel

  • Now, a new window titled Data Validation will appear. We will select List from the Allow drop-down menu.

Create a Drop Down List with Unique Values by Removing Duplicates in Excel

  • Then, we will insert $F$5:$F$8 in the Source input box.
  • After that, we will click on OK.

Removing Duplicates in Excel

  • Finally, we will see a drop-down list in cell F10 with only the unique countries.

Create a Drop Down List with Unique Values by Removing Duplicates in Excel

Read More: How to Remove Duplicates from Drop Down List in Excel (4 Methods)


Similar Readings


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

Create a Drop Down List with Unique Values by Removing Duplicates in Excel

Formula Breakdown:

  • The UNIQUE function will extract the unique values from the range D5:D14.
  • The FILTER function will return those unique cell values from the Country column that are not null or empty.
  • The SORT function will sort 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.

Create a Drop Down List with Unique Values by Removing Duplicates in Excel

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.

Use Helper Function to Create a Dynamic Drop Down List with Unique Values

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

Use Helper Function

  • Finally, we will see a drop-down list in cell F10 with only the unique countries.

Use Helper Function to Create a Dynamic Drop Down List with Unique Values

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

Use Helper Function

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

ASM Arman
ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo