How to create a dropdownlist in excel?

The trick is to use data validation. Follow the step belows:

1.  Prepare simple data such as the following picture

2. By selecting cells A1: C5 determine the table name for the data, with the name ‘departments’.

3.  The next step to create a new table as shown in the picture, then select the range A10: A13. Select the menu Data - validation.

So the window will appear for data validation.

In the list of options ‘Allow’ select ‘list’, and for the source  type the following =$A$2:$A$5. And then click OK

• In  B10 cell use vlookup function to find the data of  Department Name base on ID Department, the function is like this.

=VLOOKUP(A10,Department,2,TRUE)

• In C10 cell use vlookup function to find the data of  Department Name base on ID Department, the function is like this.

=VLOOKUP(A10,Department,3,TRUE)

Drag your mouse from B10 to B13, as shown below. Do the same thing for C10 to C13.

4. As the final result, when you choose one of list for department ID, the departmen name and Amount of Employee will appear automatically base on the data in table department.

Good Luck…