How to Create a Dependent Dropdown List in Your Excel Worksheet

We have introduced many useful tips about the dropdown list in our previous articles. And here we will introduce the method of creating a dependent dropdown list in your worksheet.

The dependent dropdown list can be very helpful when there is a bunch of information in your worksheet. When you choose an item from the dropdown list in a cell, the items in another dropdown list will change the category accordingly. Thus, your work will be more convenient. Below are the steps on how to create such a dependent dropdown list.

Create a Dependent Dropdown List

The image below shows two different categories in a worksheet. And now you can create a dependent dropdown list according to this range.Example

  1. Select the first range in column A.
  2. And then click the tab “Formulas” in the ribbon.
  3. After that, click the button “Define Name” in the toolbar.
  4. In the dropdown list, there will be a name in the first text box. Excel will generate the name automatically according to your selection.
  5. And then select the target range for the “Refers to” text box.New Name
  6. After that, click the button “OK” in the window to save the setting.
  7. Because the name of the new name will be “Sales_Representative”, you need to change the text in the cell A1 accordingly.
  8. Now repeat the step 1-6 and create a name for the second column. Here this name is “Product”. Therefore, you don’t need to modify in the cell B1.
  9. After that, you need to create the dropdown list in the worksheet. Click the target range for the first dropdown list. Here we will click the range E2:E6.
  10. And then click the tab “Data” in the ribbon.
  11. Next click the button “Data Validation”.
  12. In the “Data Validation” window, choose the “List” for the “Allow” text box.
  13. And then click the cell A1:B1 for the “Source”.Data Validation
  14. When you finish the setting, click the button “OK” to save the setting.
  15. After that, you will create the dependent dropdown list. Click the target cell in the worksheet. We will click the range F2:F6 in the worksheet.
  16. Repeat the steps 10-12 for the data validation.
  17. Next input this formula into the text box of “Source”:

=INDIRECT(E2)

  1. After that, click the button “OK” to save the setting.
  2. Next you will see a new window pop up. Here you need to click the button “Yes”.Yes

Now you have finished all the settings in the worksheet. When you choose a category in the first cell, the items in the dependent dropdown list will also change accordingly.

From the above steps, you can see that it is veryResult easy to create such a dependent dropdown list. You may also have a try in your own worksheet.

Prepare a Contingency Plan for Data Disaster

Excel corruption is also one of the data disasters in our daily life. Most of the time, those disasters will happen abruptly. And the result can be unimaginable. The only thing you can do after those disasters is trying to repair those corrupt files. In order to repair corrupt Excel safely and easily, you can turn to a third-party tool for help. This tool has been used by many other users and has received good news.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word docx data damage and outlook repair software products. For more information visit www.datanumen.com