Magical Things You Can do with Checkboxes in Google Sheets

Turn your sheets into web apps with interactive checkboxes. Make your sheets' UI better with more interaction.

Video Transcript

Hi, welcome to Better Sheets. I'm Andrew. I'm making all these tutorials and this free teaser is meant to show you not just what magical things you can do with check boxes, but also to show you my process and how when you become a member of better sheets, you'll get a lot more than just basic tutorials on formulas.

What you're gonna get is. Very similar to this, where I go into some of the unique use cases and I show you step by step how to do some of these things. Without further ado, let me share with you the magical things you can do with check boxes. This is a fairly recent addition to Google Sheets, is the uses of check boxes.

It used to be very well impossible, but now you can just insert a checkbox by going up to insert and checkbox, and you have a nice little checkbox here and that check box. Is true or false? And if it's checked, it's true, if it's false, if it's unchecked, it's false. But you can use this in a lot of very interesting ways, not just like a to-do list.

You can make it say hello and how I do that, here's a formula. What it is saying it's using an if formula and essentially just say equals if if. B3 is equal to true and say hello. Just take hello in quotation marks and that's it. Hello? And if you get this false, why that is because this formula takes three arguments you need to add on another comma, and when it's not, you couldn't say something like, see, and you can say anything you want there and then check Hello. And you can say if it's checked, hello. If it's false. Or as I have it in the top one. The thing at all. And I go more into the if statement in one of the tutorials in the paid membership section where this is a really great formula to hide cells.

You want blank until something happens. And I go much, much, much deeper into it in the in one of the tutorials inside. So next thing I wanna show you is you can use these. Just a little hiccup there. Needed to sneeze so you can use check boxes in this unique way if you have say some values that you want to add up.

And typically to add them up, you need to just. Do some, right? You say some C seven through C 10 and you have this number, but sometimes you want to say, oh, I only wanna know the these two or these two, or these three, or, I don't even know yet which ones I wanna add up. But what you can do, watch this F seven box.

Each time I check it adds to that sum and even the cooler. Done here, you can do multiple things. You can say, okay, this is the average of everything 1 0 8, but if I take just the average of the check, it's here to one E 7.5. So you can create these formulas that say if checked, add them up, or if checked, what's the average of them?

This came in handy very recently for me because I was using I had different Items that had to take different amounts of time, and I wanted to add up half of them, like a certain amount that said, okay, if I did this many items, how long would that take? But I had a very long list and I wanted to know what the percentage difference was of different items checked.

So this helped me figure that out and through this formula. So all this formula is is three formulas in a. String together. It's the sum. And so right here, actually, we'll, let's rebuild this step by step. So we're gonna insert in four columns, in check boxes. We're gonna put in some sums.

This is just a. A colored cell with a number in it, and I like this color to say, Hey, put some number in me. It's like a really garish number, a really garish color. And that's what I use personally to say, put some input here. And so a total, we'll do this, a total, it's gonna be equal to some. Of this, right?

That's it. But to get a checked total, so we wanna do checked total. We're gonna do a couple things here. It's actually three things, right? We're gonna do first we need the sum but the sum isn't of everything. It's of a filter. We use, filter and filter. Takes a few arguments. You can always use this question.

Box, but it gets in the way a lot of times. So what are we gonna filter? We wanna filter these range, and we wanna filter it by if b, this column is equal to true.

And what we get is a na, right? So why that is, is because nothing's checked. So if I start checking, I have a number, but this is really garish and awful. And again, in your, in another tutorial, in the paid section, I go into deeply what I'm gonna do now. But basically it's an if error, and I want it to just do literally nothing.

I know that, I know the error is just, there's nothing there an A, but I want nothing. So now once I start checking, boom, I have some number. Great. And then you can do the same with average. I'm not gonna go too deep into that, but if you check here, it's if a if error, it's the same exact formula except as instead of some, it's average.

So just go here, copy and paste, and instead of some go average.

And that's the checked average. That's it. And we can also do average here. And that's literally the same thing here, except average.

And that's it. The next thing that I think is really cool, that's really almost a magical thing is that when I said, this was true false, you don't have to remain true false for it being on and off, you can actually change the value. How you do that is you go to actually data. This is, it's a little different and it's a little hidden data validation.

For your criteria, you go checkbox, click use custom sell values. And here they give you what the normal checkbox is true and false. But you might wanna put a number checked zero, or you might wanna put a larger number, maybe it's a hundred. And you go save. And now if you look up here where the formula is, it's actually a hundred.

And I can use this quick function equals this. I just click over here to show you that's a hundred. So now when I click, it's a hundred. You can use this in sums, you can say, add stuff up. But here's, I think a really unique case. I'm gonna share with you. This is central, essentially if you wanted to take a survey of your team and you said, Hey, we're gonna have this.

We wanna prioritize these three things. We're gonna do one of them. We just wanna know which order we're gonna do 'em. And you want to assign numbers or values to different people. Like different people's level of seniority will say, they probably know more about the situation in a certain way.

So you wanna give them rank, you wanna give them priority. But you wanna get input from everyone. And you wanna see if there's a large number of different differing voices. But let's see. So what I did here is I inserted each of these check boxes add as a different rank. And how you do that is we'll show you but I'll show you how it sh does so people check off, maybe they want to do in that order.

And you see now, okay, the user dashboard has a higher rank than the users can upload images. You know this by, this ranking, each person can have their own even different color or different column here. Really good way to, to take a survey of everyone. And this is just summing those numbers.

Now, if I had not been able to change these values they, the. Checkbox would have been something like this. And this is a, I'm gonna try it to do this quickly, but you'll see how garish this is basically me. I know the manager is gonna be 10 points and the engineer is gonna be five points and the intern is like one point.

So if I wanted to add that up with just two false statements, I'd have to be like it's equal to if. This equals true. Then 10

10 and I'm taking the sum of this, plus if this equals true. True. And five. And you can see how garish this is. This would take a long time to figure out and do this multiple times, right? One point. And so we get the same result, right? But this formula is awful to deal with when I can truly just assign these numbers right, and I can go and edit these numbers anytime I want, I can go to.

Date of validation, I'm gonna go to checkbox use custom values a hundred and unchecked zero. Save and then I can copy paste those and now I just do right click, dial the validation, change this to 25, save right click data validation, change this to 10, and now I go some. This is equal to some. I like these three and I got some of them done instead of having to deal with this formula and assigning numbers and going in here.

And this is just a different way to do it. And depending on, what kind of things you're doing, at least knowing to do it this way may save you a lot of time and you can put together some pretty interesting surveys, charts Really get some, like people's opinions, ranking priorities.

You can, set up your competitors and see if they're winning awards and check them off if they are, or are they featured or back links. So you can do a lot of cool stuff with these. Just check boxes. I think check boxes are really fun and there's a lot of magical things you can do with them.

Please feel free to feel free to watch this video as many times as you want. See if you can get something else out of it. Email me, Andrew Camy approved. Comp it's Andrew at kefi approved. Huh? And I'm happy to answer any of questions and consider, buying a membership it's to this better sheets.

Thanks. Hi.

Better Sheets