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.
Contact Us