Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to create dependent drop down lists with unique values in Excel?

Author Xiaoyang Last modified

Dependent drop-down lists are a powerful tool in Excel for data validation and ensuring consistent data entry. When combined with the requirement to display only unique values, they become even more valuable. To exclude the duplicate values and only keep the unique ones when creating a dependent drop-down list, this tutorial provides step-by-step instructions to create dependent drop-down lists in Excel with unique values only.

create dependent drop down lists with unique values

Create dependent drop down lists with unique values by Excel features

Create dependent drop down lists with unique values by Kutools for Excel

Create dependent drop down lists with unique values by Excel features

It is somewhat troublesome to create dependent drop-down lists with only unique values  in Excel, you should apply the below operation step by step:

Step1: Create range names for the first and second drop-down list data

1. Click "Formulas" > "Define Name", see screenshot:

Click Formulas > Define Name

2. In the "New Name" dialog box, enter a range name Category into the "Name" textbox (you can type any other name you need), and then, type this formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$100)) into the "Refers to" textbox and finally click the OK button:

Note: A2:A100 is the data list you will create the first drop down list based on, if you have large data, just change the cell reference you need.

set options for the first drop down

3. Go on creating a range name for the second drop-down, click "Formulas" > "Define Name" to open the New Name dialog box, enter a range name Food into the "Name" textbox (you can type any other name you need), and then, type this formula =OFFSET($B$2,0,0,COUNTA($B$2:$B$100)) into the "Refers to" textbox, finally click the OK button:

Note: B2:B100 is the data list you will create the dependent drop-down list based on, if you have large data, just change the cell reference you need.

set options for the second drop down

Step2: Extract the unique values and create the first drop-down list

4. Now, you should extract the unique values for the first drop-down list data by entering the following formula into a cell, pressing Ctrl + Shift + Enter keys together, then dragging the fill handle down to the cells until error values display, see screenshot:

=INDEX(Category,MATCH(0,COUNTIF($D$1:D1,Category),0))
Note: In the above formula, Category is the range name you created in step 2, and D1 is the above cell of your formula cell, please change them to your need.

enter a formula to extract the first type unique values

5. Then, please create a range name for this new unique values, click "Formulas" > "Define Name" to open the "New Name" dialog box, enter a range name Uniquecategory into the "Name" textbox (you can type any other name you need), and then, type this formula =OFFSET($D$2, 0, 0, COUNT(IF($D$2:$D$100="", "", 1)), 1) into the "Refers to" textbox, at last, click OK button to close the dialog.

Note: D2:D100 is the unique values list you just extracted, if you have large data, just change the cell reference you need.

create a range name for the new unique values

6. In this step, you can insert the first drop-down list. Click a cell where you want to insert the drop down list, and then, click "Data" > "Data Validation" > "Data Validation", and in the "Data Validation" dialog box, select "List" from the "Allow" drop down, then enter this formula: =Uniquecategory into the "Source" textbox, see screenshot:

set options to insert the first drop-down list

7. Then, click OK button, the first drop-down list without duplicate values is created as below screenshot shown:

the first drop-down list without duplicate values is created

Step3: Extract the unique values and create the dependent drop-down list

8. Extract the unique values for the secondary drop-down list, please copy and paste the below formula into a cell, and then press Ctrl + Shift + Enter keys simultaneously, then drag the fill handle down to the cells until error values display, see screenshot:

=INDEX(Food,MATCH(0,COUNTIF($E$1:E1,Food)+(Category<>$H$2),0))
Note: In the above formula, Food is the range name you created for the dependent drop-down list data, Category is the range name you created for the fist drop-down list data, and E1 is the above cell of your formula cell, H2 is the cell you have inserted the first drop down list, please change them to your need.

enter a formula to extract the second type unique values

