The key to creating a dependency drop-down list in Excel is to understand the use of data structures and functions. There are three main methods. 1. First organize the basic data source, such as the classification and corresponding subitems are listed separately. It is recommended to use naming scope management; 2. Use the "Data Verification" "INDIRECT" function to achieve linkage, provided that the main pull-down option is consistent with the named area name; 3. Use the FILTER function (suitable for Excel 365 or 2021 and above) to achieve more flexible linkage through dynamic filtering. The formula is =UNIQUE(FILTER(sub-item column, classification column =main pull-down cell)). Pay attention to naming specifications, tidy data and function limitations to ensure smooth secondary linkage effect.

It is not difficult to create a dependency drop-down list (that is, the secondary linkage drop-down menu) in Excel. The key is to understand the combination of data structures and functions. As long as you master a few basic skills, you can easily achieve the effect of automatically changing the content of another drop-down box after selecting one option.

Prepare the basic data structure

First, you need to organize your data source. For example, you have a relationship between "categorization" and "child items", like this:

• Fruits: apples, bananas, oranges
• Vegetables: tomatoes, cucumbers, carrots

It is recommended to organize these categories and subitems into two columns or multiple areas separately for convenience of subsequent references. for example:

 Column A: Category (fruits, vegetables)
Column B: Corresponding child items (apple, banana...)
Column C: Single fruit list Column D: Single vegetable list

Or the more recommended way is to manage it in the form of "table name naming range", so that subsequent quotations are clearer.

Use the "Data Verification" "INDIRECT" function to achieve linkage

This is the most common and easiest method, provided that the name of your child list is exactly the same as the main drop-down option.

1. Name each child list, such as "fruit" and "vegetable".
2. In the main drop-down box, use the normal "Data Verification" list, with the option "Fruits, Vegetables".
3. In the sub-drop-down box, use "Data Verification" and enter the formula:

    =INDIRECT(A1)

   Here A1 is the cell where the main drop-down box is located.

In this way, when you select "Fruit" in the main drop-down box, the sub-drop-down will automatically display the contents of the "Fruit" named area.

Note: The INDIRECT function depends on the cell content and the named area name, otherwise an error will be reported. If your main drop-down option is not a named region name, such as "fruit" instead of "fruit", then this method is not applicable.

Use "dynamic array" or "FILTER" functions (Excel 365 or 2021 and above)

If you are using Excel 365 or Excel 2021 and above, you can use the FILTER function to achieve more flexible linkage.

For example, you have a complete data table with the structure as follows:

You can enter the formula in the data validation in the sub-drop-down box:

 =UNIQUE(FILTER(sub-item column, category column = main drop-down cell))

This allows you to dynamically filter out the child items that meet the criteria and display them deduplicate them. This method is more flexible and does not rely on named regions.

Pay attention to small details

• The named area cannot have spaces or special characters , otherwise INDIRECT may not be recognized.
• The data should be organized to avoid blank or duplication, otherwise there will be problems with the drop-down box display.
• If you use FILTER, remember that the result is a dynamic array, which may affect the layout of other cells' contents.

Basically all is it, the operation is not complicated but the details are easy to ignore. As long as the data structure is clear and the correct function is used, the linkage drop-down menu can be easily made.

The above is the detailed content of How to create dependent dropdown lists in Excel. For more information, please follow other related articles on the PHP Chinese website!