Create an Interactive Chart with Checkboxes in Microsoft Excel

- Hi there. Jamie here today at Teacher's Tech. Hope you're having a great day today. Today I wanna show you how you can use check boxes to create an interactive chart in Microsoft Excel. So what I mean is, if I go ahead to over here if I go and click one on, you can see then that is represented in the chart. I can turn it off very quickly. Add another one or turn another one off to compare. So the chart is gonna dynamically update as I choose what check mark I want. So that's what I wanna show you how to do today on Teacher's Tech. If you would like to follow along with today's tutorial I'll put a link to this workbook down below in the description. You can just click on it and then just go file and save as a copy. The other thing I want to point out is this video comes in multiple languages where you can make the selection down below in settings on the YouTube video and go to audio and you'll see what languages are available. All right, let's get started with how to insert check boxes into Microsoft Excel.

I'm gonna be using sheet two on the tab here when I go through it but I'll leave sheet one with all this information. So you can take a look at it and play with it before you even get started. All right. So on sheet two, you can see we have the same information. The first thing we're gonna need to do is add our check boxes to Microsoft Excel. Now we have to do something before we can get started with this. And that is add another tab, a developer tab up top. So if you kind of hover over it here, if I right click when I'm up here in the menu, you get these options. So I'm gonna go ahead and I'm gonna go to customize the ribbon. And at this point, if you take a look down on the right hand side, you can see developer right here and by default, it's not checked. So I'm gonna select that, hit okay, and now I have another tab called developer

and this is where you're gonna find the check boxes. So what I'm gonna do is now go to the developer and go to insert right here. So if I go and choose insert, the text box is gonna be right here. So I'm gonna just click on it. And where do I want to have it placed? Well, I'm gonna go ahead and just put it right here and I'm gonna make it a little larger 'cause you can see that there's text already automatically into this and you can change this.

Notice if I go to try to click on it I can click it on off the check, but now I can't move it. If I wanna move it again, you can simply right click and then you get the handles back and you can see you can have them move one to bring it to where you want. The other thing I want to point out right away is I don't want it to say check box one. I just want it to be empty here. I just want the check. So I'm just, I can go in here. As soon as you go in here, you can highlight it, and just like other text boxes you're working with. Go ahead and delete it from here, and I can go and make this smaller, just like this. Now if I click off of it, notice I can't move it again over. Now the other thing that I need to have done here is, it doesn't do anything yet because I haven't told it, I haven't set to what this means when I check it on or off. And I do wanna point out when you right click on this again you can see you get different options from your cut copy paste. If I wanted to do multiple, but I'll show you a different way you can do this in a moment. But if I go down to format control here and you can see

we do have color and lines and size protection and all these I'm not gonna go over these things here in today's tutorial. But I do want you to go to control and make a couple changes. Because if we go to this here, I need this to be under checked and it needs to be linked to a cell somewhere. So I'm gonna just select here and I'm gonna select right on this one right here and just go back and you can see now I have that cell and hit, okay. So if I click off of it now, that means when it's checked, it's true. So if unclick it, now it says false. So this is important because this is what I'm gonna be connecting to. Now in my demo over here, if I go back to sheet one, you can see I had four different ones and notice it doesn't say false or true anywhere. And I'm gonna show you where I hid those. So I'm gonna go back to sheet two.

Now what we can do at this point here. Now you could go, you can do your control C control V and copy it down. Or you could go insert another check box. What you can do though, is if you go to it once you click off of it and you have it in place here, I can just use my normal drag down to paste or to copy them down. So if I want, I need to have four different ones. There's my four there, but I do need to set each one up. So right now these three check boxes are connected to this one right here. I need to each of them have their own cell. So if I go to this one, and I'm gonna just right click and format control. So I'm just gonna make a quick change this one and I'm gonna hit, enter and hit okay. And I'm gonna go to this one now, I'm gonna click on this one, right click and format it. And again, change it to this spot here. I'm just gonna hit okay. Oh, that one didn't go, there it goes. And then, so this last one right here and format control. So now I have them all that are gonna be all connected to their own spots here. So now you can see that they're independent of each other. So that's the first step to get those false and true to come up and then we can start to go and create what the next step would be for this interactive map.

So the first step I'm gonna do here is actually just copy down this table that I have here of this information. I'm just gonna go control C and go to this spot and control V, and then I'm gonna go ahead and just delete this information because this is where I'm gonna be putting my if statement here to say, "If this is chosen, so if this becomes true, then give me back the Netflix information. " So if Disney is true, then it's gonna be bringing me back the Disney information.

