MS Excel - Data Validation

hi in this video we'll be looking for data validation data validation for whole numbers text we'll be looking for dates and drop-down lists let's see those one by one right now I I have a skarssen my cell number c1 in this cell I can type any value I can type text I can type number I can type error messages function formula etc so if I want to restrict my cell for some data let's say for whole numbers I want only whole numbers to be inserted in this column so I've selected and I will put some condition over here that condition is nothing but data validation so if you want to go for data validation you have to go to the data tab remember I have selected the column and I will go to the data tab in the data tab if you see there's a data validation in this data validation I'll click on this button right now by default it says in the Settings tab it allows any value as I said I can type any value in the cell a number a text error message a function or date everything I can type but right now my expectation is I want to type only whole numbers and we'll give some criteria let's say I want whole numbers between 10 200 so let's see now I'll ask him allow only whole numbers I love only whole numbers between so in I want only in between I can say greater than less than equal to but keep it in between and what should be in between I'll say numbers should be in between 10 and the number should be in between 100 so the minimum value is 10 and the maximum is 100 and once I click on OK the data validation setting is applied on this region so let's see I'll click on OK now when you are giving this excel sheet to some other person to enter data so by mistake now it's entering his numbers let's say he's entering 11 he's entering 1525 by mistake if he inserts less than 10 let's say he inserts nine when he press Enter he'll get a my error message let's see what is that error message the error message says the value doesn't match the data validation restriction I defined for this cell so what is the restriction that is 10 200 so it will allow only values which are between 10 to that is the data validation similarly now if I go for text length what is text length this I want to insert text length that means I want to type any text the length should be between three to five characters so same way like I have applied data validation for here I'll do the same for this column now I'll go to this data tab and I'll select this data validation when you open this data validation you can see this dialog box it will say it will allow only a text length between let's say I'll keep a criteria three to five characters so I will type here three to five characters and click on OK now when I keep on typing on this cell in this text like any any text which is three characters like say a B C enter d EF enter if I type let's say more than five characters ABCDE F and when I press enter again the error message is displayed let's see that it is the same error message which is shown the value doesn't match the validation restrictions defined for this cell so I'll click on retry and it will give me one more chance to again try the again so I'll just type here again and enter so now it is accepting so this was for whole numbers second is for text and the third thing is purposely I'm taking for day in this cell number in this column I want to insert only dates so what is that dates let's say I'll take an example of 1 1 2016 2 1 1 2017 ok so I'll select this column so in this date column I'll just take this select this data in this sorry in this column and what I'll do is I will put a data validation so I'll go to this data validation and in this dialog box I'll select here there are many options whole numbers decimals list and date I'll go for date now and I want a date between 1 1 2016 2 1 1 2017 1 1 2016 1 1 2017 so once I click on ok any date which goes lesser than that and more than this 2017 it will it should throw an error message so what I'll do is I'll type here a date let's see the date would be 1/2 2016 that's valid if I put today's date it is still valid but if I put 2 2 2018 and the moment I press Enter I should get an error message yes I got a error message the message is same you should apply the value doesn't match the data validation restrictions applied for the cell so again retry it gives me one more chance let's see if I say 16 2016 enter so now this is accepted so this way you can apply restrictions to a cell for whole numbers you can give for a text length and for a day let's see something more interesting if you go for some websites to book up let's say flight or a book or train or a bus you can see there's a drop-down list that drop-down list will give you options to select the bus for destinations and the source in this place I have I want a drop-down list to be created what does that drop-down list should create containers it should contain names of the countries so what I'll do is I'll select this column and this entire column a list will be created so how do we go for it I'll go to this data tab select this data validation it should allow only lists so you can see the option is given as 4th option list so I can select this option as list here and it will say the list is will be created but what do you want in that list so I'll say I want 4 names of the countries so I'll start typing the name of the countries here I'll type here India Japan China Singapore that's it so F type for different countries name and I'll click on OK I hope the spelling is correct yesterday our Spelling's are correct and I'll click on OK so once I press ok if you see there's a drop-down list which is appearing which is empty right now I will select here and select India I'll click in this cell and I'll select some other country China I'll click here I'll select something as Japan and so on but if I click here I will not get this list the reason is the validation is only applied in this column right so in this way you can apply our data drop-down list but imagine if you have a data which is 500 different cities or 500 different countries let's say for example any data how do you create it you cannot keep on typing the names separated by a comma you have to use some other method so what is that method is fetching data from the sheep given itself so I'll just select this column in this data and in this place I'll put a data validation I'll select the option as list and if you see I I typed the names of the countries separated by comma but now I'll just click on this button and I will fetch this names of the cities names of the countries yeah these are the names of the countries and I'll click on ok so the names of the countries are Thailand Malaysia China and Singapore and the cell numbers they have captured is that is h v 2h 8 they have captured so I'll click on OK now if you see this list which is given here is been captured and stored here so this way if you have lengthy data you can just capture that from the sheet itself and you can select and use it so I'll select and use it and I'll select something else Singapore selected but now this was the advantages of list I will tell you some disadvantages of list if the data from vary of captured if it is deleted by mistake if I delete it by mistake what is it what is the impact on the list so I will click on this click on the drop down yeah you got it right so Thailand there's a blank space and China and Singapore so what is the method by which you can save the data so there's a small trick you write the data in some of the sheet hide that sheet or lock that sheet so there so that no other persons goes and troubles their data and this will be saved with you forever right so this is a disadvantage or limitations now lastly I just wanted to tell you a few things in this that is the input message and error messages if I type some number let's say if I type here 900 and press Enter I'm not able to understand this error message it should be exact accurate so that user should be able to understand so that time I can create my own error messages and displayed so what is that error message I'll show you here first of all I'll just select this entire column where data validation is applied I'll go to data validation and if you see there are two tabs given here one is error alert and input message let's go with the error alert now there's a title and error messages so what is the message you want to display when you get an error message what exactly you want to display so I'll type here an error message that is the title is not valid yeah please enters number between 10 200 and I'll click on ok so now if I try to enter any invalid number here let's say if I enter 900 the same number which I've entered when I press enter you see the message which is given here this is not valid if you remember the title which have caved on the top next one is please enter numbers between 10 200 so this is the message I have caught it so this message you can customize as per your requirement so it will be user friendly and the person can easily understand similarly if I if I have something input messages let's say if I keep my cursor I can only understand when I enter sup invalid data but what if I keep my cursor the user should be able to understand so that is known as input message so again I will select this column and I'll go to data validation go to input message so in this input message I will type something here yeah so kindly enter numbers between 10 200 and this is just an information so I'll just click on ok so what is the benefit is if you keep your cursor at any place so you can see that's the information which is displayed and that is very useful so the person who's using this excel sheet will understand where exactly I have to write a data so similarly if you keep your cursor it will just tell you what exactly the data has to be written and this is for information message similarly we have seen the error message alerts also so I think that's all for this video we have seen the data validation whole numbers text dates and different dobbed only drop-down list that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 761,033

Rating: 4.9260378 out of 5

Keywords: Data Validation in Excel, How To Data Validation in Excel, Data Validation in MS Excel ms excel - Data Validation, Data Validation, excel data validation, data validation list, Data Validation in MS Excel, ms excel - Data Validation

Id: nMxl1_NAcxc

Length: 11min 1sec (661 seconds)

Published: Mon Jan 15 2018