Data Validation in Microsoft Excel

 

We are now here writing about an important function of MS Excel called Data Validation. Data Validation is set to assign rules and limitations to users according to requirement. For instance, we have set the fees to receive at least upto 1400 and above, then set rules like this requirement through data validation or we have to set the rule to admit the students only upto eleven years and above. Data validation plays an important role in our office routine work and limits the chances of typing errors. We can also set dropdown list through Data validation which help in data filling speedily.

How to set Data Validation in Microsoft Excel

Click on Data Tab in where we can find Data Validation icon. Click on data validation icon which will open Data Validation window.

There are many arguments to be set in Data validation. Some these data validation are given in succeeding paras. 

Here we can see the first tab “Settings” in Data Validation Window where, we have to select the Validation criteria dropdown list. We will see following options in this dropdown list:-

Any Value

Whole Number

Decimal    

List

Date

Time

Text Length

Custom

Any Value.          Any value is shown by default, which means that no validation rule is applied to the data.

Whole Number.            Whole Number validation is applied to the data having digits. On selecting Whole Number option, following options in Data label, from where we may select our desired number and type validating rule whether less or greater, which apply validation to the  data:-

Between

Not between

Equal to

No equal to

Greater than

Less than

Greater than or equal to

Less than or equal to

After filing all fields we will press “OK” which will apply validation rule in the data.

Decimal Number.         Decimal Option is like Whole Number option and functions both equally.

List.   List Option is used to insert the data as per list given in the validation rule. Suppose we admit students from 5^th Class to 10^th Class then we shall validate data to enter from 5^th to 10^th. After selecting List option, we will see another range to select list. Where we can type list of or give range of list where,  classes are typed. We can two button check button near to list option selected i.e. Ignore Blank and in-cell dropdown. Ignore Blank selecting will accept blank data otherwise it will not accept blank data. In-cell dropdown check will show dropdown button in each cell where validation data has been applied.

Date. Date validation rules are applied to data filled with date’s formats. Suppose we have to admit the student who have 11 years age and above.

Time. Time validation is applied to use validate data having time. Suppose we have to set the validation rule for teacher to be present between 9.00 AM to 3.00 PM in attendance sheet of teachers. Since, we will not be able to insert time before 9.00 AM or after 3.00 PM.

Text Length.       Text Length validation rule is set to use the length of texts to be inserted in data. Suppose, Some persons type Class with the options Sixth or 6^th, but these option vary the length of text which we can set it by giving the range.

Custom.     Custom validation is used to validate data through formulas. Suppose we set validation rule in fees amount to set the validation rule to insert the amount equal to 1500 or above. Then we shall type formula “=I2:I121>=1500”. Which will give us limitations to type the amount between 1500 and above.

Input Message.  We can see a Tab Validation Window as Input Message, where we can set to give guidance to users to insert validated data. After setting Input message, we will see input message in every cell.

Error Alert. We can also set error alert to give alerts during inserting data. We can set this alert with three options i.e Stop, Warning and Information. On setting Stop alert, the excel will not accept the data except inserting according to validation. Whereas Warning and Information will allow data insert after giving error alert.

Circling invalid Data. After setting validation rules, we can analyze data after circling automatically through press the button shown below the Data validation at Data Tab. this command will circle all invalid data within the worksheet.  Subsequently we can clear these circles on clicking the button circle button in Data Validation icon.