Note: The other languages of the website are Google-translated. Back to English

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

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.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations