Using Data Validation - Exercise

Contact Us or call 1-877-932-8228
Using Data Validation - Exercise

Using Data Validation

Duration: 15 to 25 minutes.

In this exercise, you will practice using Data Validation to restrict the data that can be entered in specific cells in a worksheet.

  1. Open Data Validation.xlsx from your Excel2013.3/Exercises folder.
  2. Use the Data Validation command to restrict the data that can be entered into cells C2:C17 to the list of salespeople on the sheet named "Salespeople".
  3. Use Data Validation to restrict the data that can be entered into cells D2:D17 to a date in 2012.
  4. Use Data Validation to restrict the data that can be entered into cells E2:E17 to the list of territories on the sheet named "Territories". Enter an Input Message and an Error Alert to help the person entering data.

Solution:

  1. To restrict the data that can be entered into cells C2:C17 to the list of salespeople on the sheet named "Salespeople":
    1. Select cells C2:C17.
    2. On the DATA tab, in the Data Tools group, click the Data Validation command:Data Validation Command
    3. Click Data Validation:Data Validation
    4. In the Data Validation dialog box, on the Settings tab, under Allow, select List:List
    5. Click the red arrow to the right of the Source data entry box to collapse the Data Validation dialog box:Data Validation Dialog Box
    6. Navigate to the sheet named "Salespeople", select cells A2:A6, and then click the red arrow at the right of the collapsed Data Validation dialog box to restore it:Restore
    7. Back in the main Data Validation dialog box, click OK:Click OK
    8. Note that selecting any of cells C2:C17 in the sheet named "Sales" opens a drop-down list from which you can select one of the allowed entries:Drop-Down List
  2. To restrict the data that can be entered into cells D2:D17 to a date in 2012:
    1. Select cells D2:D17.
    2. On the Data tab, in the Data Tools group, click the Data Validation command:Data Validation Command
    3. Click Data Validation:Data Validation
    4. In the Data Validation dialog box, on the Settings tab, under Allow, select Date:Date
    5. Under Data, select between. Under Start date, enter "1/1/12" and under End date, enter "12/31/12". Click OK:Enter Date
    6. Only dates in 2012 are allowed in cells D2:D17 now. Entering another date will result in an error message:Error Message
  3. To restrict the data that can be entered into cells E2:E17 to the list of territories on the sheet named "Territories", and to enter an Input Message and an Error Alert to help the person entering data:
    1. Select cells E2:E17.
    2. On the Data tab, in the Data Tools group, click the Data Validation command:Data Validation Command
    3. Click Data Validation:Data Validation
    4. In the Data Validation dialog box, on the Settings tab, under Allow, select List:List
    5. Click the red arrow to the right of the Source data entry box to collapse the Data Validation dialog box:Data Validation Dialog Box
    6. Navigate to the sheet named "Territories" and select cells A2:A6:Select Cells
    7. Click the red arrow at the right of the Data Validation dialog box to restore it:Restore
    8. Select the Input Message tab and enter a Title and an Input message:Input Message
    9. Select the Error Alert tab, enter a Title and an Error message, and then click OK:Error Alert
    10. Note that selecting any of the cells E2:E17 in the sheet named "Sales" opens a drop-down list from which you can select one of the allowed entries. In addition, your Input Message is displayed and entering an invalid entry results in your Error Message popping up:Error Message
Next