Skip to content
Related Articles

Related Articles

How to Create a Dependent Drop Down List in Excel?

View Discussion
Improve Article
Save Article
  • Last Updated : 24 Feb, 2022
View Discussion
Improve Article
Save Article

Dependent Drop Down Lists allows you to validate the data and make the data entry and data filtering very smooth and time-saving. Dependent Drop Down Lists make chances of fewer errors in one’s worksheet. The prerequisites for learning Dependent Drop Down List is Create from Selection and Drop Down List. 

Create From Selection

Create from selection helps you to create a named range of a cell. For Example, the Data set is Web Development, Data Structures and Data Analytics, and the skills required to master them. 

Data-Set

Steps to Create Drop-Down Lists

Step 1: Go to the Formulas tab, you will find an option Clear from Selection. 

Select-Clear-from-Selection

Step 2: Select the column, you want to create a named range, here E6:E11. 

Select-the-column

Step 3: Click on the Create From Selection and a pop up appears. 

Choose-option-in-Pop-up

Step 4: This pop up tells from which value you want to create a named range. More generally we will be using the Top row. The top row specifies that the name of the selected range will be the value written in the Top row. Click ok and a named range is obtained from E6:E11. 

Choose-names-from-values

Step 5: Click on the Name box and you will find Web Development added to the Name box list. 

Name-added-in-the-name-box

Step 6: By clicking on the Web development in the Name box, you will be directed to E7:E9. With this, you could be redirected back to the name range despite you being anywhere in your worksheet. 

Clicking-on-the-name-box-redirects-to-range-in-sheet

Step 7: Similarly, create a named range for Data Structures and Data Analytics also. 

Step 8: Currently, you might not be able to understand the benefit of Create from Selection but as soon as you will study Dependent Dropdown List in Excel, you will understand the power of Create from Selection. 

Note: The Scope of Create with Selection is in a work book and not in an individual Worksheet. i.e. if you create multiple worksheets then also the list inside the Name Box will retain itself. 

Drop Down List in Excel 

Drop Down List in Excel helps to limit the data that you want to enter in your worksheet. For Example, A list of Students is given and we want to enter the data whether that particular student is Pass or Fail. This task can be achieved with help of Drop Down List in Excel.

Steps to Create 

Step 1: Select the cells in which you want to apply Data Validation.

Select-Cells-to-apply

Step 2. Go to Data Tab, and click on the Data Validation

Click-on-Data-Validation

Step 3: Three options appear, again click on Data Validation. 

Choose-Data-Validation-from-Three-options

Step 4: A pop-up opens. Inside the Allow, select the type of data you want to allow in that Drop Down List. Most frequently, we use List. So, click on List. 

Select-the-type-of-Data

Step 5: A Source Option appears. Now there can be two ways to fill the Source Option either write the Text that you want to allow inside that cell or select the cells inside the worksheet whose value you want to allow in your List. After that click Ok

Fill-the-source-option

Step 6: Now the List looks like this. You could select any value from that list.

Choose-option-from-list-created

Step 7: This makes our work easier and faster. 

Dependent Drop Down List in Excel

Now you know about Create From Selection and Drop Down List in Excel. Dependent Drop Down List is the advanced version of Drop Down List. In this, we have Dependencies of Dependencies. We could Create as many dependencies as we want.

Consider a data set with Name and Age

Select-dataset

Steps to Create 

Step 1: Select the whole Data Set and go to the Formula tab and click on Create From Selection. 

Select-create-from-selection-under-formulas-tab

Step 2: A pop-up appears select Top-Row only. Click Ok.  

Choose-selection-from-values

Step 3: In the Name Box you have got Name and Age. 

data-range-in-name-box

Step 4: Try going to any cell on which you want to apply Data Validation. For example E14. Then Go to Data Tab and click Data Validation. A pop-up appears. 

Choose-Data-Validation-from-Three-options

Step 5: Select List in the Allow section. 

Select-the-type-of-Data

Step 6: Select the Cells in which you want to have Data Validation. Click Ok. Your Drop Down List is Created. 

Select-Cells-to-apply

Step 7: Select any other cell in which you want to apply the Dependent Drop Down List. For example E15. Again Go to Data Tab and click on Data validation and a pop-up appears. 

Select-other-cells-to-apply-data-validation

Step 8: Select List in the Allow Section and Use =INDIRECT(REF_SRC) function in the source as shown in the image. For Example, =INDIRECT(E14). and click Ok

INDIRECT function returns a valid cell reference from a given text String. 

Apply-Indirect-function

Step 9: Hence, you have Created the Dependent Drop Down List which changes with the Drop Down List.

Dependent-Drop-Down-List-CreatedSecond-list


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!