Skip to main content
 

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

Author: Xiaoyang Last Modified: 2022-08-31

It may be easy for us to create a dependent drop-down list in Excel, but, if the source data contains duplicate values, all the duplicate values will be applied as well, this may be annoying. To exclude the duplicate values and only keep the unique ones when creating a dependent drop-down list, this tutorial will introduce some tricks for you.

Create dependent drop down lists with unique values only by Excel features

Create dependent drop down lists with unique values only by an amazing feature


Create dependent drop down lists with unique values only by Excel features

It is somewhat troublesome to create dependent drop-down lists with unique values only 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:

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.

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

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.

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.

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:

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

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.

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.

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:

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 only by an amazing feature

The above method may be headache for most of us, here, I will introduce a handy tool- Kutools for Excel, with its Dynamic Drop-down List feature, you can solve this problem with ease.

After installing Kutools for Excel, please do as this:

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

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.

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

Click to Download Kutools for Excel Now !


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

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!