How Excel makes a two-level drop-down menu

　How Excel makes a two-level drop-down menu

As shown in the figure, enter the finance department in the A2 Cell, B2 name drop-down menu only shows the employees under the Finance Department, if the A2 into the administrative department, the B2 cell drop-down menu shows only the employee name below the administrative department.

Step 1: First select the data area where we need to generate the level two menu, hold down the "ctrl+g" or "F5" key on the keyboard, and click "Locate conditions" in the pop-up window.

Step 2: In the positioning criteria, select the constant constant to locate valid data, because there are many invalid data in the range we select, that is, blank cells

When you click OK, you will see that the blank cells have been excluded.

Step 3: Next we want to give the selected data region to define the name, to facilitate our reference later, select the Formula menu to find "create according to the selection" check the first line with the first line of the department to define the name