Sabrina Ayon
While working in Microsoft Excel, sometimes we need to create data entry forms or Excel dashboards. When developing data entry forms, a drop-down list is a very helpful feature in Excel. It displays a list of things in a cell as a drop-down menu, from which the user may choose. When you have a list that you need to enter in a set of cells frequently, this could be beneficial. In this article, we will demonstrate the steps of creating excel dependent drop-down list with multiple words. Also, we will show you to clear or reset those lists.
Download Practice Workbook
You can download the workbook and practice with them.
What is a Dependent Drop Down List in Excel?
A drop-down list in Excel is a data validation function that allows choosing from a list of options. And, when the list of items varies depending on the value in another cell, these types of lists are referred to as dependent drop-down lists.
Steps to Create Dependent Drop Down List with Multiple Words in Excel
Sometimes, we might wish to utilize more than one drop-down list in Excel so that the items accessible in the second drop-down list are based on the first drop-down list’s selection. And, those are the dependent drop-down lists.
To create the dependent drop-down list with multiple words in excel, we are going to use the following list of data. We have 3 lists of data in the List sheet; the first one is Product where there is two product- fruits and vegetables, the second one is Fruit Item where there are six different types of fruits and Vegetable Item where there are five kinds of vegetables. Now, let’s see the step-by-step guidelines to create a dependent drop-down list with multiple words in Excel.
Step 1: Create Two Sheets in Excel Workbook for Dependent Drop Down List with Multiple Words
For example, we have created two sheets to make the work more efficient. One sheet is for Data List another one is for Data Entry. To create sheets in a workbook, simply, just click on the plus sign ‘+’.
And, that’s it! Our sheets are ready. Now, put data into it.
Step 2: Make a List for Dependent Drop Down Menu with Multiple words in Excel
In the Data List sheet, we have three data columns. In column B, we can see that there is a list of the product, and we have only two product lists. One is Fruit in column D, another one is Vegetable in column F. Now, to make a list for the drop-down list, follow the sub-steps down.
- First, select any cell of the column that you want to make a list for the drop-down menu.
- Second, go to the Home tab from the ribbon.
- Third, click on Format as Table under the Styles group.
- This will open the Create Table dialog box.
- Further, select the range $B$4:$B$6 and check-mark the My table has headers box.
- Then, click on the OK button.
- By the same token, do this for making the Fruit list and the Vegetable list. All three lists are structured as Excel Tables on the Data List sheet.
- Now, we need to create a named range for each list. For this, the items in the Product List must be selected but not the table header.
- Next, to the left of the formula bar, click on the Name field.
- After that, type the name that you want to give the range. So, we type Product.
- Finally, press Enter.
- Follow those steps and do the same for Fruit and Vegetable.
Step 3: Create the Primary Drop Down List in Excel
Now, we need to add the main drop-down list to the Data Entry sheet. To do this, follow the sub-procedure below.
- Firstly, select the data range B4:C6.
- Secondly, go to the Home tab on the ribbon.
- Then, under the Styles category, click on the Format as Table drop-down menu and select any formatted table.
- This will take you to the Create Table pop-up window.
- Now, choose the range in the box Where is the data for your table?. So, we choose $B$4:$C$6.
- Tick mark the box, beside My table has headers.
- Click OK.
Step 4: Add the Drop Down Lists That Are Reliant on Each Other
Now, we need to make a drop-down list. For this, let’s demonstrate the sub-steps down.
- In the beginning, select the range of cell B4:B6.
- Go to the Data tab from the ribbon.
- After that, click on Data Validation from the Data Validation drop-down list under the Data Tools category.
- This will open the Data Validation dialog box.
- Next, in the Setting menu, there is a drop-down list, from there select List.
- In the Source box, type the equal sign (‘=’) and then the name of the list which is Product. We named this in the Data List sheet.
- Finally, click OK.
- Now, for the dependent drop-down list, select cell C5 and go to the Data tab on the ribbon.
- Further, click on Data Validation from the Data Validation drop-down menu.
- This will open the Data Validation dialog box.
- Similarly as before, go to Settings and select List from the drop-down under the Allow section.
- On the Source, type the formula.
=INDIRECT(B5)- Then, click OK.
Here, the INDIRECT function will make the new select cell C5, dependent on cell B5.
- If cell B5 is blank, the message given below will appear. To proceed, choose Yes.
- Do the same for cell C6.
Step 5: Experiment with the Dependent Drop Down List Menu in Excel
Now, we can see that there is a drop-down list in each cell. If we select cell C5 and click on the arrow, it will show the list of fruits because, in cell B5, the Fruit product is selected.
- Click on cell C6’s arrow and you will see the list of vegetables there, cause in cell B6, the selected product is Vegetables.
Read More: How to Select from Drop Down and Pull Data from Different Sheet in Excel
Clear or Reset the Data of Dependent Drop Down List in Excel
When we select a list and subsequently alter the parent drop-down list, the dependent drop-down list does not change, resulting in an incorrect entry. To clear or reset the data of the dependent drop-down list we are going to use the Excel VBA and Conditional formatting.
1. Use Excel VBA to Delete Dependent Drop Down List with Multiple Words
Excel VBA enables you to impose far more complicated navigation, advanced execution, and many more constraints. To use the VBA to clear the dependent drop-down data entry, we need to follow the steps down.
- First, go to the Developer tab from the ribbon.
- Second, click on Visual Basic or press Alt + 11, to open the Visual Basic Editor.
- Instead of doing this, you can go to the sheet and click View Code, this will also open the Visual Basic Editor.
- Now, copy the code and paste it.
VBA Code:
Private Sub Worksheet_Change(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
exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub- Press Ctrl + S to save the code in your worksheet.
Note: Don’t change the code, just copy and paste it. If you make any changes, the code might not work properly.
- You can see the Product is now Fruit and the Item is Banana.
- Now, we want to change the Product to Vegetable.
- When we make the selection for the product this will automatically clear the item for it.
Related Content: How to Edit Drop-Down List in Excel (4 Basic Approaches)
Similar Readings:
- How to Extract Data Based on a Drop Down List Selection in Excel
- Add Blank Option to Drop Down List in Excel (2 Methods)
- How to Add Item to Drop-Down List in Excel (5 Methods)
- VBA to Select Value from Drop Down List in Excel (2 Methods)
- Unique Values in a Drop Down List with VBA in Excel (A Complete Guide)
2. Conditional Formatting to Highlight the Mismatched List in Excel
We can highlight the wrong entry and make it easier to understand the data. We are using a combination of VLOOKUP, ISERROR, INDEX, and MATCH functions for the condition. For this, let’s follow the procedure down.
- Select the cell, which has the dependent drop-down list. So, we select cell E5.
- Next, go to the Home tab and select New Rule from the Conditional Formatting drop-down menu under the Styles group.
- This will open the New Formatting Rule dialog box.
- Now, select Use a formula to determine which cells to format from the Select a Rule Type selection box.
- Type the formula on the Edit the Rule Description.
=ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))- After that, click on Format to select the preferable format to highlight the cell.
- Another pop-up window will appear, which is the Format Cells dialog box.
- From the Fill menu, pick a color to highlight the cells that you want to format.
- Then, click OK.
- This will take you to the New Formatting Rule dialog box again.
- Further, click OK on this dialog box.
- Finally, you can see that if you enter the wrong data, it will highlight the cell.
Here, the VLOOKUP function is used in the formula to determine if the item is in the dependent drop-down list or not. The ISERROR function is checking this and if it returns true only then this will highlight the cell.
Read More: Conditional Drop Down List in Excel (Create, Sort and Use)
Conclusion
The article will assist you to create a Dependent Drop Down List with Multiple Words in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
