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
- Step1: Create range names for the first and second drop-down list data
- Step2: Extract the unique values and create the first drop-down list
- Step3: Extract the unique values and create the dependent drop-down list
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:
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:
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:
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.
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:
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.
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
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!
