You Should Know The Limitations of Data Validation

Intermediate Beginner

About this Tutorial

You should know the difference between Data Validation and Data Veracity (or truthfulness of data)
This video points out some incredibly simple concepts of Data Validation you may want to use in your Google Sheets to make your data analysis better.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:01 In the total universe of Google Sheets, I think there's something that is highly or least talked about, and that's validated data , or data validation . 00:15 I think it's something that we do when we think we need it, but I think you should be doing it way more. 00:20 And this is for the total usefulness of Google Sheets, and this video is all about just making you a better Google Sheets user. 00:30 And under understanding what data validation is, how it's used, in a lot of different ways to check the format of the kind of data you're getting, but to be weary and aware that this is not a veracity check, this is not checking the truthfulness of your data . 00:50 So, in this particular case here is a little example, we have like names, scores, and games, we have an average, and we're just trying to figure out the average of each player, let's say in a, number of games, and the way this data is set up is really, ah, it's set up not in a way that's very good for 01:09 Google Sheets, and not good for us in the total, ah, keeping track of games and scores, because every time we have a game we need to add a number here of 45, let's say in the next game Andy scored 15 points, so we put in 60, and we put an add a number here. 01:28 What we really want to do is have a list of log of each individual game and then add them up, and we could do that in Google Sheets, we can create a new sheet, we can say games, and we can say the same thing here that we have, the same header, name Andy, score 15, and we don't even need this games column 01:53 , we just maybe need a time stamp, and in fact Google Forms helps us do this, so we can create Google. 02:00 Form that says, hey, give us your name and your score, and it'll also add the time stamp, we can even add the date and the time of the event or game when it happened, uhm, and this is great because now we have individual data over which games they've played, what time, and what score, and we can add  02:19 these up by just using SUMIF, uhm, SUM wrapped around to filter.

We can do that in other videos on better sheets. 02:29 I have a lot of data . A lot of videos about, uh, formula combinations that's, are super helpful here. That's outside of the bounds of this video. 02:38 But what we need to make sure of is that we put in a number here, and that's what data validation is for. 02:48 So let's look at our, uh, form, but we can also put this data validation here in the B column.

In two ways, we can validate that we have the one of the player's names, and we can also validate that the score is a number , so we don't have to have 15 here or none for zero. 03:10 We can make sure that we have a number here. So we can do insert, and here we can right click, uh, view more column actions. 03:23 And here we don't have it, but we can do data , here it is, data validation right there, add a rule. 03:29 They change this all the time. So we can say, is it a drop down? In this case, for score, it probably is not. 03:36 We just want to make sure it's a number .

So, and also, this is interesting that data validation can give us constraints over what that number is. 03:47 So we can say is between zero and let's say the top score is going to be 25. Done. And so now, it says this input must be a number that's, is not between. 04:01 So let's go figure out. Actually, I think I, is not between. We should have said is between. There we go. 04:06 Is between zero and 25. Done. And see the invalidation is up at the header portion. So to get rid of that, we just need to change B1 in the range to B2. 04:19 Done.

So now we have made sure that no number is outside these bounds, which is really helpful. But again, And we need to be aware, and the second point is we need to be aware that this is not. 04:31 Truthfulness. It is not checking that it is the correct score. We still need to check that. That's what humans need to do here. 04:39 Here we can create another data validation .

And in this case, I want to create a drop down menu for the names. 04:47 So I can click data validation , or I can just go and click drop down menu here, and we can make sure we have all of the names of possible players. 05:00 Dale. Earl and Fran. We can color them as well, which is really awesome because this lets us see, uh, which data is different than the others. 05:13 Let's pick, no, not yellow. Another gray. Yeah. So here we have Andy. And again, we have invalidated our header so we can go back to a1 and change the range to a2. 05:29 Done. And now we have a drop down menu.

And this makes sure that we are only selecting one of these possible names. 05:39 In this particular case, the veracity of it or the truthfulness of this is constrained to one of these six names. 05:47 But again, we cannot verify that we got the correct name for the correct score, uh, unless we physically look at this. 05:54 This is just one of those limitations or constraints about Google Sheets and any spreadsheet software.

Or even any physical spreadsheet is that it is only as good as the data we're inputting. 06:06 But this data validation is really making sure that we are constraining those risks to the minimum amount. We want to make our spreadsheet least risky as possible. 06:20 So we can change this to Carl and put in a score of 30.

We can see here it must be a number between 0 and 25. 06:27 If we wanted to make sure that this 31.5, would never be entered, we can go back to our values between 0 and 25, and the bottom here on the right, it says if the valid is, if the data is valid, we have two options. 06:44 Show warning or reject the input. We can reject the input. So now, if I try to put in 30, it will tell me there's a problem. 06:52 This is, again, limiting the risk of having wrong information.

because somebody could potentially turn it put in 55 when they meant just 5. 07:04 This is very easy to do on mobile. This is very easy to do if you're not paying attention to inputting each and every piece of information totally, if you're not totally focused. 07:18 So that's the end of my little rant about verified versus validated data . I think, on the whole, more people need to be using more data validation . 07:27 You have lots of options. You can even validate that the fact that a number is a digit.

Date instead of a number , or that it is text instead of a number , or it is a, it's a number and not text. 07:37 Uh, Google Sheets has a profound amount of data validation here. We can see here, data , data validation , add rule. We have text, it is text. 07:53 We can even make sure it's a valid email or a valid URL.

The date, it's a valid, just the fact that it's a valid date is going to help you a lot. 08:01 This happens in calendars, especially when there's global calendars, and you want to make sure it is a specific day, month, year, or month, day, year format , or that it is, so many people use hyphens instead of slashes or slashes instead of hyphens, or they may want to put in the whole word. 08:23 Uh, when it comes to numbers, we can say that they're greater than, less than, equal to, or even between or not between. 08:30 checkboxes are super simple validation to just say true or false.

The interesting thing about checkboxes is that it will always be false unless checked, so there's no null. 08:45 Uh, when we're inputting data , sometimes it is incredibly useful to have the option of null, meaning there's no known answer, there's no text, no data , nothing. 08:55 It is not even anything. It is null , meaning there's nothing. Check it boxes don't allow us to have nothing. It's either going to be false or true. 09:04 That is it.

So that's interesting if you want to have that kind of situation, but also be wary that a checkbox unchecked in this particular case does not mean null, it means literally false. 09:20 And then we can see that we can even select none.

Uh, the other data validation is custom formula , this presents an entirely new set of possibilities here. 09:32 Almost an infinite number of sets because we can combine formulas here, we can look at other cells , we can say is something blank somewhere, is something, some other number in some other cell. 09:44 And the tricky part about custom formulas is that we can either use absolute references or relative references .

So in a situation where we're looking at the entire row , we don't have to program in the formula that it's looking for the entire row , we just say, in this case C1, we want to save. 10:02 Hey, in A1, is there something? We just put in A1, and then for every single row in this formula , it will automatically change that row number to look at the relative, uh, column, for instance. 10:16 It won't look at A1 for all of them unless we say, always look at A1, putting dollar signs in.

So that's very interesting in custom formulas and something you may have to work around and test out. 10:27 I have a lot of interesting, uh, examples of custom forms . Over in BetterSheets and among the BetterSheets formula , uh, tutorials, you can see a lot of cool uses of this custom formula . 10:39 Is, I won't go into that in this video. I want to make this, I wanted to make this video pretty short just to urge you to validate your data more and to double check the veracity or truthfulness of your data even if you have validated data . 10:55 Thanks for watching.</div>