Data Validation with Formulas

Intermediate

About this Tutorial

The issues you might run into validating data and 4 formulas you can use to create workflows or processes in sheets. Covering: ISBLANK, ISFORMULA, ISURL, ISTEXT formulas.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

IF() ISBLANK() ISFORMULA() ISTEXT() ISURL()

Video Transcript

<div> Hello. In this video I wanted to go over data validation and data validation in formulas or with formulas, meaning we can actually figure out what data is what's really cool. Is we have covered in better sheets. A ton of is blank. We've used islan with a lot of the IF formula . It's a great combination, but there are some other is formulas. The is that allow you to do data validation within a form, from a formula inside of a sheet. Normally if we want to do data validation , we would do go here, right click on a cell, and we'd have view.

Actions and then we can clo go click on data validation . Now, data validation rules exist here and we can add a number of. And that's how we get dropdowns is we validate that the data is one of these options, but there's a lot of other things we can do. Text contains te text does not contain. We have all these rules that we can put in dates greater than less than check boxes. A checkbox is a data validation because it's validating that it is either true or false and nothing. And then we have a huge op amount of options with custom formula with this data validation.

But that's not what we're talking about in this video. In this video, we are gonna go through using the formula , using very specific formulas. The iss, as I say in order to validate data . Now, why would we ever wanna do that? Like, why would we not validate data say through some other means like this data validation rules or even. Why would we pick, why would we use data validation maybe without Google forms ? Google forms I think, is one of the best ways to do data validation , cuz the form itself has lots of data validation. You can be very clear at what you're asking for.

The person putting it in can see is it correct or not? You can have data validation of it's, is it a URL or not? Is it an email address? All types of stuff. You can have calendar pickers much easier to enter data if you use a Google form , and then have that data validate through Google Forms . So why would you not use Google Forms ? You would not use data validation , but you u would use a formula . One reason might be is that you're, you already have your data inside of a sheet.

You don't want to put that data back through a form and you don't necessarily want to just figure out a rule. You want to quickly figure out one formula , copy paste it, or do a array formula . We wanna get it done right now. Or what's even more interesting is allowing a lot of sheets we create need to allow someone to edit it, but they have some. Restrictions, some constriction or constraints and we don't necessarily want to go on to a next step unless something is fulfilled, right? And so that's why I love if is blank.

That's one of my favorite formula combinations if is one of my favorite formulas, filters but if is blank allows us to produce show something. Basically, once something is filled out, show another cell. So it gives us really cool way to create workflows, also create games and stuff, but is blank. I've covered in some other videos, so I'll cover it very quickly here. It's either gonna resolve the true or false. I have in c2, here I have is blank A two and it's just asking is a two either blank or not blank. And in this case it's false. Not blank. If it is blank, it becomes true.

See it, it's a little bit different than the other ones right now. So what we, what I like to do is, I like to do if and if needs to resolve to true or false and is blank will always re resolve to true or false. And so if blank a two now that's filled. So it's gonna be false. So the true I like to do as blank, like we don't do anything if it's true, meaning it is blank. But once then something is written there, we can say next step. And now see there's next step. So if I go to a two and I delete anything, and now it is blank. It doesn't say it, it hides that data .

So it's a little in opposite of what you imagine. Or immediately think of. So it takes a little bit of cognitive difficulty to figure out what, how to use this if is blank. The other ones are going to be a lot more straightforward is formula . What's cool about this? Is formula allows you to know, is there like basically an equal sign? Because all formulas will have an equal sign cuz if you have right over here on is Formula D three. So in the E column I have false. False and it's is formula . So D three right now, there's nothing there. So it's not gonna resolve properly.

Cause that's blank, right? Like blank doesn't mean that there is a formula . There's not a formula . So we're, you're going to get a lot of falses when you really have just null or nothing. But if we put in equals, let's say, is blank, there. The two. Now we have True. Cool. So this might be, just thinking about it in my own little personal world is formula could be very useful for creating creating workbooks in order for you to learn more about formulas, right?

