Question: I have a list of all the company’s employees in an Excel report and would like to assign the specific department they work in. Is there a way to do this without having to type the department next to each employee’s name?
Answer: Yes, by using Data Validation in Excel
Why: You use data validation to control the type of data or the values that users enter into a cell. For example, you may want to restrict data entry to a certain range of dates, limit choices by using a list
Applies To MS Excel 2003, 2007, 2010
1. First create a list in an Excel Worksheet for the Department Names
2. Select sheet 1 worksheet tab and enter as below
3. Select A3:A10 and enter Dept in the name box (You must press the enter key after typing Dept)
4. Select sheet 2 worksheet tab, and select the cells in the Department Column of the report (E5:E17)
5. Add the Data Validation to the selected cells. Refer to the screen shot below
6. The following screen will be displayed, select the criteria as shown below.
To insert the range name (Dept.) you can either type in =Dept or press F3 in the Source text box and then select Dept from the list of Named Ranges
7. Select OK and enter employees’ information. To choose the department, select the drop down arrow in column E. A list of departments will be displayed thus automating the entry of departments into the worksheet. The screen shot is displayed below
