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…
Under format menu, select conditional Formatting
When the first Conditional Formatting window appears, enter first condition. In this example Formula is =OR($C3=”",$C3=”0″, $C3=”Right”). Next, you need to select what formatting to apply when the condition is met. To do this, click on the Format button.
In the font tab you can set the font style and font color
For the cell background color or style you can choose Pattern tab. In order to understand the detail see the following picture.
Now when you return to the spreasheet, the conditional formatting will be applied. As you can see in the following sample.
).
