Current location - Recipe Complete Network - Complete cookbook - How does excel set a multi-level drop-down menu?
How does excel set a multi-level drop-down menu?
With the data validation function, you can set a drop-down menu for cells. For example, if we set the data validity source of cell A4 as "mobile phone" or "laptop", click the drop-down button behind the cell, and these two products will be displayed in the open drop-down list.

Methods/steps

1

First, enter the first-level content (category), the second-level content (brand) and the third-level content (model) in the worksheet. In addition, it should be noted that the third-level title content at the oval box should be second-level content+first-level content;

Please click to enter a picture description.

2

Select the cell range A 1:B4, click the "Create from Selection" button in the formula tab, select only the "First Line" check box in the opened dialog box, and then click the "OK" button;

Please click to enter a picture description.

three

In the same way, select the cell range A6:F 1 1, click Create Based on Selection, select only the checkbox of the first line in the opened dialog box, and then click OK to define a new name;

Please click to enter a picture description.

four

Select the range of cells under the category (first-level content), click the data validity button in the data tab, and set the allowed sequence in the data validity dialog box that opens. The source is "=$A$ 1:$B$ 1" (the first step is to enter the first-level content.

Please click to enter a picture description.

five

Select the cell area under the brand (secondary content), set the data validity to "= indirect (A 14)", and click "OK" to prompt that "the source currently contains errors. Do you want to continue? " Click the "Yes" button;

Please click to enter a picture description.

six

In the same way, verify that the data (three-level content) in the unit area under the model is "= indirect (b14&; A 14) ",and then click ok;

Please click to enter a picture description.

seven

Personal recommendation

Through the above operations, the three-level drop-down menus can correspond to each other, that is, after selecting a certain category, you can select the corresponding brand and model under that category.

Please click to enter a picture description.