If you're trying to learn about formulas and you're gonna enter a formula in, I can say, did you get the equal sign there? Did you, Is it at least a formula that exists? You get a point or something, you can do that. But also it rings true if it's just a plus side. Let's do some math. That is true as well. So what's really cool is say you want to rid your sheet. Let's say you're tr you, you created a sheet, you did a lot of formulas. You did it for a bus or even for another company. You had some client work.

Or even for a colleague and you're like, the formulas that I did in here don't really matter. I really want to like copy paste values , but I don't know if I fin I finished it all. I don't know if all of these are copy pasted values and only values . I think there might be some formulas hidden in here. You can quickly do is formula and. Check an entire column and say, is it true or false? And if it ends up being true, if you can count all the trues, right? You can say Equals count if. Count if E to E. Criterion true. You can say, is there a true or not? So now you can count.

How many formulas did I leave in? Very cool, very quick formula to say true or false, just I wanted to ma mention and make sure that you followed along that even if it's blank, it turns out to be true. So that is something to be aware of, and it could be a problematic if you're looking for certain things, meaning this should be nothing. It should not be. It is technically false, like it is correctly identifying that this is not a. It's just sometimes you want it to say null. So you can actually combine this with is blank right now is U url. Find the U URLs.

Figure out is the data in here a U url? So a U URL might be better. sheets.co at the very bottom right the least amount of url, but that is a u URL is. And that is true. So you can also have http s better sheets.co. And that should be true, but it's, let's say we mistyped it, htp, and so we have what the structure that looks like of U url, but we missed a T, right? We didn't do http, we did htp. That is false. So it's making sure that not only are.

Do you have some url, but then you actually have a valid url that is really important if you're doing like link copying stuff and trying to do like sheet, let's see, sheet.new that doesn't get it. So this is interesting, right? sheet.co. True, but sheet.new is an actual url, so I don't know if it gets like newer. It seems like it doesn't really understand newer domains, but if you do, let's say www sheet.new, that is still false. So I think you have to do the whole https sheet.new, and that is true.

So that is something to be aware of in the is URLs that newer, longer, bigger domains TLDs might not be working. Really upsetting to know that they don't do that. Now. Next is text. Is text. Very simple. Very simple, right? Is something text or not? Yep. It's, but is it or not? Cuz if we have a url, let's do dot co. Still true, right? That is text. If we got formula . A formula and it's just text. There it is. So even though it's a formula , it's also text. But let's say we have that. Yep. So even a formula that ends up in text is going to be text.

But let's say we have a checkbox let's check. We're looking at this E five and seeing that true or false, but let's see if we add a check box here, will it? True or false? Is text. So right now it's false. And it's saying is text is false. And even if the checkbox is checked, it is still not text. It's false. So even though there's something in there which is blank, would've said, oh, there's something there, right? Nope, Nope. Is blank. Even a checkbox is gonna be not true or false. It's gonna always be. So that's another caveat to look out for. That's another issue.

Roadblock challenge you might have is if you have some check boxes and you're like, is this blank or not? This is definitely not blank. It's, it has some value, right? It's true or false, but it's gonna be false and on the is blank. But that's actually fine if you're doing workflow and stuff, cuz true or false is actually still. So thank you for watching the video.

I hope you enjoyed seeing a little bit of challenges or roadblocks with these new kind of, not new, but these new to you or new to me, is formulas a lot more is and a lot more data validation that you can do inside of sheets. Really useful for workflows. For little graders, calculators that you're trying to build, maybe some lead magnets or something, and you're saying, okay, here's a workflow, or here's a process that we go through and we just need to check along this process. Have you filled in the correct information?

Not just that the information is correct, but it's in the correct format . Is it a text? Is it a formula ? Did you fill something out? Did you make something? Did you make a change that like is irrevocable or not? Not almost nothing is irreversible here. Hopefully you enjoyed this video and seeing, in place is blank is formula , is your URL is text and you saw some of the challenges. Thanks for watching.</div>