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.
▶ You can also watch this video at YouTube.
▶
▶ Watch more videos about related subjects: Numbers (211 videos).
▶
▶ Watch more videos about related subjects: Numbers (211 videos).
Want to know more about how to use Numbers on your Mac?Video Summary
In This Tutorial
Ten ways to use checkboxes in Numbers, from marking a simple true or false value to sorting, filtering, counting, and driving formulas and displayed data based on whether boxes are checked.
Intro
- A cell can be formatted as a checkbox, which is functionally the same as holding the value True or False but shows a box that is either checked or empty instead of the words.
1. Represent a True or False Value
- Selecting cells and using Format, Cell to set the data format to Checkbox lets you mark whether something has been done, such as whether an order has been prepared, packaged, shipped, or received, with a checked box meaning True and an empty one False.
2. Sorting Rows
- You can sort a table by a checkbox column, ascending or descending, to group completed rows at the top or bottom, and re-sorting after checking boxes moves rows accordingly.
3. Consider Multiple Checkmarks
- With several checkbox columns you can add a column using COUNTIF over those cells to count how many are True per row, then sort by that count to put the most complete rows on top, optionally hiding the helper column and sorting by the hidden column.
4. Filter Rows
- A Quick Filter on a checkbox column can show only rows with False values, such as orders not yet received, and unlike sorting, filters update automatically so a row disappears as soon as its box is checked.
5. Counting Checkmarks
- Adding a Footer Row (via Format, Table) gives a place for table-wide calculations, where COUNTIF over the whole checkbox column counting True values tallies the number checked.
6. Percentage Based On Checkboxes
- Dividing the count of checked boxes by the total (using COUNTA to count non-empty cells) and formatting the result as a percentage gives a live completion percentage.
7. SUMIF Using Checkmarks
- SUMIF can total numbers only for rows where a checkbox is True, for example summing prices only for checked rows, with the total updating as boxes are checked or unchecked.
8. Formulas That Depend On Checkboxes
- An IF formula can control a calculation based on a checkbox, such as adding tax (price times 0.07) only when the box is True and adding nothing otherwise, then copying the formula down the rows.
9. Different Formulas Based On the Checkbox
- IF can also choose between two entirely different calculations depending on the checkbox, for example applying a 7 percent rate when True and a 3 percent rate when False.
10. Show Values Based On the Checkbox
- A checkbox can determine displayed data, such as calculating tax when True or zero otherwise, and a Custom Format on the cell can show nothing when the value is zero, so unchecked rows appear blank while the total still works out.
Summary
Checkboxes in Numbers provide a simple true or false value that can be used far beyond marking tasks done, serving as the basis for sorting, filtering, counting, percentages, conditional sums, and IF-driven formulas that change calculations or hide values, making them a flexible tool for organizing and computing on spreadsheet data.
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.
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?
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.
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.
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.
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.
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.
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.