Interactive Excel Check Boxes for Dynamic Reports

Excel check boxes are type of form control that can be added to a spreadsheet with just a few clicks to create an interactive list of items that can be checked off and also link them to formulas to dynamically turn off and on items you want displayed in a chart or conditional formatting and more let's take a look at a few ways we can use check boxes to make your spreadsheets more visually appealing and user-friendly one of the most common uses for check boxes is to create a task list that you can use to keep track of progress I created one for my son's morning routine before school because I am tired of reminding him what he needs to do each morning don't forget to feed Diego so I delegated it to excel check boxes are available from the developer tab of the ribbon and then insert and under form controls we've got checkbox don't confuse them with the ActiveX controls they work in quite a different way we're going to stick with the form control version for this lesson now if you don't have the developer tab you can right click on any tab of the ribbon and customize and then make sure the developer tab is selected on the right hand side and click OK and then it will be available to create the checklist I've entered the tasks in column B and then we're just going to insert the check boxes in column C I'm going to hold down alt while I draw in the checkbox that just snaps it to the size of the cell and makes it quick and easy to insert you can see it has a label now I could use that label as my task name but the font's quite small and you can't make it bigger so I'm just going to right click and edit the text shift and end to select it all and press delete and that gets rid of the text and then I'm just going to bring it over here and Center it in my cell roughly so there's my first text box inserted I need to copy it down so holding down Ctrl and shift just going to left click and drag and we'll copy it four times and then I'll just select them all and copy them again because I've got eight tasks now don't worry too much about the alignment just get the last one and the first one exactly where you want and then Ctrl a to select them all and then on the formatting tab we're going to align them and distribute them vertically that just centers them in each of the cells I can check the check boxes as they are but if I want to reference the status of the check boxes in a formula then I need to Output that information to a cell I can do that by right clicking the check box and then format control and then here I want to reference a cell in the cell link I'm going to just pop it in column F on the same row as the text box but you could put that on another sheet out of the way of your report for example so I'll click OK and then I just need to repeat it for each of the text boxes there's no shortcut so I'll fast forward while I do this so you don't get too bored now you won't get a result in the cell link until you check the check box for the first time and then it will record the status now if you perform a math operation on these true or false Boolean values you can see it converts it into the numeric equivalent of one or zero one for true zero for false and I can use this to add a progress complete status bar using a formula to count the completed tasks divided by the count of the total number of tasks so first of all we want to know how many tasks are completed so I can sum these now I need to convert these values into their numerical equivalents so I could do times one or I can use the double unary which is two dashes or two hyphens to coerce the values into their numeric equivalent and so you can see there we've counted five trues now I need to divide that by the total number so I can use count and again coerce them into their numeric equivalence to count the total number and we get the percentage complete of course I could use the count a function and not need to coerce it at all it's up to you right let's delete these I don't need that it'd be nice to have a visual representation of the progress so we can use conditional formatting to add a data bar and I'll just go in and manage the rule for that data bar double click on it and in the editing here I want to choose number starting at zero and the maximum number is one one hundred percent and that will just position the bar to illustrate 63 as a proportion of the cell rather than filling the whole cell and if we check them all then it fills the whole cell now I can take this one step further and offer some encouraging words to keep them motivated I can use the IF function to do that so starting with first of all have they started so is the progress zero if so we want to give them some encouragement to start and next we'll put in another if and here I want to see if they only have one task left so we use the sum with the double unary to count how many tasks they've completed so far and if that equals seven there are eight tasks in total so if they've done seven then they've only got one task left so we'll say great job only one task left and then I need another F to see if they've completed all the steps that is if this equals one then we can wish them a good day at school and if they haven't completed all the tasks then we need to know how many are left and we'll say keep going only and we want to count the number of tasks if so we're counting here we just want to count the false tasks that is the tasks that are left so how many tasks are left and some text that says tasks to go close parentheses on my ifs and I've got three of them press enter so you can see we've finished all the tasks we get the have a great day at school here we've got one task left here we've got two three four five six seven and we haven't even started and there you have an interactive checklist that leverages the Boolean values returned in the cell links with formulas to create a user-friendly experience let's look at another way to use check boxes Excel tables have banded rows applied to them by default but they can be slow if you're working with large amounts of data Maybe you simply don't want your data in a table but you'd still like the banded row effect in this case we can use conditional formatting to apply the bands and Link it to a checkbox that turns it off and on I'll insert a checkbox and this time I want to use the text so we're going to label this show formatting and we want to link it to a cell just pop it over here remember you can put this on a completely different sheet so now the check box is linked to the cell we can use this in our conditional formatting rule conditional formatting formulas must either return true or false the formats are applied when the formatting rule formula returns true let's write the formula for formatting then we'll link it to the checkbox we can use the subtotal function with 3 for counting and I'll reference the data in column A and I want it to start at A4 and I want to Absolute the first reference and then only absolute the column on the second reference so we're going to count incrementing one row at a time so as I drag it down you can see it's counting each row just keep in mind that if there are empty cells in the column you choose to count the results will be wonky so it's best a counter column you know will not have any empty cells the next part of the formula uses mod so let's wrap it in mod to calculate the remainder after dividing the subtotal by two so we'll pop that in there and then when we copy it down you notice it returns a series of ones and zeros remember one is the numerical equivalent to true and 0 is false and that means when we use these numbers in our conditional formatting rule this row is going to be formatted this row isn't this row is this row isn't and so on so that's the formula we can use for conditional formatting the banded rows now to link that format to a checkbox we simply wrap this formula in and so this is our first logical test and then we want another one that simply checks the status of the checkbox so our absolute reference that with F4 close parentheses so at the moment you're not going to see any difference other than instead of ones and zeros we get trues and falses because we've used and but if I turn the formatting off they're all false and on and so on so this is the rule I'm just going to highlight it in Ctrl C to copy it and then we're going to apply the conditional formatting so selecting the table I'm going to go to the Home tab conditional formatting new rule and here I want use a formula let's control V to paste it in and we'll format the fill in this gray color okay and okay and there's our formatting we don't need this formula here I just like to build my formulas in the grid so I can see the results are working out as I'd expect then I copy it into the conditional formatting manager and then you have turning formatting on and off with a checkbox checkboxes enable you to add interactive elements to a chart including removing and adding series at the click of a button you can see here the series are linked to this table and these formulas are linked to the cell link for each of the text boxes so let's go and set that up in the file we're working on we'll start by inserting the check boxes and we're just going to draw them up here on the chart I don't want the text so we'll just select that and delete it and let's just position it holding down control and shift I'm going to copy it across two more times and then let's right click and set up the cell links so this one is for average I'm just going to pop it above where I need to reference it so it's to hand of course this table would normally be on another sheet out of sight okay there's my check boxes and they're all linked to these cells next I just need to populate the formulas that are going to return the values that feed through to the Chart so the first one is average and we can just use if to see what the status is here so if it's true and we'll F4 to Absolute then I just want to pick up the average F4 to Absolute otherwise we want the N A error and we can use the n a function with no arguments just to an opening and closing parentheses and we're done press enter let's copy it down you can see it's now in the charts and it's referencing the average let's repeat that for the Target so again if this is true then and absolute then we want the target absolute otherwise n a error close parentheses and copy it down there's our Target now the trend is a little different because we need to use the trend function so if this is true F4 to Absolute then we want to find the trend of our sales close Trend otherwise we want to return the N A error times F and this is a dynamic array formula so it's spilled the results now if you don't have Dynamic arrays all you need to do is select the cells before typing in the formula and then Ctrl shift and enter to complete it and you'll notice in the formula bar it's got the curly braces around it to indicate that this is an array formula and there you have an interactive chart where you can turn on and off the series that you want to display so there you have three different uses for check boxes of course there are many ways to use them so let your imagination run wild you can download the Excel file containing the completed examples for this lesson from the link here if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching [Music]

Channel: MyOnlineTrainingHub

Views: 32,730

Keywords: excel check boxes, excel check list, check box form control, excel form controls

Id: nqfQQVoHT-4

Length: 13min 10sec (790 seconds)

Published: Thu Mar 30 2023