It is important that the data you enter in spread sheet is accurate and error free. Microsoft Excel provides a Data validation tool to ensure that the entered data follows some pre-defined standards. For Example, suppose you are creating a result card of students and you know that no subject has total marks more than 100. This means that you can set a rule that no student can get marks more than 100 in any given subject. This can be done using Data Validation in excel. Every time you violate rule, a message will appear to stop you.
To set Data Validation in Excel do the following steps.
Create a result card for a student, select all cells in which validation rule is to be set and click on Data menu and again click on Validation.
In excel 2007 click on Data menu and click on Data Validation in Data Tools group.
A data validation Dialog box will appear as shown in figure below. Figure also shows the result card of student.
Now we have to set a validation rule that no student can get more than 100 marks. So in the settings tab of Data validation Dialog box, choose whole number in allow field and 0 to 100 range in Minimum and Maximum fields as shown in figure above.
If you want to show a message to user to help in data entry, you can do this in Input Message tab. This message appears when focus is on the cell.
In Error Alert tab, enter error message. When user enters value in the cell, excel validates this value against validation rule and displays error message if value does not stratify the rule. There are three Style available in Error Alert tab, Stop, Warning, Information. Stop do not allow user to navigate away from cell without entering correct value. Warning and Information just intimate user and focus is shifted to next cell.
To test this enter a 150 in cell c7 and press enter, an error message appears and will keep on displaying until you provide correct value or cancel as shown in figure below.
Note that the value in the cell C6 is violating validation rule. This is because validation rule was set after data entry in C6. Excels allows this but provides a way solve this problem.
In excel 2003, Click on Tools menu, point to Formula Auditing and click on Formula Auditing Tool Bar. On the tool bar, click on Circle Invalid data icon.
In excel 2007, click on Data tab and in Data Tools group click on Data validation Arrow and clink on Circle Invalid data.
As a result of this excel will encircle invalid data as shown in figure.
It is very useful study material for how to fix data validation excel. Thank you very much for this material…….