Aug 212009
 

When you need to verify data that can go in a cell, Data validation is the function in Excel to use.

For example, to enter grades for students you may want to enter only A, B, C, D, and F. To see how to accomplish this in excel, enter data below

Student Number Grade
2001  
2002  
2003  
2004  
2005  
2006  

Now select Grade Column, and Go to Data -> Data Validation

Enter values as below

Settings

excel3 thumb Data Validation in Excel image

Input Message

excel4 thumb Data Validation in Excel image

Error Message

excel5 thumb Data Validation in Excel image

Now try to enter different values in Grades and see how Excel reacts.

excel6 thumb Data Validation in Excel image 

You have different options while setting validation

  • Restrict only whole number (Such as for Student role number).
  • Provide a list (List of grades).
  • Restrict number of character to enter (For example SSN will always be 9 digit).
  • Or you can provide a custom formula.

You also have the option of setting hard error, soft warning or just information.

Leave a Reply