So what we're gonna do is start our if statement and I'm just gonna put equals and type if, and you can see, "Checks whether a condition is met and returns one value if true and another if false. " So if it's false, it's just gonna say false here and won't bring back any information. Now I do have a different tutorial all about if statements, I'll put a link to that down below in the description as well, and then in the card. So I'm gonna choose this if right here and the logical test. So it's gonna be this first spot right here. So B12 is where the information from Netflix is gonna come out. Now I'm not gonna make this an absolute here 'cause I am gonna copy this down. But if you didn't have it in a row, if you didn't wanna copy things down you might wanna make this absolute. What I mean is use the dollar signs. If you don't want it to be moving when you're copying things on it. So, but I'm leaving this as this. I'm gonna put my comma. And so yeah, if the value is true, what do I want to have happen? Well, I want it to bring back this range right through here. So just not one, but the range I want it to bring back. So I'm gonna hit enter. So it says false, false is here. I'm gonna check it. And now that it brings it back just like this. So that's what I want. Now, since I didn't make that absolute, I'm gonna copy just this first thing down. So I'm gonna just grab the corner and bring it down like this and notice it says, "False, false, false. " Let's see if it works, let's click this one. And it brings me back to YouTube. And we can check to see if that's correct here. Yes. And we can bring back up here. That looks correct. And it's copying it down. So this information is where we're gonna be creating our chart from which makes it interactive when we choose the check box that we wish.

Now I'm gonna keep our chart pretty simple here today. But if you wanna learn more about charts in Microsoft Excel, I'll put a link to one of my videos where I explained different types of charts and how to customize them. But for our purposes here today, this is gonna be the information that we're gonna chart. So I'm just gonna select this range in here. And if we're under insert, you can see that we do have recommended charts. So I could go ahead and click on this and it comes up. You could use a different chart than the one I showed you. You could pick one, I'm just gonna go to all charts and I'm gonna use a combo one here. So combo is at the very beginning, and you'll be able to see now, some of these are clustered column. I'm gonna choose each of these to a column or sorry, a line with markers. So I'm just gonna go through and change each of these like this. So I'm gonna have four different ones that when they show up, and I'm just gonna go ahead and hit okay right now and move this over closer. And let's just check it out. There's other things that we can customize this with but let's just check out the check marks here and you can see now, if I say true here, now I'll turn it off, and Disney disappears. Turn it on again. And it goes on. So it quickly updates based on this, because this if statement here is gonna be connected to these true and falses that you choose. Now, if you want to customize your different charts, you can go through and choose different ones.

So if you do want to have your access titles, you can go through and I knew this is I had this in billions here. So I'm just gonna put this as billions here, just like so, and you can give it a chart title. So this I'm just gonna grab my up here, grab this information from the title here and just copy and put it over here. And I'm gonna paste it in here, just like this. So there we have a title. We have a little bit of axis title here. We don't want that. I could put quarters if I want to go ahead and add that, I'm gonna hit delete, just so I have a little bit more room, we can go through and make some changes to this. You can see how quickly you can make some changes. I believe I was using the black one like this a little bit different with the color lines than what I had on the first page, the sheet one, but you can kind of go through and choose which ones. Now I could go ahead and just write these names down here but I wanna go and get the graphic. So I just wanna change the size of these real quick. So I'm just gonna change these rows the height to a different one, the row height, I believe it's 25, should work. And you notice that the check marks just kind of stay in place, so I have to right click on them. And now I'm just gonna line them up a bit more, so right click and bring them down to get them in the right spot. One more here. And I'll spring this one down like so, and this one will be the last one that I go through. So we have these here. Let's go get some of these. So what you can do to select all of these at once, you can go and get the if there was different graphics that you're using you can copy paste them from somewhere or insert them. But I'm gonna just take from here. So I'm gonna just hold down shift and select each one here. And you can see that they get highlighted as I select all of them. I'm just gonna go control C and go over to sheet two. And I'm just gonna bring them back over to here and kind of line them up. So this kind of works like they're in the right spot. The other thing I have said I was gonna mention to you is we don't wanna see the true here. We can see everything's working now. Everything's interactive as I click on different ones, you're getting that dynamic change to it.

But if we don't want see these a quick way what you can do is if I go ahead and just change the color of this font. So if I'm just gonna highlight these, go up to font color, and I'm gonna make it white. It's still there. But what I can do at this point if I go ahead and just right click, I can bring this cell right over top now. So it's gonna be over top of where I place the cell that it's connected to, so I can bring them back over here and we'll just bring this last one back over. And now everything will still work. As I click on it, it's still interactive. It's being hidden underneath. Now I could have put that true and falses to a different place, and then even chose to hide a certain column if I wanted to do from that. But in this case, I just changed it to the white font and put them in the exact same place. Now there's a lot of different things you can do with the check boxes. This is just one example. I'd really be curious to know what other things you're trying to learn with the check boxes or Microsoft Excel. Let me know in the comments down below. Thanks for watching this week on Teacher's Tech, I'll see you next time with more tech tips and tutorials.