Business Automation and Software Blog

Excel Tips and Tricks: Data Validation

Posted by Robert Baran on Fri, Oct 12, 2012 @ 01:42 PM

Excel Tips and Tricks / Business Reporting 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.

Excel tips and tricks

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).

Excel tips and tricks

5. Add the Data Validation to the selected cells. Refer to the screen shot below.

Excel tips and tricks

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.

Excel Tips and Tricks

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.

Excel Tips and Tricks

Contact PositiveVision ► ► for a Complimentary  Reporting Consultation

Topics: Business Reporting, Excel Tips and Tricks