Lessons

Lesson Info

1:00 pm - Text to Columns, Data Validation

We're gonna go a little bit more advanced with a mark cell skills in this segment, we're gonna be going into something called data validation. We're all so we're going into text two columns, I explained earlier data validation going a lot, a lot of different directions, but we can create dropdown list. We can also do data validation in terms of numbers and dates where we actually only want certain types of dates when we're putting our information in there, therefore keeping it valid, hence the name. So we're going exploring all those summer things and again, both on the mac and the pc side, you're going to see that there's going to have definitely a lot of lot of utility to that, so certainly keep the questions coming in. We'll have questions here from our studio students, and we'll just go ahead and jump on in. So the file that we're going to be working with is going to be this day three, session three, text two columns, all right? So we're going to be working with him in data validat...

ion, drop downs, all right? So you're going to see this is actually very, very cool, all the stuff that we can do, so we'll start off on the mac side and just go ahead and open up to this here file. Now, the first thing I'm going to show you is howto work with this text two columns, aspect of things, okay? Because what I have here is ah, list of names now problematic to this list of names is if I want to alphabetize this by last name right now, it is not possible to do that because david davis, lee and malcolm and all these guys here these are all put together as one full name, a full string of text, but not a something that I can segment out as an individual column to be able to sort by that column. So what I'm gonna do is I'm going to do a very, very sophisticated yet simple tool to be able to divide these names up just by putting a little crowbar, if you will, in between each name and then it's going to pop it over to another column. All right, so really, really slick and how to do that, okay? And we're going to get to that tool while going up to data and then text a column, and if you're on the pc side, it should be the same, but of course we're going to do a repeat on the pc side as well, all right now, what's very important to do before we actually jump right into the actual icon and the actual command is we need to create a space for it that's what's very important because basically, if we do the text of columns separation right now, it's going to override what's currently here, so we don't actually want to do that because if I separated out is going to pop over and it's going to replace what's in column b right now, so what I need to do is put in another column, essentially all right, so if you recall from yesterday, we did some work on this, we certainly go right over here to column b and we right click and just choose insert, okay? So hopeful that should be a review for most of you and bam. Now, I've got a space for, you know, it's, just like we're building out our homes a little bit, we need to have a little bit extra space out there so we can bring in a new baby or whatever going to do we have new space here, too, basically put in more things into our happy little home here, okay, so now what we're going to do is we're going to highlight all the content that we want to separate out, all right, so how do we do that? Very simple? We just go in highlighting the content, so there we are just nice and easy highlight and then all we do now is we're gonna go through this little wizard, so excel is going to take us to this very helpful, helpful little wizard that's going to allow us to just divide everything up. We're going to tell excel what we want to divide up and it's gonna understand, okay, this is where I should put the divider, and then this is how I should bring it over to the next column. So it's, really magical just think about how much time this saves when you're doing something like this, rather than having to cut and paste or retyped let's. Go ahead and do that. So click on texted columns and this little handy dandy convert texted columns wizard pops up, and we're on step one of three, all right, and it's basically telling me here, this is essentially what you want to choose that it's the limited characters such as commas or tabs separate each field or, in this case, spaces so just know that you are working with something else that does have some of these things built into it, like commas or tabs or whatever it's going to recognize that because if it comes in, as like you know lee comma davis or something like that you can still do it that's what's known as d ltd all right, so I'm going to keep it as this right? We're gonna keep it just like that and then I'm going to say next you'll notice here what are my d limiters it's asking me to choose what's my deal emitter in this case davis space lee is the keeps going unchecked this tab don't need that I had space and look about magic it actually brings that up for me to just put that crowbar right between the first and the last name, which is pretty pretty amazing I know I agree all right? So we have that they're going to be separating it out and we're getting a preview of the magic that we're about to behold all right? So we go from here and then we were on our wizard step two of three and we just keep on keeping on we do that and then okay, that looks great here's confirmation of it it looks really good and all we do is click finish and then bam just like that, we just saved hours and hours of time do that so it is easy is that that's texted columns all right? And if you had let's say middle initials or something like that if you had mr and mrs and all that kind of stuff you would just add on mohr columns is what she would do because you basically need to allow for a space for each individual. Uh, new element that's going to bring in because you want to separate out each salutation. First name, middle name, last name, and you won't have junior senior all that kind of stuff. So you may have to do this in sort of a repeated form where you're doing this one, this one. This one. Because you have five elements. I need to be separated out. So the key is to insert those columns ahead of time to make sure that you have all of your kind of real estate, if you will all laid out before you execute. Okay, now, I can usually just go over here and say first name hoops first, first and last. Okay. And then you can see now I'll be able to sort this out. No problem, because that needs air. All separated. Okay, so, really, really straightforward. Let me just jump over here too. The pc side? Yes. You create space for it on b. Did you make a one space for it to go? Okay. So question was that I created created space for b, so I basically created a new column. Yes so go ahead and do it again on the mack on the pc side that's what's nice about actually doing stuff back and forth between the two platform is that we get to do a little review cave so even though it's going to be in a different platform there was a lot of similarity in this. Ok, so thanks for asking that question so let me come back to my documents, okay? And here we are. So how am I going to do that? I'm just gonna go ahead and right click right on column b and choose insert we have to have this space here I'm going to show you actually just in case you can kind of get that kind of like feeling of sort of like you know you like you put your hand on the on the fire so you remember never again to do that I'm gonna actually show you what's gonna happen when you do with the incorrect way to make sure that you kind of have that feeling so it doesn't happen again. Okay, so um I'm going to now do this I'm gonna highlight everything all right? So let me go ahead and do it against the data you see very similar data and then text the columns and then so you have the same exact thing de limited and then I have all my names here and everything like that I'm gonna click on next and see if the same exact thing where I have tab when that simon check that and I'm going to say it's a space see that there and it just puts that well kro bar in there for me okay then I'm good to go and I'm going to say next I'm gonna say finish now watch what happens here because I don't have an empty column I'm seeing this do you want to replace the contents of the destination cell all right I'm just going to say okay because I don't know how to read and I just say okay because that's what we do yes I agree with you just go ahead you know I'm busy and lazy like oh now what just happened there okay so we want to make sure that you know we kind of read what is saying to us but I do want to kind of step you through the process of here what do we do now and of course we could just undo all right what's my own do either control z or my friendly little backwards arrow there okay, so let's do that again right click insert okay and I have a little space for it and remember again if you have more than one name meaning mr first name middle name last name you want to make sure that you have enough space for it was here inserting the columns make sure you insert enough columns went in doubt always in certain mohr columns you better tohave mohr empty columns and replace columns that are you have some valuable data in there. Okay, so once again I go to data I go to text two columns you'll actually see by the way there's always little hints for you like texted columns gives you this amazing, amazing explanation for what it does separates the contents of one excel cell into separate columns for example you can create you can separate a column of full names into separate first and last names ok, what a coincidence that this happened to me what we're doing right now. Okay, so it's a really perfect example of how to do that but you're thinking about why would we want to do that? Because we might want to sort it out by alphabetical e or whatever by that particular thing that we cannot do when they're all brought together in one cell. Okay, so let's do that again. Good thing my wizard, you should have this down by now and I was going to say space remembers from before fantastic there's my crowbar looks great click next watch the magic happen bam just like that you could have fifty you could have one hundred you could have a thousand it's gonna be that easy to be able to do that all right, so really joe was a huge huge time saver on dh yes, so use it and have fun with it, okay? So that's text two columns all right and remember that's under data and text two columns right there she's going to see that's gonna be the same thing on both the mac and the pc side. All right, now what we're gonna do is we're gonna work on something called data validation alright, so data validation understand why they call it date a validation if you're putting in certain kinds of data, you want to make sure that the data that you want to put in is valid, meaning that you don't want to put in stuff that has nothing to do with what you're putting into your putting in states you don't want people to actually put in ah state that is not part of your sort of expect room of choices. So you want to say ok on lee put in these or we're going to do something for dates also you only want to make sure people are putting in the date entered from this time to this time so you can keep that as a valid setting all right to make it so it's only going to be those types of data on dh you can also do it as drop down menus and that's really cool because we can actually make it so the choices are actually right there as we do, it is when I click on the drop down, those options are going to be there for me to choose from since there's really good if you're working with somebody else, who's doing the data for you, who's, actually doing the data entry, and they don't really know the data so well, but they know just to click on the drop down to make it nice and easy for them, all right? So we're gonna do a variety of different types of data validation, all right? And we'll start off, actually with the drop down. So I think those are pretty cool on dh that's, goingto that's going to really help us out, and we're going to be doing data validation dropped down in terms of either typing the data ourselves, but we also might have the data already someplace else that we're just going to want to import directly. So there's a lot of different things you can do so let's, take a look at what this might be here, okay, so we're going to dio region in this case, and the region is going to be, you know, east, it's, west, north, south, and maybe central, whatever it's going to be okay, and we want to be ableto have it all right here, as drop down many for us to work with. All right? So the first thing you have to do is select the area that you want tohave the drop down menus in okay, so we basically have to highlight if they're highlighting empty cells we are going to have the data validation information baked into those cells all right, so it's very important to do that first because otherwise that's not gonna apply it, but only one of those cells and if you ever do want to change these cells, you will know which ones to go to a cz well to be able to change it when you go into the data validation dialog box. Okay, so how do we do that? We're gonna go to data, and if you're on the mac side, it should be the same thing, but of course I will repeat it on the mac side as well. I'm gonna go over here to date a validation, all right? And it tells me here prevent invalid data from being entered into a cell. Okay? And then it also says for example, you could reject invalidates or numbers greater than a thousand cases giving an example of like what this could be we're going to be doing certain things like that I'm gonna be using a zip code example all right, you can also force input go away all right, you can also force input to be chosen from a dropdown list of values you specify okay, so very, very cool that you can do all these things and it's just kind of like when you're on the internet and you see like dropdown list you can create the same type of thing with an excel tohave a dropdown list of whatever you want and also just kind of created so you have certain criteria that can only be put into these cells okay, so I have everything selected and I'm gonna go now to my data validation now you'll notice here I have three tabs I have my settings I had my input message and I have my terror alert all right these air kind of optional but I'm actually going to show you howto work with ease but they're not absolutely necessary the settings is what we're going to be exploring here you see it says validation criteria what is our criteria for being for being known as valid data? Okay, so we're gonna have here whole number decimal list date time, text land custom so this is going to really depend on what you're kind of working with but we're going to be working with first list but later on we're gonna come to a whole number and we're gonna come to date as well I can't possibly even text length if you want experiment with that too all right, so let's just go to a list now first so I click on list because we're doing a dropdown list choose list they'll notice here is in cell drop down it's already they're for us so it's really great how it actually will allow us to do that if we choose to not do that it's going to be a different style so well actually try a little of both because you might have a different preference in terms of what you want to work with so it could be dropped and many or could be another kind of list that just kind of like hovers above and then you'll be able to choose which one you want to space on your style or based on the data what state itself so now notice after I choose list I have the word source down there okay, so what does that mean essentially we're talking about what is the content of what you want to put in there so what's going to be the contents of my drop down all right? So basically all I'm doing is typing in what am I dropped down components going to be all right so in this case is going to be east west, north, south and central okay, so I'm just gonna go ahead and type them in and what you want to do is you want to separate each out by a comma east, west, north, south central they'll notice this we're going to see this in just a little bit okay, so we're going to be working with importing stuff as well. All right, so that's all I do at this point okay, now I'm going to show you this input message also before I click okay, we're going to go into the input message and the air alert. All right, so the input message this is really good if you're working with people, we're not familiar with the data, all right? Or if you need a reminder of some kind, the input messages like a little bit of a hint it's a little bit of a tip to tell you, hey, this is what you need to do here, because, again, we're looking for valid data, so we want to give people enough information so they know this is what they should do in this case it's really just going to be hey, can you just click from the drop down? Okay, but it also might be like, okay on lee chews on ly type in more than, you know, two hundred characters or something like that or it could be only choose these dates are something like that, right? So we'll be able to do a number of these different things, all right, so you'll see here I have input message, and it says show input message when cell is selected, we're gonna keep that all right, so we're just going to go ahead and put in a title and an input message. All right, so our title is going to just be region all right, it's going to say, please choose from drop down menu and pick a region now, after that's done, we can also do an air alert, okay? And this is where you get tau sort of play god, so to speak, and you could kind of slap somebody on the wrist if they don't do what you want to dio can you could have some fun with this or you can maintain a professional decorum like I'd encourage you to do all right, and you can actually just pipe type in whatever you want, basically, if they don't do it the proper way, you can say you can remind them, you know, little little gentle coaxing to say, hey, if you please, wouldn't mind doing what the input message told you to do, okay, so we could go ahead and ends just put in right here, okay? So reminder please choose from drop down less, and it will not actually allow you to do anything unless you actually choose unless you do what is in there. I know that you also have a few other options here. We have warning information and there's a little bit less severe. Like information is just information warning. They're going to just give you a warning about it. So if you don't if you don't comply, they'll still let you go. All right? We're gonna keep it. It stopped for right now, okay? And let's watch the magic happens. All we're gonna do now is click okay, and then notice right away because this is selected I'm gonna click away from it. You watch how it disappears when I click on this notice it tells me here region please choose from drop down menu and pick a region and watch this like magic. All the stuff that I put in there is actually right there. So just like we've ever seen on the internet like magic with just a couple of clicks, we can create this kind of interactivity, so it looks really cool. Number one number two it allows us to get valid data. We're not gonna get anything more, anything less so I come over here. I'm going to say they're from the east. Go there. Alright, go the next one. Okay and this this thing will start to bug you after a while I can guarantee it so I would not recommend actually doing this for every single one to tell you the truth may be on the first few ones you could do that but this thing kind of gets in the way so I'm not a big fan of it there so I can move it away let's go ahead and do this come overto there that actually does help but not everybody's going to know to do that and once you've been once you've been sort of reminded do it twice hopefully you remember for the rest of them right you just have that there but just in case you wanted that I want to show you how that's done okay, so you can do that now why is this data validation? How is this okay? This is cool. The drop down is really cool just in and of itself but there's going to be people in there putting in stuff that's like okay, well, you know what I'm I know my territory and I'm in the southwest territory see what happens now, huh? What is that? Oh reminder please choose from dropdown list oh that's right ok, good, good oh yeah there is no south course region so now I'm reminded of this okay? I'm keeping my data valid and that's what this is about it's not on ly making sure that people just choose from dropped in with and having a dropdown list but it's also giving us the ability to make sure that we don't get sort of false data and there okay so when you're working with these things you want to make sure you get what you want to get all right so I'm going to let me to retry and this time I'm just going to choose from the dropdown list of to have actually delete this all right and I was now going to choose okay we're in the south okay you can see that I can go from here nice and happy and if you want to type it in you could also type it in two and now that it's already in there you've probably seen it on this on excel on some levels like if something has already been in there I can type in and for north and it actually just shows right up so after a while you might not actually need to use it because it's already been in there all right just type in west and the east type in and for nor that's for south you see that and were lucky enough that they don't know any of them start with the same letter so that could be really, really helpful okay? And one example that I've actually seen this use very well and it's just sort of like putting putting planting a little seed is if you ever working with something called v look up maybe some of you have heard of that before he look up is really nice because it actually allows you to look up a variety of different data that's not on the on the current place where you're doing the look up but if you know you're looking up like a whole let's say a whole series of employee numbers you can have those has dropped downs within that same look of value and have them look them up all right? So it might be something like east west, north and south, but you're basically alternating between all those to do the look up so we won't be getting into b look up right now in this class but some of you who are a little more advanced might know the look up for one a little bit more and you can see how having a dropdown list integrating with your v look up could actually help out okay, so let's now move on teo I'm going to do something a little bit different now but still as a drop down in you, so I have let me just go back to my auto fills if you'll remember from last class I did an auto fill lists and I'm goingto go into my auto listening to see which ones I created I had okay, I just had colors last time so let's just go ahead and we'll actually do departments because I want to actually use it where I can import my different departments into here so I don't actually have to type them out because a lot of times you are going to be in a position where you are just reusing content that's already there and rather than having to type it in again we're just going to simply just import it all right? So what are we talking about here? So I'm just going to go ahead and just say, um some departments ok human resource is management's manage meant t admin sales marketing okay just like that so I have all these that's my whole company these are all the departments I want to be working with, so what I'm gonna do is I'm going to keep this here and this could really be anywhere that you have this you could this could be like in some secret segment someplace else or could already be in the list already and we're just going to essentially just kind of suck this up so it gets put into the dialogue box that we're going to be working with with the data validation so I want to choose what department I want actually just have it seems to me the same exact thing or I have my department just like that at the highlight, everything first it's going to be the thing that it's not the hardest part of it, but it's going to be the easiest thing to forget. So we always want to make sure that what other part we want to do? I want to make sure that we do highlight the area that we're going to be putting in the data validation let's go out and do that. You mean to say data validation, and we're back to this again when you go to settings and then here's my allow once again, I'm going to say, allow and the son I'm going to say list, but this time I'm going to just not do in cell drop down so you can see the difference of how they look. And again, it's going to be up to you in terms of what your kind of which what's your type of interactive preference would be going to go ahead and go to source this time instead of typing it in like it did before I'm gonna click on this guy right there and that's going to allow me to then choose from a list that already exists on importing the list into this box right here. Someone go and click on that and this gets collapse we may have seen this with other things too or certain formulas and were selecting other things that's essentially is waiting for me the highlight something else I'm gonna click on this and dragged down to the stuff I want and it's basically taking this data and putting it in there okay that's looking for those sell values so understand that this is really cool because it's saving us some time but also understand that it's looking for those sell values for those cell addresses so that data moves then you have no drop downs, you're not gonna have that content anymore, so you want to make sure when you highlight it, do you not want to remove that data from where it currently lives? Because then our drop downs will get affected, okay, so just understand that, okay? So I'm just gonna go ahead now and I can put my input message, which I'm not going to do. I have an error message here and we'll just try something else we'll do warning this time see see that's gonna be a little bit different. All right? We're going to go ahead and to say apartment's a please onley choose from drop down for departments ok? So we're going to show we're going to try a warning this time and see what that does okay, very good. So we have all these here and noticed that we did a little bit different here we took away the insel dropped down we have the source we have that there it's in there and then we're going to simply just click okay, come over here now and where to go on it's not showing up is not one second let's see, uh god let me shy and because of the insults are often let me try one more okay? It should actually pop up on the screens now we have this here so the insel dropped down okay? It should actually be popping up on the screen, so maybe we won't be doing it that way and I'm going to choose for you is it showing up on yours? But I forgot to put my note how do I go back to find it and edited okay, all you have to do is actually highlight the content itself and then go back to date a validation one more time. Okay, so I'm actually going to do that one more time. I'm going to choose in cell drop down so I would recommend actually doing the drop down anyway. Why it's not showing up in this case I'm not totally sure about what we see here because it should still be a list of some kind but for whatever it's not showing up all right, we are going to do the inside drop down and this is a good example of what you need to do to actually edit it. So cat, your question is a good one because it's like, well, if I have something that I want to change, I might actually want to change the selection of some kind right? I might want to do that then you could do that, but I'm actually do it in a different way where we're going to choose on an additional department and also an additional um other segment where there's gonna be a little command in there so I'm gonna choose the drop down part I'm going to say ok and now all of the you should now have drop downs all right? Now, if there's any question where I wanna have maur and there I can go back again actually when I want to do is I want to go back to here I'm going to add on another department's okay, so I will the department so I could be working with here what's another transportation okay jazz borte shin ok, something like that food and beverage. Okay, something like that right now I want to add those onto my list then I would have to highlight everything all over again, just like I would be with formatting comeback to date a validation all right? And it tells me here the selection can handle more than one type of validation or race the current settings and continue. And I'm going to say, okay, and then essentially going to start over again, okay, when I say list, I'm gonna do the same thing again and highlight these now and then I could put all my stuff and all over again because we have more than one type of thing. It is going to make me actually start from the beginning, but most of the time, it will not do that. Like how I had it the first time. Okay. So it's going to say departments choose from dropped down, and then I'm going to say warning this time and this is the icon that I should be getting. Look, look. Okay, all right. And there it is, right? And this time, I'm just gonna go on this type in production. Do that. And then c I'm going to get another message here, okay? And this time notices continue. Yes. No. Or cancel. Okay. So if I say no, it allows me to come right back to it. And I can say this. Okay and then let me just come back to my here okay there we go I can't come back to that and she just noticed with the different options are so it's a little bit less strict on uk so you can just do whatever you want at that point rather than like retry or cancel whatever it's going to be okay so just understand this so again we have the difference here between working with these guys and also working with these where we actually brought them in and we do not have another message in here okay but again understand like if I get rid of these hey, I might have a problem here let me just see here yeah, we just stay here, okay. See that now these are empty. Okay, why are the empty because there's nothing to refer back to now? Okay, so I want you to at least sort of like preemptively, you know understand what's going on with the program that as I'm doing this and I get rid of what's here because it was looking for this again if I go back to my data validation it's looking for these sell values they sell values do not exist so sometimes we want to just kind of take some shortcuts because already exists that's great but we have to understand that if we get rid of it from where it lived it's not going to show inside the drop down because it no longer has anything to refer back to okay, so really, really understand that's very very important okay, but you know that's how we want to work from time so it's up to you or you just go in and type it in directly all right? Now let's go ahead and do a different kind of data validation and this is going to be one where we have I'm gonna do one with zip codes, okay, so, um I know for a fact that, you know, we live in san francisco and zip codes start from nine for one and go all the way to nine for one nine nine okay? So I know everybody's in there, so I want to make sure that nobody actually puts like, nine, five or nine two or something like that case we just know that that's kind of the range of zip codes I can create data validation based on that criteria just the same way and a little bit we're going to do it for dates. Okay, so I'm gonna do, of course is highlight everything. Okay, then I'm going to say data validation again, but this time I'm going to say hole number and then I get this nice criteria here where I can actually do data and I say between not between equal to greater than they just understand kind of the implications of that is that you can do so many so many things really depending on what you're looking to do what you want to put in there just depending on what things you want to stay vallis you're putting this stuff in there you can make it on lee going to be this particular thing or whatever it's going to be and it could be a number it could be words it could be lots of different things but you want to keep your data valid so there might be a range of things has to be less in a certain thing or whatever it is okay so I just want to make sure that nothing is left to chance and you know there is human error we want to make sure that we always keep it inside of arrange whatever it is that's what data validations going to be so I'm going to say between and very nicely I have minimum and maximum so I know it's gonna be nine for one zero zero and I mean to say nine four one you should be not for in one nine nine okay, I know that that's within san francisco and of course you understand to do the input messages do the alerts and all that kind of stuff and I'm going to simply just click okay and now it's ready for me to put some stuff in they're away conduct on the chat room was asking if there's a way to clear out validation that fell no longer has that drop down ok good question so essentially what you want to do is if you want to get rid of any of these things here in general with anything that you want to clear out you can always go back to home only to show you how it's going to get cleared out is there is a clear over here in the far right hand side clear and then clear all okay and that goes away okay and I don't know is how dog on the pc or the mac so no but we'll find out all right and on see here on the on the mac it's right under the home tab and then clear and you say clear all right there yeah ok ok good alright okay, mac okay. Okay. Very good. Okay, so you see how it's right there alright, excellent anymore questions keep him coming for sure. Forty four actually. Okay says that is dropped down on his data list as long as their way to increase the length of the drop list so that more choices appear in the west immediately without scrolling down that list window uh okay, so it's too many but the window that showing it um that's a very good question um I'm not sure when I could try to do one where it's going to be like months of the year I can try to recreate that um let me see here remember this one yesterday from last year from yesterday doing our auto fill list so I'm going to go ahead and you just create something like that and if I just do data validation for this I just want to see what we can do here validation we're going to do list and then I'm going to choose this source that's going to be this okay so you see how easy that could be done and then I come back to here all right and then you say ok let me see how it's gonna show me well this is showing me the whole list you can see on my screen here is this is actually showing me everything so I'm not sure if his experience is a little bit different so I'm actually seeing everything on the list let me try it on the mac side because that hound dog this is rodney forty four okay, so let me try maybe it's different on the pc let me see here do you want more time? I'll do right next to it it's not monday, okay? And then I'm going to just choose these guys here good data is all good review you watch me do it a few times good here and say list and then my source it's going to be here come back here all right and I think that's about it say okay uh okay yes on the pc it doesn't show it let me see here there's a thanks for that. Let me just come back to data validation and so I guess on the pc is that it's not showing up but on the mac it isthe so um I don't think that there's a way to change that I don't see any options from doing that so yeah, thats what again one of those differences great and we actually have an update from hound dog he said when I chose clear deletes all my data I just want to delete the validation let's drop down not clear all the data in themselves so I'm not sure I understand. Okay, so you want actually so we have this right here I see okay, so let's just try um clear off clear formats then let's try that. Yeah. So let me just try oh, actually that might not do it. I can't be tried again. I may have to go back to date a validation to do that. So data validation whether it is the kind of queer no see back into here clear all there we go so with thin the dialogue walk yourself yeah, I was right there clear all and then say okay right and the data remains so essentially have to go back into it so I'll just undo that for a second so we can see here we have all of our data here and then if we go back to date a validation right where we started you'll see is right here in the lower left of the dialog boxes clear all and then our content actually disappears from in here I say okay and then I drop down should go away then okay very good great questions really good questions are you two doing I tryto duplicated in the us with zip code and it didn't take I know I did all the steps right how are you well we haven't finished that part yet so let's make sure because we'll go out and take a look at so let me just show you what it's going to do and I just want to make sure I have valid data validation so I have that there okay so I have whole number between nine for one zero zero and nine for one nine nine okay so I'm just gonna go ahead and type in nine for one one o okay it took that that's great ok nine for one one five that's great. All right now when it's been nine five five o one and then I get this error message and this is actually the generic error message you're going to get all right, so if you decide to not put any our message and it all you see this is going to come up. The value you entered is not valid. Ah user has restricted values that can be entered into the cell. Okay, so that's just kind of the generic message you're gonna get so we can go and explore yours if you want to see because maybe other people having the same issue down menu that would give me the number scene between oh, no, this is not a drop down. Yeah, sorry that wasn't clear. So basically when you want to drop on you would choose list yeah, because if you had to drop it and then if there would be like one hundred right nine for one two nine four one nine nine you actually have one hundred in there which you could do you certainly could do if you wanted to and I would I would do that by creating the list first because we learned that yesterday we could do nine for one hundred and then check it out nine for one o one and they remember you khun dio all of these if you recall from yesterday this is one of our auto fill exercises I'm just going to do like up to twenty you see it's going to give me every single one just like that this will go on for long I wanted to go, you see that and I could make a list out of this if I wanted to and that is going to be right there. So if you know exactly what you want your list components to be, you could do that, you can either type them in manually or you can create a list out of them here on the spreadsheet and then important, okay, so everybody see how that works then. So this is this is again, this is a different type of validation. So that wasn't clear. The ones that we were doing before was going to be dropped downs. All right? And then the one that we do now is going to be still data validation, but not necessarily a drop down because basically it's, like we want to have certain data in there and on lee that data in there okay, nothing more, nothing less that we're setting the parameters of that, and it could be whatever you want again. It could be text length. It could be dates. It could be all kinds of different things we will put in there.