DATA VALIDATION IN MS-EXCEL
DATA VALIDATION IN MS-EXCEL
YOUTUBE LINK: DATA VALIDATON IN MS-EXCEL: https://youtu.be/pf7jAcKjIiU
Data validation comes in Data Menu of Excel. Data validation is simply used to find the mistake which may occur due to typing. Here I will explain this with an example.
In the sheet given below there are marks of few students
Here you can see in the above table that there are marks of 3 subjects and by mistake marks of Hindi (Cell E5) which has to be 45 has been fed as 450. As volume of data is less it can be easily identified but in case of larger volume it won't be easy to find such mistakes.
Here the need of validation appears. There are two methods one that you stop the person from wrong feeding as soon as he enters the data or check all at once if not applied earlier.
As in above table we can apply validation on the Marks cell or Total cell. When we click on Data Validation its window appears as given below:
Here under Validation Criteria we can set the data type under allow and when we select it than we get Data and minimum or maximum column to enter values. This totally depends upon the value entered in Allow.
Now as for the above example if we have to put condition on marks than we have to choose whole number or decimal number.
Whole number will not allow decimal values to feed. So if there are values with decimal numbers than we should choose decimal.
So,
Whole numbers are for numbers without decimal numbers. It will only allow 1 not 1.5
e.g. validation data to be fed as given below:
allow : Whole number
data : between
minimum: 1
maximum: 100
value to be entered in cell should be a whole number
Decimal number will allow both 1 and 1.5
e.g. validation data to be fed as given below:
allow : Decimal
data : between
minimum: 1
maximum: 100
value entered in cell can be a whole number or a decimal number between 1 to 100
List can be a set of cells in the same or other sheet which will allow to select the data from the given list.
e.g. validation data to be fed as given below:
allow : List
data : -
Source: cell range with data
while feeding data the list of values will appear in cell.
Date will allow to keep an eye on entered date. Keeping in mind that range should be given in date format.
e.g. validation data to be fed as given below:
allow : Date
data : between
start date: 01-01-2021
end date: 31-01-2021
will allow to enter dates between 1st January 2021 to 31st January 2021 only.
Time will allow to keep an eye on entered time. Keeping in mind that range should be given in time format.
e.g. validation data to be fed as given below:
allow : Time
data : between
start time: 1:00:00 am
end time: 3:00:00 am
will allow to enter time between 1 am and 3 am only.
Text length will allow to set the length for number of letters allowed in a cell.
e.g. validation data to be fed as given below:
allow : Text length
data : between
minimum: 1
maximum: 10
will allow to enter the text with minimum 1 letter and maximum 10 letters. This also allows numbers and symbols to enter.
other criteria can also be set under data according to the allow value.
Input Message
If we deselect Show input message when cell is selected message will not appear while selecting cell.
Error Alert
In this we can set Alert message which appears on screen when one enters wrong data in the cell.
Message appears as given below:
If we deselect Show error alert after invalid data is entered, than alert message will not appear on screen even after the wrong input is done.
OTHER OPTIONS
Circle Invalid Data
This is used when we have not applied alert messages to appear on screen to see which data have been entered wrong.
When we click on it, screen appears as below:
When we save file or when we click on Clear Validation Circles, these circles disappear.
Hope the information in this blog is useful.
Please do leave a comment/query for this blog.
इस प्रकार की कम्प्यूटर सम्बंधित जानकारी के लिए हमारी वेबसाइट पर ब्लॉग लिंक पर क्लिक करें।
द्वारा
कुमार वैभव
डायरेक्टर
वैभव कंप्यूटर सेंटर एवं कंसल्टेंसी
वैभव सदन, देवकाली रोड,
फैज़ाबाद, अयोध्या।
Center website: www.vccfzd1.weebly.com
Facebook Page: https://www.facebook.com/vccfzd
Comments
Post a Comment