Data Validation

This is one of those really neat and versatile features with a really vague name.  Validation can be used to ensure that only certain types of data are entered into a cell range, such as dates only, text, numbers between x and y, etc.  Validation can also be used to display user prompts, informing the user of the information required in a cell.  You can also create your own error messages if the users' input does not match the criteria for the cell (that's always the fun part!).

Adding Validation  

To add this feature to a cell:

  1. Select the cell you wish to add Data Validation to.


  2. Display the Data Ribbon and click on the Data Validation button
  3. . (If you click on the bottom of the Data Validation button, choose Data Validation from the list.) There are three tabs within this window.

  4. Specify the allowable data within the Settings tab. Theses settings allow you to specify the type of information that can be entered into the cell.
  1. Specify a message prompt to appear using options within the Input Message tab. When the user selects the associated cell(s) the message will appear. It's a good idea to advise the user of any restrictions placed on the cell, i.e. a message might read 'Enter the 6 character invoice number'.
    1. Enter an appropriate Title for the message.  The title appears in the title bar of the message window.
    2. Enter a message for the user in the Input message box.  This field would normally provide instruction for the user as to the type of information required and/or format to use.


  2. Specify an error message within the Error Alert tab. This message will only appear if the criteria you specified in the Settings tab are not met.
    1. Choose the Style of error: choosing Stop will prevent the user from entering anything other than a valid response.   Alternatively you can choose Warning or Information.
    2. Enter an appropriate Title for the error message.  The title appears in the title bar of the message window.
    3. Enter a message for the user in the Error message box.  A standard message might include Please click on Retry then read the instructions.

Limit Input To A List  

If desired, you can limit the user's selection in a cell to a specific list, such as a list of department names, inventory items, etc.

Circle Invalid Data

If you applied settings to data already present (typed), you can check for invalid data (data that does not meet your settings) using this feature. This is also useful if you did not choose the Stop action within the Error Alert tab and wish to view data that does not meet your criteria.

  1. Select any cell within the range that you applied validation to.


  2. Click on the bottom of the Data Validation button within the Data Ribbon.


  3. Choose Circle Invalid Data. This process may take several seconds. Data that does not meet the criteria specified in the settings tab will appear with a red circle.
  4. To remove the circles, repeat this process selecting Clear Validation Circles in step 3.

Additional References