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. 

What is a Dependent Drop-Down List in Excel

A dependent drop-down list in Excel is a type of data validation feature that allows the options available in one drop-down list to depend on the selection made in another drop-down list. It’s commonly used when you have hierarchical or related data and you want to create a more organized and user-friendly way to select values.

How to Create Dependent Drop-Down List in Excel

Follow the steps to create a dropdown list in Excel:

Step 1: Select the Cells

Select the cell where you want the main Drop to downlist 

Step 2: Navigate to the Data tab and select Data Validation

Go to Data, and click on Data Validation. This will open a data validation dialogue box.

Step 3: 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, Pop-up, and Data Analytics, and the skills required to master them. 

Steps to Create Drop-Down Lists

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

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

Step 2: Select the Column

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

Step 3: Click on Create from Selection

Click on the Create From Selection and a pop-up appears. 

Step 4: Select Top Row

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. 

Step 5: Click on the Name Box and Select the option

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

Step 6: Redirected to the Data

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. 

Step 7: Repeat for Data Structure and Data Analytics

Similarly, create a named range for Data Structures and Data Analytics. 

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 the help of Drop Down List in Excel.

How to Create Drop-Down List in Excel

Step 1: Select the Cells

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

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

Step 3: Select Data Validation from drop-down

Three options appear, again click on Data Validation. 

Step 4: Select List from the type of data

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. 

Step 5: Select Cells from the worksheet

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. 

Step 6: Select Value from the list

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

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. The dependent Drop drop-down list is the advanced version of the 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. 

Steps to Create Dependent Drop-Down List in Excel

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 Reset /Clear Contents of the Dependent Drop-Down List Automatically

When you have made the selection and then you need to change the parent drop-down, the dependent drop-down list will not change and therefore it will become the wrong entry.

For example: If you select the ‘Ram’ in the name category and age ’21’, and then go back and change the ‘name’ the age will continue to show ’21’.

You can use VBA to make sure the contents of the dependent drop-down lists reset when the main drop-down list is changed. Below is the VBA code to clear the contents of a dependent drop-down list:

Private Sub Worksheet_Chnage(ByVal Target As Range)

On Error Resume Next

If Target.Column = 4 Then

If Target.Validation.Type = 3 Then

Application.EnableEvents= False

Target.offset(0,1).ClearContents

End if

End if

Exit Handler:

Application.EnableEvents = True

Exit Sub

End Sub

Follow the below steps to use the above code.

Step 1: Copy the above code.

Step 2: In the Excel Workbook where you have the dependent drop-down list, click on the Developer tab, and within the ‘code’ group, click on Visual Basic.

Step 3: In the VB Editor Window, on the left in the Project Explorer. You can see all the worksheet names. Double-click on the one that has the Drop-down list.

Step 4: Paste the code in the code window on the right.

Step 5: Close the VB Editor.

Now, whenever you will change the main drop-down list, the VBA code will clear the content of the dependent drop-down list.

How to Highlight cells in Excel

You can also use the Conditional Formatting trick that will highlight the cell whenever there is a mismatch.

Below are the steps to highlight mismatches in the dependent drop-down lists:

Step 1: Select the cell that has the dependent drop-down lists.

Step 2: Click Home> Select Conditional Formatting> New Rule.

Step 3: In the New Formatting Rule Dialog box, Select ‘use a formula to determine which cells to format.

Step 4: In the Formula Field, Enter the Following formula: =ISERROE(VLOOKUP(E14, INDEX($A$2:$B$6,, MATCH(D3,$E$7:$F$11)),1,0)) and set the Format

Step 5: Click OK.

The above Formula uses the VLOOKUP Function to check whether the item in the dependent drop-down list is one from the main category or not. If it isn’t from the category the formula returns the Error. This is used by the ISERROR Function to return TRUE which tells conditional Formatting to highlight the cell.

FAQS on Dependent Drop-Down List in Excel

Q1: What is the Dependent Drop-down list in Excel?

Answer:

It shows a list of items as a drop-down in a cell, and the user can make a selection from the drop-down. This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells.

Q2: What are the ways to create a drop-down list in Excel?

Answer:

You can create a drop-down list :

• Using Data from Cells
• Entering Data Manually
• Using the OFFSET formula

Q3: How to create a drop-down list using data from cells?

Answer:

Follow the below steps to create a drop-down list using data from the cell:

Step 1: Select a cell where you want to create the drop down list.

Step 2: Go to Data > Dta tools> Data Validation

Step 3: In the Data Validation dialog box, within the setting tab, select list as the validation criteria.

Step 4: Now in the Source field, select the cells using the mouse and click ok, or else you can also write the cell range manually. This will insert the Drop -down list in Excel.

Q4: How to create a drop-down list using Excel Formulas?

Answer:

Follow the below steps to create a drop-down list using Excel Formulas:

Step 1: Select a cell where you want to create a drop-down list

Step 2: Go to data > Data tools> Data Validation

Step 3: In the Data Validation dialog box, within the settings tab, select list as the Validation criteria

Step 4: In the source field, Enter the formula: =OFFSET($A$2,,0,0,5)

Click OK.