Computer and Software Support


How to validate data

You can have Excel validate the data that you enter. For example. If you want to verify that a person is over 18, you can use the verification feature. You set the range of dates. When the data is entered, if the date is outside the settings, you will either get a warning or it will not let you continue until you enter a correct date (depending on how you set it up) To access Data validation, do the following.

  1. Click on Data
  2. Click on Validation.
  3. Choose the settings that you want.
  4. Click on OK.

 

The different options under Validation criteria on allow is as follows

  • Any Value - default will not alert you for anything.
  • Whole number - The entry has to be a number. This will be good to use if you only want whole numbers entered in the cell.
  • Decimal - The entry has to be a decimal. This will be good to use if you only want decimals entered.
  • List - Is a list of text that you designate. Good for drop down options. You will have to type the text somewhere on the spreadsheet and then point to it in the validation. Then to use the cell, all you have to do is click on the drop down and select the option.
  • Date - You can designate the allowed dates.
  • Time - You can designate the allowed time.
  • Text Length - You can designate how long the text can be.
  • Custom - You can create your own.

 

On the data you can set the following

  • Between - tells the validation that the data can be between two settings.
  • Not Between - tells the validation that the data cannot be between two settings.
  • Equal to - to validate data to see if it is equal to something that you designate.
  • Not Equal to - to validate data to see if it is not equal to something that you designate.
  • Greater than - to validate data to see if it is greater than something you designate.
  • Less than - to validate data to see if it is less than something you designate.
  • Greater than or Equal to - to validate data to see if it is greater than or equal to something you designate.
  • Less than or Equal to - to validate data to see if it is less than or equal to something you designate.

On the input message, you can have a pop up window appear with instructions when a cell is selected. If you don't want this feature, then uncheck Show input message when cell is selected.

On the Error Alert, you can have the cell either warn the person of an error or condition, Stop the person from continuing till error is corrected, or give information. You can have it give a title and what the error message should say if an error happens. For example, if you have it where the person has to be over 18 years old to continue, you can have it not allow you to continue if the date indicates they are under 18 years old.