Steps to Create Dependent Drop-Down List in Excel

Dependent Drop Down List in Excel

How to Reset /Clear Contents of the Dependent Drop-Down List Automatically

Step 1: Select the Data Go to the Formulas Tab and Select Create from the Selection

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

Step 2: Select Top Row

A pop-up appears to select Top-Row only. Click Ok.

Step 3: Go to Name Box and Select your Choice

In the Name Box, you have got Name and Age.

Step 4: Go to the Cell where you want to apply Data validation, Click the Data tab and Select Data Validation

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

Step 5: Select List in the Allow section

Step 6: Select the cells in which you want Data Validation

Select the Cells in which you want to have Data Validation. Click Ok. Your Drop drop-down list is Created.

Step 7: Repeat Steps 4-6 to create more drop down

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.

Step 8: Select List From the drop-down and Apply the Formula

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.

Step 9: Preview the Results

Hence, you have created the Dependent Drop Down List which changes with the Drop Down List.

Note: If the main category is more than one word (for example, “first name”instead of name) then you need to use the formula =INDIRECT(SUBSTITUTE(E14,” “,”_”)), instead of simple INDIRECT function shown above.

How to Create a Dependent Drop Down List in Excel

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

Sometimes you may want to use more than one drop-down list in Excel, Such that the items available in the second Drop-down list are dependent on the selection made in the first drop-down list. These are called Dependent drop-down lists.