10 Ways To Use Checkboxes In Numbers

Checkboxes are a useful tool in Numbers that allow you to simply indicate a true or false value. You can use that as data, to sort, filter or alter the outcome of formulas.

Video Transcript: Hi, this is Gary with MacMost.com. Let me show you some uses for Checkboxes in Numbers. 
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
One way you can format a cell in Numbers is as a Checkbox. Functionally this is the same as either typing the value True or False into the cell except instead of seeing those words there you're going to see a box that either has a checkmark in it or doesn't. 
So here is one way you can use those. You can use those to indicate whether something has been done. I'm not going to perform any calculations in this first example but here I've got some cells. It would be nice to be able to indicate whether something has been done. Like, for instance, whether this order has been prepared, packaged, shipped, and then received. Now I could put a value like 1 or zero in them or I could type the value True or False and that would work. But a better way to do it would be to select all of these cells and then go to Format, Cell and then change the data format to Checkbox. When I do that a Checkbox appears. If the value is blank or False it is empty. But if I click it it changes so there is a checkmark there. Notice the actual value here is now True. Where, say, the actual value of this cell is False. So I can now go through and check each one of these off, like that, and I can record in my spreadsheet very easily when things have been done.
Now you can use Checkboxes to organize your data. So, for instance, here you could see I've got some orders that have been completed. They have been received by the customer. If I wanted to Sort to put those orders at the top and the orders that have not yet been received at the bottom I could do so. I could simply select the table here. Click on the Column and then Sort either ascending, so I put the completed orders at the bottom, or sort descending here to put them at the top. Now if I were to change something, like to check these, and then resort I would then move this one up. But I could also add another column here and then in this one put a number that counts the checkmarks. So I could do Count and then If. COUNTIF will allow you to select a range, so these four cells here, and then the condition they must meet. So whether or not they are True. Now I can get four for that row and then as I paste them here, you could see, I get three, four, and one. So now I could sort by this row, like this, and put the ones that are more completed toward the top. If I want I could Hide this column here so it kind of works invisibly. So now if I were to change these like that and then go to Organize, Sort, I could choose this hidden column here and have it sort by the hidden column and that way I can get my sorting order without having to see the number there. 
You can also Filter based on similar things. So, for instance, if I just wanted to see all of the orders that had not yet been received I could click here and I can do a quick filter and doing a Quick Filter on this column only, I could say show me only the ones with the False values. So I can see the ones that are not received. The great thing about Filters over Sorting is that it updates automatically. So, as soon as I check received here the filter gets applied and that row disappears completely. Anytime I want I could go to Organize and then Filter here. Turn Filters Off to see everything or turn it back On again to filter to the ones that are not yet received. 
You can also count the number of checkmarks. So I'm going to add another row here but I'm going to go to Format and Table and then change that to a Footer Row. So now I have something where I can easily go and do calculations on the entire Table but it won't be part of the Table itself. So if I wanted to count the number of checkmarks here I could use what I did before and say COUNTIF and then look for the entire column, so just H in this case, and then see if it is True. Now I can count the number received. 
Now I can also get a percentage. So this gives me the number but I can also count the total. So I can say COUNT A. You could see the number that are not empty. So in this case it is just going to count the total number there. I can do all of H. I'm dividing the number of checked by the total number. I get 0.75. If I change the formatting of that cell to percentage I get an easy percentage there and it will update depending upon how well I'm doing. 
You can also SUM UP numbers based upon whether or not a checkmark is present. So, for instance, I can get the sum of the prices here. But only for rows where there is a checkmark here in the last column. So to do that you would do SUMIF. The first element would be H, whether or not it is checked. Then you would compare that to True. So only count rows where H is True. Then what do I add? Well, I'm going to do the amount in D. So now I get the total here but it depends on whether things have been checked. If I uncheck things you can see it changes the total. If I check them it adds them up.
You could also control calculations based on a checkbox. So, for instance, here if I wanted to get the total price but only add tax if this is checked. I can do that. I could create a formula here that takes the price, adds, and then uses an IF, SAYIF this is True then add tax. Which would be the price times some number. So 0.07, 7% here. Or zero if not checked. So now you can see it is 300 but if I checked this box you could see it is 321, plus 7%. Then copy and paste this in all of the rows there and then the formula works with the checkbox. 
You can also have it choose a different formula based on the checkbox. So in this case I can have it do IF and then the condition is, whether or not this is True, then do a formula like this +itself times 0.07 and otherwise do a completely different calculation, like this plus itself times 0.03. So now the total there depends on the checkbox. It is doing two different formulas based on whether or not this is true. 
Now take it one step further and it can actually have data shown based on whether or not there is a checkbox. So here I can calculate the tax. I could say IF this is true then the tax would be the price times 0.07. Or zero if otherwise. So I get $21 there but if I Copy and Paste that zero this one gives me $24.50 and this is zero. Then I can have a total that is basically this plus this and I can Copy and Paste that throughout. Now what would be really cool is if you didn't even see the zero here. That can be done in Formatting. So I can select all the cells here in Column F and I can go to Format and then cell and then set it for a Custom Format. Then in the Custom Format say, okay the   tax but let's add currency before it. We can add a decimal after it if we wanted to but add a rule that if it is equal to zero then simply show nothing. So now we get nothing here shown. It is just going to be blank. Every time there is a zero value it is going to show it as blank because of our Custom Formatting. So we check this box here. We get a tax there but we uncheck it and it is just blank. The total will always workout.
So I hope that this gives you some ideas of how to use Checkboxes in Numbers. Thanks for watching.

