The drop-down list is one of the outstanding features in Excel that is useful for Excel dashboards and data entry form creation. It helps you to create a list of items in a cell so that users can select an option from the options. This feature is particularly useful when you need to enter details like gender, religion, country, etc., in certain cells in a worksheet.
This article will show you how to create a dependent drop-down list in an Excel worksheet. But, let's discuss the meaning of the dependent drop-down list.
What is a Dependent Drop-down List in Excel?
A drop-down list lets you select an item from several lists in a cell. For example, you may use a drop-down list in a cell where you want users to select their gender from the options you provided.
It let users select an option from a predefined list instead of typing the needed entry in the cell.
A dependent drop-down list goes a step further, as it means the list of items that depends on the value in another cell.
For example, selecting a country from a drop-down list will require a dependent drop-down list if you need to select the continent first. That is, the country you want to select will depend on the continent you chose.
So, if you choose a continent, say Asia, the dependent drop-down list will show the list of countries in Asia.
But if you choose Africa, you will then see the African countries in the dependent drop-down list.
Before creating a drop-down list, you should list the items you want the drop-down to contain in the worksheet. You will see that I have listed each continent I want to use in this illustration and selected countries in the continent in each column.
Creating a Dependent Drop-down List in Excel
Below are the steps to create a dependent drop-down list in Excel.
Step 1: Select the Main Drop-down List Cell
First, select a cell where you want the main drop-down list to appear. For illustration, this is the cell that will contain the list of different continents.
Step 2: Data
Click on the Data tab at the top ribbon in the Excel environment.
Step 3: Data Validation
Select the Data Validation option from the Data tools section. It will display the data validation dialog box.
Step 4: Setup the Main Drop-down List
Set the main drop-down list from the data validation dialog box as follows:
- Select the Settings tab at the top left of the dialog box.
- Click the drop-down field under the Allow option and select list from the drop-down options.
- Click the Source field and enter the range of the items you want the drop-down list to contain using the format: =$A$2:$C$2. I used A2 to C2 because the continents I want in the main drop-down are listed in cells A2 to C2.
- Click OK and you have created the main drop-down list which is the continent drop-down list in this illustration.
Below is how it will look:
Step 5: Setup the Dependent Drop-down List
Since you have created the main drop-down list, you can now create the dependent drop-down list, which will contain the list of countries in this illustration, as follows:
- Select all the data sets in the worksheet, that is, A1 to C5 in this example.
- Select Formula at the top ribbon.
- Select the Create from Selection option in the Defined Names section or you can press the Ctrl + Shift + F3 buttons instead.
- In the 'Create Named from Selection’ dialog box, select the Top row option and uncheck all the others. Note that I am selecting the top-row option because the continent names are in the top row of the selection.
- Next, click OK.
- Select a cell where you want the dependent drop-down list to appear.
- Select the Data tab and select Data Validation.
- Select the Settings tab at the top left of the dialog box.
- Click the drop-down field under the Allow option and select list from the drop-down options.
- Click the Source field and enter the formula =INDIRECT(D2). Note that D2 is the cell name containing the main drop-down list I first created.
- Click OK.
The dependent drop-down list will appear in the selected cell in your worksheet.
If I select Africa in the main drop-down list, you will see African countries in the dependent drop-down list and the same happens if I select the other continents too.
You may also like to read:
What is Data Validation in Excel and How to Use It
How to Switch First and Last Names in Excel with Comma
How to Insert a Signature in Excel
How To Remove Empty Cells In Excel
