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.
Similar Reads
How to Make a Dynamic Dependent Drop-Down List in Excel
Dynamic Drop Down List in Excel 365: Quick StepsPrepare Your DataConvert the List into an Excel TableCreate the Drop-Down List Using Data ValidationTest the Dynamic Drop-Down ListCreating dynamic dependent drop-down lists in Excel is a powerful way to improve data entry accuracy and user experience,
5 min read
How to Edit, Copy and Delete Drop Down List in Excel
Excel is a versatile tool used globally for various data management tasks, including the use of drop-down lists to simplify data entry and ensure consistency. However, there might be times when you need to modify these lists to keep up with changes in data or requirements. These essential techniques
11 min read
How to Create a Drop-Down List in Excel: 2 Easy Methods
How to Build Drop Down Menu in Excel: Quick StepsOpen MS Excel Spreadsheet >> Enter List Items Select Cell >> Go to Data Validation Choose List Option Set the Source >>Click OKHave you ever typed the same data repeatedly in Excel, only to make typos or inconsistencies? A drop-down
9 min read
How to Create a Dynamic Chart with Drop down List in Excel?
The Dynamic Charts are the chart that gets updated itself when the range of underline data changes. In these types, of charts the dynamic range is used as the source data of the chart. So, as the data changes the dynamic range gets updated instantly which further updates the chart according to the n
4 min read
Compare Data in an Excel Chart using Drop Down Lists
An Excel chart is a graphical representation of a set of data that can be used for analyzing the data easily. A drop-down list allows the user to choose a specific item or element from a list. Here, we will compare and analyze data using an Excel chart with the help of a drop-down list. We will use
6 min read
How To Create Dot Plots In Excel?
A Dot plot is a type of chart used in statistics for representing relatively small data sets where the values are uniquely categorized. A dot plot is also known as dot chart or strip chart. A dot plot and a bar graph are similar in the sense that the height of each âbarâ of dots is equal to the numb
2 min read
How to Create Dropdown List in Google Sheets
Adding a dropdown list in Google Sheets can make your spreadsheets more dynamic and user-friendly. This feature allows you to create a list of options that users can select from, making data entry quicker and reducing errors. Whether you're managing a budget, tracking projects, or organizing data, d
9 min read
How to Create a Dashboard in Excel
Bringing data to life is essential for clear communication, and creating a dashboard in Excel is one of the most effective ways to present key information in a visual, interactive format. Whether youâre tracking project progress, visualizing financial metrics, or summarizing performance data, learni
5 min read
How to Add Drop Down List in Table Cell ?
Drop-downs are the user interface elements. Drop Down List is used to select one out of various options. This article focuses on creating a drop down list in the table cell which helps in selecting the choices directly from the table itself. Below are the approaches to add a Drop Down List in a Tabl
3 min read
How to define an option in a drop-down list in HTML5 ?
In this article, we will learn how to define an option in a drop-down list in HTML5. The <select> element is used to create a drop-down lists in HTML. This is generally used to present a list of options to the user in a form so that the user could choose one option as the needed one. The <o
2 min read