9. Then, go on creating a range name for this secondary unique values, click "Formulas" > "Define Name" to open the "New Name" dialog box, enter a range name Uniquefood into the "Name" textbox (you can type any other name you need), and then, type this formula =OFFSET($E$2, 0, 0, COUNT(IF($E$2:$E$100="", "", 1)), 1) into the "Refers to" textbox. Finally, click OK button to close the dialog.

Note: E2:E100 is the secondary unique values list you just extracted, if you have large data, just change the cell reference you need.

create a range name for this secondary unique values

10. After creating the range name for the secondary unique values, now, you can insert the dependent drop-down list. Please click "Data" > "Data Validation" > "Data Validation", and in the "Data Validation" dialog box, select "List" from the "Allow" drop down, then enter this formula: =Uniquefood into the "Source" textbox, see screenshot:

set options to insert the second drop-down list

11. Click OK button, the dependent drop-down lists with unique values only are created successfully as below demo shown:

Create dependent drop down lists with unique values by Kutools for Excel

The above method, while effective, can be quite time-consuming and complex for most of us, especially when working with large datasets or if you're not familiar with Excel's advanced functions like named ranges or dynamic formulas. Fortunately, with Kutools for Excel, this process becomes much easier and faster. Kutools provides a user-friendly interface and powerful tools that allow you to create dependent drop-down lists with unique values in just a few clicks, eliminating the need for manual setup or complex formulas.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Click "Kutools" > "Drop-down List" > "Dynamic Drop-down List", see screenshot:

click Dynamic Drop-down List feature of kutools

2. In the "Dependent Drop-down List" dialog box, please do the following operations：

  • Select "ModeB: 2-5 Levels dependent Drop-down list" from the "Mode" section;
  • Select the data that you want to create the dependent drop-down list based on from the "Data Range" box;
  • Then, select the output range where you want to put the dependent drop-down list from the "Output Range" box.
  • Finally, click OK button.

set the options in the dialog box

3. Now, the dependent drop-down lists are inserted into the selection while the duplicate values are excluded as well. See below demo:

Tip: With this feature, you can easily sort the contents of the drop-down list in alphabetical order, making the data more organized and user-friendly. In the dialo gbox, click "Advanced Settings" button, and then select "Sort Alphabetically". Now, when you click the drop down list, the items are sorted alphabetically.
select Sort Alphabetically option items are sorted alphabetically in the drop down list

Creating dependent drop-down lists with unique values in Excel can significantly enhance data accuracy and usability. Whether you prefer Excel's native tools or an advanced add-on like Kutools, dependent drop-down lists with unique values are an invaluable addition to any data management workflow, ensuring efficiency and precision in your work. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.

More relative articles:

  • Create Drop Down List With Images In Excel
  • In Excel, we can quickly and easily create a drop down list with cell values, but, have you ever tried to create a drop down list with images, that is to say, when you click one value from the drop down list, its relative image will be displayed at once as below demo shown. In this article, I will talk about how to insert a drop down list with images in Excel.
  • Create Drop Down List With Multiple Checkboxes In Excel
  • Many Excel users tend to create drop down list with multiple checkboxes in order to select multiple items from the list per time. Actually, you can’t create a list with multiple checkboxes with Data Validation. In this tutorial, we are going to show you two methods to create drop down list with multiple checkboxes in Excel.
  • Create Multi Level Dependent Drop Down List In Excel
  • In Excel, you may create a dependent drop down list quickly and easily, but, have you ever tried to create multi-level dependent drop down list as following screenshot shown? This article, I will talk about how to create a multi-level dependent drop down list in Excel.
  • Create Drop Down List But Show Different Values In Excel
  • In Excel worksheet, we can quickly create a drop down list with the Data Validation feature, but, have you ever tried to show a different value when you click the drop down list? For example, I have the following two column data in Column A and Column B, now, I need to create a drop down list with the values in Name column, but, when I select the name from the created drop down list, the corresponding value in Number column is displayed as following screenshot shown. This article will introduce the details to solve this task.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in