Dependent Drop-down Lists in Excel
This tutorial shows an easy, step-by-step way to make dependent drop-down lists in Excel. Here’s what we are trying to achieve:
The user selects Pizza from a drop-down list.
As a result, the second drop-down list displays the Pizza options.
Follow these steps to create dependent drop down list.
1. Create the named range listed below on Sheet 2.
| Name | Range Address |
|---|---|
| Food | A1:A3 |
| Pizza | B1:B4 |
| Pancakes | C1:C2 |
| Chinese | D1:D3 |
2. On the first sheet, select cell B1.
3. Now, go to the Data tab. Choose “Data Validation” from the Data Tools option.
The ‘Data Validation’ dialog box appears.
4. In the Allow box, click List.
5. In the Source box, type =Food.
6. Click OK.
Result:
7. Next, select cell E1.
8. In the Allow box, click List.
9. In the Source box type =INDIRECT($B$1).
10. Click OK.
Result:
Explanation: The INDIRECT function converts a text into a valid cell reference. Suppose the user picks Chinese from the first drop-down list. =INDIRECT($B$1) returns the Chinese reference. As a result, the second drop-down menu lists Chinese items.
7/9 Completed! Learn much more about data validation ➝
Next Chapter: Keyboard Shortcuts