Thursday, December 26, 2013

How to validate data in Microsoft Excel 2007?

1 comment
In the previous article we learnt about Watch Window to view multiple cells at a time in Microsoft Excel 2007. In this tutorial we will see how we can define Data Validation Rules in Microsoft Excel 2007. It may happen sometimes that you require certain specific type of data/values to be entered in your excel sheet. We can define rules for such excel columns and as such Data Validation of excel comes into picture.

Features of Data Validation in Microsoft Excel 2007

1.       Allows only the entry of correct data as per the data validation rule.
2.       Restricts/Notifies invalid values.
3.       Provides option for data to be of type: Any Type, Whole Number, Decimal, Lists, Date, Time, Text length. We can also create a Custom Data Validation Rule using the Custom option in which data entry is based on some custom formula defined.

Types of Data Validation Styles/Severities in Microsoft Excel 2007

1.       Stop: Columns for which Data Validation Rule is defined as ‘Stop’, user is not allowed to enter any value other than range defined in the Data Validation rule. On submission of a wrong value, an error message is displayed to correct the value.

2.       Warning: Columns for which Data Validation Rule is defined as ‘Warning’, user is prompted whether he wants to continue with the wrong data or not. User has the choice to enter wrong data or modify it.

3.       Information: Columns for which Data Validation Rule is defined as ‘Information’, just an informative message is displayed for invalid data entry.

Steps to create Data Validation Rules in Microsoft Excel 2007

1.       Suppose we want data entry for three columns viz. Student Id, Marks and Comments in an excel sheet.
Student Id: Stop severity
Marks: Warning severity
Comments: Information severity

2.       Select the rows of Student Id column for which Data Validation Rule is to be defined. Click on the Data tab. Go to Data Tools in the panel and click on the Data Validation icon. From the drop down select Data_Validation...

3.       A Data Validation window will appear. In Settings section click on the Allow drop down and select the type of value. For Student Id we select whole number.

4.       In the Data drop down choose the type of operator.  Here we have selected ‘between ‘ operator. Set the Minimum and Maximum values to 100 and 1000 respectively.

5.       In order to show a message when cell is selected, go to Input Message section and check the check box ‘Show input message when cell is selected’. Set the Title and Input message as shown.

6.       Error severity is defined from Error Alert section. Check the checkbox ‘ Show error alert after invalid data is entered’.  Now select the Style/Severity from the drop down. As Student Id has Stop severity we will select Stop from the dropdown. Select the Title and Error message to be displayed in case of invalid data entry in Student Id column. Click on Ok button. Data Validation Rule is defined for Student Id column.

7.       Follow the similar steps for Marks column. Set the valid data as whole number between 0 to 100. Define Style/severity as Warning as shown . Click on Ok button. Data Validation Rule is defined for Marks column.



8.       Follow the similar steps for Comments column. Set the valid data as text length between 3 to 10. Define Style/severity as Information as shown. Click on Ok button. Data Validation Rule is defined for Comments column



9.       Now we can test the columns with valid/invalid values and check how the style/severity of the column makes a difference in data entry. Check out the snapshots.



1 comment:

  1. I really love reading and following your post as I find them extremely informative and interesting. This post is equally informative as well as interesting . Thank you for information you been putting on making your site such an interesting. I gave something for my information. Data entry

    ReplyDelete