Microsoft Excel is a classic feature for many users. So, it is nothing but a spreadsheet for many aspects. You can make use of it for various purposes. Moreover, you can create many useful sheets with the help of the Excel feature. There is a facility to create a dependent drop-down list. It provides many advantages for you. So, it helps to make data entry easier. You can easily manage your tasks with the help of this facility. Many users are facing the problem of ‘How to Create a Dependent Drop-Down List in Microsoft Excel?’. You need not worry about it at all, We are ready with the proper solution for this issue. By applying it to your system, you will reach the final solution. Thus, to make your task easy, we are providing here a tutorial on ‘How to Create a Dependent Drop-Down List in Microsoft Excel?’.
Table of Contents
How to Create a Dependent Drop-Down List in Microsoft Excel?
‘Dependent Drop-Down List’ is a feature with many benefits. So, you can select the item you want in the first list & choose the items to display in the second one. We are delivering here a tutorial on ‘How to Create a Dependent Drop-Down List in Microsoft Excel?’. You will get a detailed idea about the same. In this list, you can select the main type at your convenience & add select the sub-options. For example, ‘Fruit’ in one cell, and ‘Apple, Banana, Strawberry’ appear in the dependent drop-down list. So, you can easily choose them. Here we go for this informative tutorial now.
Also Read: How to Connect Google Sheets with Microsoft Excel.
Need to Create a Dependent Drop-Down List in Microsoft Excel
The ‘Dependent Drop-Down List’ in Microsoft Excel helps a lot with many advantages. Moreover, it gives a great platform for easy data entry. So, it becomes very task to enter the relevant data for you along with the collaborators. Hence, it provides many benefits from executing it properly. Thus, it becomes useful when you have a list of the items like names, products, regions, etc.
Useful Method Available For This Purpose
There are different methods available for this purpose. Thus, we are providing here thorough information regarding the same. So, go through the steps mentioned here & get your operation done. Go for it now.
Creating a Drop-down List
The very first thing you need to do is to create your drop-down list setup. So, it is essential to set it up before the dependent list there. Thus, it will display as shown below. The ‘Product name’ box is on one side & the ‘Item’ box is on the other side. So, when you search for the ‘Fruit’ option in the main list, it shows the searches as mentioned in the Item box.
Similarly, you should create your drop-down list as per your requirements. So, the next move is to add the naming & adding dependent drop-down list items for further operation. Thus, let’s move towards it now.
Dependent Drop-Down List Items (Naming & Adding Operation)
For the instance, we are focusing on the company department & employees here. So, we are using the ‘Departments’ for the first drop-down list & ‘Employees’ for the second list. You can make use of other entities also. Let’s suppose we have the 3 departments like ‘Marketing, Finance & Human Resources (HR)’ in our company. Further, each department has 3 employees.
So, for the ‘Department’ list, list down the items there. Then, select the cells. This activity places the cells in a group. Then, you can name the group easily. Do this necessary operation here. When you select the cells, go to the upper side of the ‘Name Box’ available on the left-hand side of the ‘Formula Bar’. Further, enter a name for the cell group you wish to. It is the foremost condition that the names for each group should match the list items in the first dropdown. So, keep this point in mind.
For an example purpose, see the following image.
Making of the Dependent Drop-Down List
Now, when you are done with everything above, you need to create the second sheet. So, moving towards it here.
1 ) Select the ‘Data Validation’ Tab
Thus, you need to make use of the ‘Data validation feature’ of the Excel tool. Simply select the cell where you wish to have the list. Then, follow the path below to get the ‘Data Validation’ tab for further operation.
Data tab --> Data Validation
2 ) Choose the ‘List’ Option
A pop-up window will appear on the screen. So, click on the ‘Settings’ option menu. Scroll down on the screen & choose the ‘List’ option under the ‘Allow’ segment. Moving ahead, check the ‘In-Cell Dropdown’ & ‘Ignore Blank’ boxes in front of this segment.
3 ) Adding the Formula
It’s time to switch towards the ‘Source box’. So, after reaching there, simply enter the formula given below. You need to replace the cell values with the cell containing the first drop-down list. Keep eys on this section & do he needful operation.
=INDIRECT($B$6)Them, click on the ‘OK” button available on the downward side of the displayed window.
In addition to the above-mentioned options, you can go for the ‘Input Message’ or ‘Error Alert’. So, go for selecting these tabs in the window. It will require the details. So, enter it carefully. After everything is done, just click the ‘OK’ button.
4 ) Testing Phase
Eventually, it comes to test the operation here. So, now, select an item in the first drop-down list. Further, you should see the items relevant to your selection as options in the second list displayed.
Conclusion
In this way, it’s all about creating a ‘Dependent Drop-Down List’ in Microsoft Excel. Thus, do adhere to the steps mentioned above. So, you will eventually reach the desired destination point. You can make use of this technique to make the tasks easier. We hope that this elaborative data will lead to a proper solution in the end. Stay connected with us for such informative tutorials on this platform.