Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.
Neale Blackwood CPA:
Welcome to the Excel Tips podcast. My name is Neale Blackwood, and in this episode, we're going to look at a brand new feature called Checkboxes. Well, to be honest, there is a checkbox system already in Excel, and it's been there for decades, but unfortunately, it was part of the controls system in Excel, which is an advanced topic. The new checkboxes are in the Insert ribbon. Now, you do need the subscription version of Excel to access this. Inside the Insert ribbon, there's now a checkbox option. It's in-between illustrations and charts, and this is a really easy way to add checkboxes to your spreadsheets. Checkboxes allow you to let the user control certain aspects of your spreadsheets. In the companion video and the companion article, I covered two examples, one with a budget and the other with a format.
A checkbox sits above the cell and when you tick the checkbox, it puts the word, "True," into the cell. If you untick the checkbox, it puts the word, "False." Now, these words will appear in the formula bar. They don't appear in the cell itself. So there's a special format that's applied. So you can't see the word on the spreadsheet, but you can see the word in the formula bar. In Excel, both, "True," and, "False," are keywords in Excel. So if you type them into a formula, Excel will capitalise both of them.
Now, in Excel, "True," equals one and, "False," equals zero. This means when you multiply by a checkbox cell, if you multiply by one, which is true, then you don't affect the calculation. So the calculation is going to roll on the same, but if you multiply by a checkbox cell which has false in it, so it's unticked, then that's the same as multiplying by zero, which as you know, is going to zero the calculation.
So with a checkbox, you can control calculations, and that's the example that I used for a budget. So we have a budget model, and there was an inflation factor for sales and an inflation factor for the costs.
And in the companion video, I took you through the calculations on how you can change the calculation so that when the checkbox is ticked for the specific inflation, it will apply that inflation, and if it's unticked, it turns off the inflation. So it's a really easy technique to allow the user to control a calculation, to turn it off or turn it on.
And in this case, it allowed the user to be able to turn on the sales or the costs inflation independently, which is quite powerful. Now, there is a keyboard shortcut associated with the checkboxes. So if you've got a checkbox cell selected and you press the spacebar, that will tick or untick the checkbox. It basically switches the checkbox between either ticked or unticked, and that's the spacebar, and you can do that to multiple cells as well. So if you've got more than one checkbox selected, pressing the spacebar will change all of them.
Now, another key that affects the checkbox is the delete key. So if you've got a ticked checkbox and you press the delete key on that cell, what it does is it takes the tick off. But if you press it again, so if you've got an unticked checkbox and you press the delete key, it actually deletes the checkbox. Now, that could be a problem if you are using that checkbox as part of a formula. So in the companion video, I showed you a technique where you can just unlock the checkbox cells and then protect the sheet. And what that does is that allows the user to use the checkboxes, but when they press the delete key, it doesn't delete the checkboxes. So check out the companion video. It's on the last part of it that I show you how to add the protection to the sheet.
Checkboxes also work really well with the IF function. The IF function has three arguments. The first argument is what's called a logical test, and that's something that returns true or false. And as we know, the checkbox can return true or false. So you can just refer to the checkbox cell in the first argument of an IF function. And then the second argument is what you want to do if it's true, and then the third argument is what you want to do if it's false. So you can have the IF function that's driven by a checkbox cell. So it's really, really easy for the user to control certain calculations. So that was the budget example that allowed you to control the inflation.
The other example that I used in the companion video and article was being able to control a format. On to-do lists, I get a great deal of satisfaction putting a line through something that I've done. So we can mimic that in Excel. So there's a Strikethrough font format, which allows you to put a line through text. So you can still read the text, but it also puts a line through it. I used an example of some of the tasks that I have when I'm creating an article, and what I've done is in column A, I had the list of tasks, and in column B, I added the checkboxes. So what I wanted to do was to tick the checkbox and then have the text in column A have the Strikethrough format.
And so to do that, you select the text in column A. So the range that you want to put the Strikethrough in. And then I clicked on the conditional formats on the home ribbon, and I used a new rule, and then I used the last option in the top section, which is a formula. And then all I did was refer to the top left cell in the corresponding range. In this case, it was B2.
Now, when you do that... So it was equals B2. When you do that, you've got to make sure there's no dollar signs in that reference. When you're creating a range conditional format, you need to refer to the top left cell of the range that you are referring to. And also, it has to be what's called a relative reference, which means there's no dollar signs in that reference. Now, that's all you need to do. You just need to refer to it because that checkbox is going to have true or false in it.
And so if it's displaying true, it means the format is going to be applied. So with the conditional format, you click the Format button, go to the Font tab and just select the Strikethrough format, and that's it. Click, "Okay," a couple of times and then ticking the checkbox will put a line through the text in column A. So that's unusual for a conditional format because most conditional formats work on the range that the conditional format is in. But if you want to use another range for a conditional format, you have to use that formula technique that I just explained.
Now, something else that's exciting with the checkboxes is the fact that they work with formatted tables. I've covered formatted tables in the podcast previously. Formatted tables have got a lot of advantages. One of them is that they automatically extend when you add extra rows to the table. Now, that applies to the checkboxes. So in the example, I converted the list that I had into a formatted table and I added an extra step to update CPA Connect. And when I did that, the next line also had a checkbox automatically included, and the conditional format that I'd had on the previous row was also copied down to the new row.
So checkboxes work seamlessly with formatted tables. And so if you have a column of checkboxes in a formatted table, when you add a new entry to the bottom of the column, the checkboxes will automatically extend.
So checkboxes, it's a brand new feature. As I mentioned, you do need the subscription version of Excel to have access to them, but they're a really easy interface and allows you to turn things off and on in the spreadsheet, which makes the user's life a little bit easier. So hope you enjoyed learning about checkboxes, and thanks for listening.
Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. And we hope you can join us next time for another episode of Excel Tips.