Comments: 12 Responses to “10 Ways To Use Checkboxes In Numbers”

harry

1 year ago

Gary , is there a way to prevent user from checking a box if the previous check box is not checked. in your example you could not check received if one of the prior boxes is not checked?

Gary Rosenzweig

Harry: No. There is no way to do that. For one thing, there is no distinction between a "user" and someone more powerful than a "user" when it comes to a Numbers document.
What you can do though is have a cell that uses an IF function to display a noticeable error message when things aren't being done right.

Jimmy Marchini

Is there a way to use conditional formatting for a cell related to its corresponding checkbox cell. For example... in the table you use in this video... is there a way by clicking the checkbox in cell E3 it will conditionally change the format color of the font in cell D3 ($300).... or perhaps change the color of the D3 cell itself, or even both.

Jimmy: You can do that if you have F3 using an IF function to set it to the value of D3 if E3 is checked. Then the conditional highlighting for D3 has the criteria that it should be highlighted if D3 has the same value as F3. Then hide column F if you like. https://macmost.com/numbers-conditional-highlighting-based-on-other-cell-values.html

Thanks, Gary...that works. Of course now I have to berate myself for not thinking of that myself.

John

Gary, thanks. This was really good information. I use Numbers often but I don’t look into the details often enough. Numbers is actually quite awesome.

Mik

9 months ago

Hey, is there a way to show checked boxes? Right now, they just dissapear. I'd like them to appear at the bottom of the list. Thanks Gary for explaining things so well!

Mik: Not sure what you are dealing with. They shouldn't disappear. Not sure what you mean by "appear at the bottom of the list" at all.

Dawn

4 months ago

Hi, I'm trying to create a checkbook register with a "checkbox" option so it only calculates the balance when it is marked true. I've successfully made the spreadsheet and the formulas work for a running balance, but I would like the option to the balance calculated only when the checkbox is marked "true." I'm at a loss and have tried some SUMIF but not quite figuring it out.

Dawn: I've got a section in this video that deal with SUMIF. I would work through that, using my example to understand how SUMIF works, and then try it in your own document.

Néman

3 months ago

I have a table with the following numbers: 100 -50=50. Is it possible to check a checkbox next to -50 so that -50 is considered as output. So when -50 is ticked then the result is 100 because -50 is no longer subtracted. Thanks for help!!!

Néman: You can't do it in the same cell. You would use multiple columns for this and an IF function. But it is hard for me to say much more without knowing what you are actually doing here.

Comments Closed.

10 Ways To Use Checkboxes In Numbers

Comments: 12 Responses to “10 Ways To Use Checkboxes In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information