Does using a drop-down list in Microsoft Excel make data entry easier for you or your colleagues? If you said yes and want to take it a step further, you can create a dependent drop-down list just as easily.
With a dependent drop-down list, you select the item you want in the first list, and that determines the items that appear as choices in the second.
See also: How to use the LEN function in Microsoft Excel
Getting started
Obviously, you'll need to have your first drop-down list set up and ready to go before you can create the dependent list. We have a full article with all the details you need to create a drop-down list in Excel for a refresher, so be sure to check it out.
Since creating the second list follows the same basic process, we'll start there. Then, we'll move on to the dependency setup.
See also: How to use the TRIM function in Microsoft Excel
Add and name dependent drop-down list items
For this tutorial, we'll use departments of our company for the first drop-down list, and then employees in each department for the second list.
Our departments include Marketing, Finance, and Human Resources (HR), and each has three employees. These employees are the ones we need to add and name.
Enter the items for the dependent list, and then select the cells. This places the cells in a group so that you can name the group. With the cells selected, go to the Name Box on the left side of the formula bar and enter a name for the group of cells.
The names for each group should match the list items in your first drop-down menu .
Using our example, we will name our teams after the departments in our first list: Marketing, Finance, and HR.
You can add your dependent list items to the same sheet as the list, or to a different sheet. For the purposes of this guide, you'll notice that we have everything on the same sheet.
See also: How to do simple mathematical operations (Summation) in Microsoft Excel
Creating the dependent drop-down list
Once all of your list items are on a sheet and named, it's time to create the second drop-down list. You'll use the Data Validation feature in Excel, just like when you created your first list.
Select the cell where you want the list. Then, go to the Data tab and click “Data Validation” in the Data Tools section of the ribbon.
Select the Settings tab in the pop-up window. At the bottom, select “List” and to the right, check the box for In-Cell Dropdown. Optionally, you can check the Ignore Blank box if you want.
In the Source box, enter the following formula. Be sure to replace the cell reference in parentheses with the cell that contains the first drop-down list.
If you want to include an input message or error notification, select those tabs in the pop-up window and enter the details. When you’re done, click “OK” to add the drop-down list to the cell.
Now, give your list a test. When you select an item in the first drop-down list, you should see the items associated with your selection as options in the second list.
For faster data entry for you or your colleagues, try a dependent drop-down list in Excel!
Information source: howtogeek.com
