How to Use Excel Checkboxes | Interactive Checklists & Reports

today i'm going to show you how you can use checkboxes in excel to create checklists that look like this one adding a checkbox is very easy by using the outcome of the checkbox so whether the box is checked or not is also really easy once you understand how to use this one setting that i'm going to show you in a bit in case you'd like to improve your office skills make sure you're subscribed now let's get to it i want to add checkboxes right here so as i'm going through my learning list and i finish something i want to place a check mark in the check box and i want to cross this off my list first off let's add a check box to do that you need to go to the developer tab now if you don't see the developer tab because it's not there by default you need to right mouse click on your ribbon go to customize the ribbon under main tabs here you're going to see developer yours is not going to have a check mark beside it so you need to place a check mark and then click on ok and then you're going to see developer right here then go to insert and from form controls see the check box right here that's what we need click on it and then draw it out where you want to have it i want to have multiple check boxes in each cell i'm going to put mine right here then you can click inside it or you can also right mouse click on the check box and edit the text then you can type in what you want so i'm just going to remove this and put done you can also remove the text completely so you don't have any text in there now i'm able to place a check mark in here or uncheck this if i want to copy this down and apply it to these cells as well i can click on the cell itself okay so make sure it's a cell selected and then just drag this down until where you want to have it and now we have our multiple checkboxes if you just need to add simple checkboxes and you don't need to do anything with the result of the checkbox aside from seeing it visually then you're done but if you want something to happen when you place a check mark in the check box then you need this one setting that one setting is this so you need to right mouse click on the check box and go to format control there's also a shortcut key you can use it's control one that's going to bring you to this view under control we have this value part here but you can ignore that because that's just the current status of the check box mine is checked so it's showing me that it's checked you don't really need to touch this part what you need is actually this the cell link this is where you're going to put the result of the check box how does that look well we can put the result right here i personally like to put it on the same cell as my checkbox because this way i don't have to look for it but just so that we can see it better i'm going to put it on d2 and then click on ok take a look at this when it's checked we have true when it's unchecked we have false even if i go and delete this if i place the check mark here it comes back it tells me it's true if it's unchecked it's false now this is information i can use to control anything else i can use this in formulas i can use this in conditional formatting but here's the problem though if i right mouse click and go back to format control and let's actually change this to c so that it's placed under the checkbox if i do this and even if i take away these dollar signs here both of them or just the one for the two even if i do this and click on ok and now let's take a look so if i click away and click on this i get the true false here this is not controlled anymore by the cell so we can see true false it doesn't look so nice but don't worry we are going to make the font white we can make it go away the problem though is that if i copy this checkbox and i put it somewhere else then when i right mouse click and go to format control it's still pointing to c2 so these are not dynamic even though they look like dynamic references that unfortunately doesn't work so this means that we need to go through each checkbox here and add its own cell link so this one would be c3 and then let's actually use the shortcut key ctrl 1 this would be c4 c5 and finally c6 okay so i put all of these right below each checkbox and you can see that they say true let's change the color so that they're not visible anymore okay so now is the time to use that result to adjust the formatting so that every time i have a checkbox here and this underlying cell is saying true i want to have a strikethrough type of formatting that's conditional formatting i'm going to highlight this go to home conditional formatting and add a new rule that rule is going to use a formula the formula is actually very simple it's just going to look at this cell that has true false that was c2 right so let's see if i can actually click on it just carefully click to the side it picks it up now here i have to be careful because i don't want the formatting of the entire range to always just look at c2 i want it to be dynamic it should look at each individual row so i need to take away the dollar sign from the two but i can leave it on the column now let's go to format under font for color we can adjust this if we want as well we could make it a lighter gray and let's also have that strike through effect so then let's go with okay and okay now we can see this in action all tasks are done now in addition to this you can also use this result in formulas so let's say i want to have a list of outstanding tasks i can use the filter function to get that list my array or my tasks here for the include argument i want to include anything that's here that's false right so i'm going to select this range and take a look at whether it is false that's it close bracket press enter and i get everything that's unchecked this one joins the list it's there and everything works why is this working because it's looking at the underlying cell here and remember i changed the color to white so we can't see it if i turn it back we can see that these are true this one is false now you can of course put these on other cells right you can put it on this cell here but i just prefer to have it on the cell itself because otherwise i don't have to go and find it i know where it is that's also the default behavior in google sheets okay so let's turn these back to white as you can see adding checkboxes is really easy using the outcome of the checkbox to control the rest of your report is also very simple once you learn how to use the linked cell in your formulas i hope you found this tutorial useful do give it a thumbs up and subscribe if you aren't subscribed already i'm gonna see you in the next video you

Channel: Leila Gharani

Views: 182,921

Rating: 4.9708519 out of 5

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, excel checkbox in cell, excel checkbox formula, excel checkbox true false, excel checkbox column, excel checkbox conditional formatting, excel checkbox strikethrough, excel, excel tutorial, checkboxes, tutorial, excel check box, excel check-box

Id: Jj1iJSnXEG8

Length: 8min 1sec (481 seconds)

Published: Thu Jul 15 2021