How To Hide Data with Checkboxes in Google Sheets

This tutorial is meant to show you not just what magical things you can do with checkboxes, but also to show you my process on how when you become a member of Better Sheets you'll get a lot more than just sort of basic tutorials on formulas. What you're going to get is very similar to this: I go into some of the unique use cases and I show you step-by-step how to do some of these things.

Now let me share with you the magical things you can do with checkboxes. Inserting checkboxes in Google Sheets used to be very impossible, but now you can just insert checkbox by going up to insert and checkbox and you have nice little checkbox here.

That checkbox is true or false. If it's checked, it's true. If it's unchecked, it's false. You can use this in a lot of very interesting ways, not just like a to-do list. You can make it say “Hello” with this formula:

If you get False, that is because this formula takes three arguments. You need to add another comma and when it's not True, you can say something like “hmmmmm.”

You can do anything you want there. You can say “Hello” if it's checked and if it's false, “Hmmmmm.”

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. I go much deeper into it in one of the tutorials.

Next thing I want to show you is that you can use checkboxes in this unique way if you have some values that you want to add up. Typically, to add them up you need to just do a Sum. For this example, it’s C7 to C10 and you have this number, 432.

Sometimes, you want to know the sum of these two or three cells. Now watch this F7 box. Each time I check it, it adds to that sum.

Even cooler, down here (F9) you can do multiple things: The average of everything, the average of the checked.

You can create these formulas. If checked, add them up or if checked, what's the average of them?

This came in handy very recently for me because I had different items that I had to take different amounts of time. I wanted to add up half of them, a certain amount that said “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. This helped me figure that out.

All this formula is three formulas you string together. Let's rebuild this step-by-step.

We're going to insert in four columns in check boxes.
We're going to put in some sum. This is just a colored cell with a number in it.

Then there’s a total. It’s going to be =SUM(C12:C15)

We're going to do a couple of things to get checked total. First, we need a sum but the sum isn’t of everything. We use filter, which takes a few arguments.

What are we going to filter? We want to filter this range, C12:C15, and we want to filter it by B12:B15 and if this is equal to TRUE.

We get #N/A because nothing's checked. If I start checking, I have a number.

In another tutorial in the paid section, I go into easily what I'm going to do now but basically it's an IF error. I want it to just do literally nothing. I know the error is just there's nothing there, #NA, but I want nothing.

Now once I start checking – Boom! I have some number. Great. You can do the same with average.

I'm not going to go too deep into that but if you check, it’s IF error. It's the same exact formula except instead of SUM it's AVERAGE.

The next thing that I think is really cool that's really almost a magical thing is that you can actually change the value.

Go to Data > Data Validation. For your Criteria, choose Checkbox. Click “Use custom cell values.”

For Checked enter “1” or you can enter a larger number, like “100.” For Unchecked enter “0.”

Now if you look up here where the formula is it's actually a hundred.

I can use this quick function and I just click the checkbox to show you that's 100.

A unique case I'm going to share with you.

If you wanted to take a survey of your team and you said, “Hey, we want to prioritize these three things.” We just want to know which order we're going to do. You want to assign numbers or values to different people with different levels of seniority.

You want to give them rank, you want to give them priority, but you want to get input from everyone. You want to see if there's a large number of different voices.

What I did here is I inserted each of these checkboxes and as a different rank.

Each person can have them different color or different column. It's a really good way to take a survey of everyone and this is just summing those numbers.

To avoid using an awful formula like the one below, I can just assign these numbers right and I can go and edit these numbers anytime I want.

Go to Data Validation and use the following values:

• Criteria: Checkbox
• Use custom cell values – Check this.
• Checked – 100
• Unchecked – 0

Save it and copy-paste that to the other cells.

Then on the second checkbox, right-click and choose Data Validation. Change the Checked field to 25. Save that.

Do the same thing to the third checkbox, but this time change the Checked field to 10.

Now I go SUM for all three checkboxes and I got the sum done instead of having to deal with this formula:

This is just a different way to do it. 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, get some people's opinions ranking priorities. You can also set up your competitors and see if they're winning awards and check them off if they are.

You can do a lot of cool stuff with these. I think checkboxes are really fun and there's a lot of magical things you can do with them.

Watch the video for this tutorial:

Read these other tutorials to know what other cools things you can do with checkboxes:

Get more from Better Sheets

Join other members. Pay once and own it forever. You get instant access to everything: All the tutorials and templates. All the tools you’ll need. When you’re a member, you get lifetime access to 200+ videos, mini—courses, and Twitter templates. For starters. Find out more here.