WEBVTT 1 00:00:01.270 --> 00:00:13.940 Bill Rosener: Hello! This is Dr. Bill Rosner. Welcome back in this instructional video. You learn about data validation using Google Sheets data. Validation is a feature which is used to control what users can enter into the cells. 2 00:00:14.000 --> 00:00:31.720 Bill Rosener: It allows you to dictate specific rules. It also allows users to specify or display a custom message. If users try to enter invalid data. As a result, data validation helps reduce the amount of unstandardized data heirs or irrelevant information in your worksheet. 3 00:00:31.840 --> 00:00:46.620 Bill Rosener: It's a helpful feature, especially when widely sharing and spreadsheet with others for completion. Data validation can help save analysts valuable resources that are spent when data is not inputed correctly. But now that we know all we want to do, let's kind of go ahead and get started here. 4 00:00:47.330 --> 00:00:57.800 Bill Rosener: So in the same er as you found a link to this video, you should also find a Microsoft excel file, and I'm: just gonna go ahead and select this data. I'm gonna copy it. 5 00:00:58.110 --> 00:01:06.690 Bill Rosener: It's a keyboard shortcuts and back to Google sheets. I'm going to position my cursor in a one and paste. 6 00:01:07.710 --> 00:01:13.780 Bill Rosener: and if you want to, you can adjust the width of the column. So I put your cursor up here and increasing those. 7 00:01:15.120 --> 00:01:23.100 Bill Rosener: Okay. So we're gonna do a data validation on the subscription type the country and the URL. And right now let's go ahead and 8 00:01:23.160 --> 00:01:29.870 Bill Rosener: only got 3. I've got standard light and premium, and let's go ahead and type in also one called professional. 9 00:01:31.540 --> 00:01:33.160 Bill Rosener: and maybe one person 10 00:01:34.300 --> 00:01:35.840 Bill Rosener: who's entering the data. 11 00:01:36.660 --> 00:01:39.190 Bill Rosener: I's all the word professional out. 12 00:01:39.340 --> 00:01:42.200 Bill Rosener: and maybe someone else, because i'm just gonna type in Pro 13 00:01:43.270 --> 00:01:43.950 Bill Rosener: Oops. 14 00:01:47.700 --> 00:01:56.250 Bill Rosener: Okay. So now we've got this inconsistent data where it's like, okay, we've got Some people enter in professional, some people. Internet, the word chrome. 15 00:01:56.260 --> 00:02:10.699 Bill Rosener: It's what's gonna happen when you start doing a search or a replace You're not going to be grabbing all the data. Maybe if you search for professional, we're not going to find pro for find looking for Pro: we won't find these. So we're gonna go ahead and do some data validation to make sure that this does not happen. 16 00:02:12.090 --> 00:02:13.610 Bill Rosener: So this first one 17 00:02:13.950 --> 00:02:26.610 Bill Rosener: I'm going to stop right here, and then the other ones will keep going. So as we've had more and more content or entries it's gonna our data validation will continue. So the first case here, i'm just gonna highlight 18 00:02:26.870 --> 00:02:36.100 Bill Rosener: B 2 through B 10. I'm: going to come right up here, and I'm going to choose data and come down and choose data validation. 19 00:02:37.250 --> 00:02:48.390 Bill Rosener: And i'm gonna come over here and i'm gonna choose. Add rule. and the person we're just going to use to kind of default here, which is a drop down. and then we just simply enter in our options. We got light 20 00:02:50.510 --> 00:02:51.590 Bill Rosener: standard 21 00:02:55.820 --> 00:02:56.880 Bill Rosener: premium. 22 00:02:58.270 --> 00:03:09.000 Bill Rosener: and i'm gonna go with the pro just P. Ro. And as soon as I do it I can actually even see if we're already getting some little invalid, and i'm gonna hit done 23 00:03:09.630 --> 00:03:16.260 Bill Rosener: so. Now I need to come on and change this from professional to pro or both of those. 24 00:03:16.720 --> 00:03:28.020 Bill Rosener: Okay. So now I've got some consistency. So maybe, for example, the word standard, it's not possible someone can't come in and go an abbreviate standard. Std. You don't even get an option there. 25 00:03:28.170 --> 00:03:33.170 Bill Rosener: You only have a pull down, menu, and these are the 4 that you can select from. 26 00:03:34.240 --> 00:03:45.110 Bill Rosener: Okay. Next up oil. We're going to do some data validation on the country. And right now I can see I've got United States and the 2 words about the abbreviation us and abbreviation the U.S.A. 27 00:03:45.370 --> 00:03:52.870 Bill Rosener: And in this case, rather than on the pull down, we're gonna specify range. Somebody come right over here and i'm gonna type in location. 28 00:03:52.960 --> 00:03:57.230 Bill Rosener: And the reason I went here is I could have actually done the same thing with a subscription. 29 00:04:07.690 --> 00:04:16.029 Bill Rosener: Okay. But I then I went with the drop down. So in this one we're gonna go with 3. I don't know. I'm gonna go with U.S.A.: Let me go type in United 30 00:04:17.180 --> 00:04:20.399 Bill Rosener: Kingdom and Sweden. 31 00:04:22.079 --> 00:04:25.970 Bill Rosener: Okay. So now i'm gonna come back to she one. 32 00:04:26.780 --> 00:04:35.940 Bill Rosener: In this case we're going to keep going all the way down to the bottom and to do that i'm going to hold the control button down, Hold the shift key down and press one time, and that's going to stop 33 00:04:36.200 --> 00:04:42.990 Bill Rosener: where our data stops. I'm gonna press one more time so to hold the control button down, press the ship counter, press 2 times. 34 00:04:43.540 --> 00:04:51.740 Bill Rosener: and that's going to take me all the way down to the end. I'm not going to choose another. Add a rule. There was a first one. I'm not going to choose, Admiral. 35 00:04:52.020 --> 00:05:04.530 Bill Rosener: and this one, rather than a drop down. I'm gonna go from range. And then i'm gonna press right over here on select data range. And i'm gonna highlight sheet, too. 36 00:05:04.620 --> 00:05:07.440 Bill Rosener: And i'm gonna highlight these 3 cells. 37 00:05:07.960 --> 00:05:09.900 Bill Rosener: and that's okay. 38 00:05:09.930 --> 00:05:26.350 Bill Rosener: And I can see it's gonna be on sheet, too. We're gonna be looking at B 2 through P: 4 and press. No. and you can see they're actually listed right here. So now, when I come back to sheet one I can see I've got some mistakes. I need to fix, or just some. 39 00:05:26.500 --> 00:05:33.650 Bill Rosener: So rather than U.S.A., I'm gonna choose the United States. I'm gonna choose U.S.A.: and the same thing right here. 40 00:05:35.190 --> 00:05:36.440 Bill Rosener: And one more. 41 00:05:38.110 --> 00:05:47.390 Bill Rosener: Okay. So this one we went all the way down to the bottom. So if we start adding some more entries when I do, you can see. Here's a few that 3 choices I have. 42 00:05:51.090 --> 00:06:06.650 Bill Rosener: Okay. And the last one we're going to do is add some data validation to the URL. If you look closely here. Maybe it will start at the top. We get some valid, URL: so we get right here. This is actually an email address. Help at smart talk 43 00:06:06.700 --> 00:06:21.850 Bill Rosener: this next one here soft 4 years. That's not really an URL needs to end with.com.net edu, etc. Here, and the last one again. That's an email address as opposed to a URL. So in this case, let's again go down to the bottom on the highlight 44 00:06:21.850 --> 00:06:28.820 Bill Rosener: f 2. I'm gonna hold the control button down. Hold the ship, button down with us once and twice. 45 00:06:30.530 --> 00:06:32.970 Bill Rosener: You actually need to hit down on that previous one. 46 00:06:35.650 --> 00:06:42.850 Bill Rosener: I'm. Now going to press. So we've got our first 2 date validation rules. I'm not going to choose one more. It's going to be pretty simple here. 47 00:06:43.650 --> 00:06:51.330 Bill Rosener: and i'm going to choose on this case. Here. Text is valid, URL and I'll just sit down. 48 00:06:52.370 --> 00:07:04.960 Bill Rosener: you know, by one I could actually even close that to get that out of the way there. So in this case here I don't have a valid URL. So I will highlight this one. and i'm gonna put that maybe the URL is actually help at smart talkcom. 49 00:07:05.040 --> 00:07:10.080 Bill Rosener: This one is missing a. It's a.edu or dot com. 50 00:07:10.960 --> 00:07:21.600 Bill Rosener: and the same thing here on this last one. It looks like an email address. So i'm gonna go ahead and put a period there. So anyway, now, as I start typing in more addresses. 51 00:07:21.660 --> 00:07:23.070 Bill Rosener: email addresses. 52 00:07:24.340 --> 00:07:28.900 Bill Rosener: So if I was just to type in issue. you're gonna see it's gonna give me an invalid 53 00:07:28.920 --> 00:07:42.620 Bill Rosener: if I type in Okay, now, it appears in red. Hopefully, in this video you learned about data validation. And hopefully, the goal here is to create standardized data 54 00:07:42.810 --> 00:07:52.890 Bill Rosener: and hopefully result in less errors and mistakes when you start doing, search and sorting and creating reports, because everyone is using the same. 55 00:07:53.230 --> 00:08:01.140 Bill Rosener: They down there in this case, in the same country They're not type in the United States, you know, type in us. They're type in U.S.A.: 56 00:08:01.400 --> 00:08:03.900 Okay? Well, i'll catch you next. Video: thanks a lot. Bye.