In this screencast, I'm going to show you an exciting way to make charts in Excel dynamic. What I mean by this, if we have a scatter plot here, I created this in a previous screencast. I can select using these checkmarks up here, I can select which of those series I would like to display on this chart. This is actually a dynamic chart. So I'm going to show you how to make this in this screencast. You also notice that I have this on what I call a dashboard. I'll be starting with this file called Dairy Products dynamic chart. It's essentially an intermediate in some of the work that I did in one of the previous screencasts. Let's go ahead and move this legend down here. The first thing I'm going to do in order to make this dynamic, I'm going to put this chart on a separate sheet. So I'm going to insert a new sheet over here. I'm going to rename this real quick. I'm going to rename this Dashboard because it's going to be our summary page. The other thing I'm going to do is you can go up here to View, and I'm going to de-select grid lines. So everything is white. It's a more professional style. I'm going to cut this, Control X, go to your Dashboard. I'm going to do Control V to paste it. I'm going to move this around a little bit. I'm going to remove this border by right-clicking format chart area and I can just say, no line for the border there. It's just a personal preference and I like that look. Let's go back to Sheet1. In order to make this dynamic, the first thing we need to do, we need to copy this information. So I'm going to do Control Shift down all the way to the bottom, Control copy, just making a clone of this and I'm just going to put it over here, Control V. The next step is to delete the contents of this chart. I'll show you why we're doing this here in a minute. But I'm going to do Control Shift down, press the delete key, or you can right-click clear contents, and I'm going to go back to the dashboard. What we need to do now is we need to add in these checkboxes. So if you don't have the Developer tab, like I don't at the moment, we can go to File, Options, Customize Ribbon, and I'm going to make sure Developer tab is selected and I can click OK. Now, we go up here to the Developer tab. I'm going to insert in these form controls, there's a checkbox. So this little green check. So let's left-click on that and then you can go over here an outline where it's going to go. You can left click in here and you can change. So this is going to be American Cheese. Then what I can do is I can right-click on that and we can copy, and I'm just going to paste three more of those, and I'm just going to spend a minute or two formatting these real quick. So I've got those labeled. Let me show you a trick. You can put the left one where you want it to be, the right one where you want it to be. Then I can hold down the Control key and I can left-click on these. If we go up here to the Shape Format menu, I'm going to align, I'm going to align them all to the top. So they're all top aligned. Then we go back to align and I'm going to do distribute horizontally. That just equally spaces them out. So that looks pretty nice. Now, what we need to do, these checkboxes, the way they work is you can toggle them, I can click them on and off. The output though, you need to specify an output cell and it's going to return either a true if it's selected or a false if it's not. We can right-click on this, and I can do Format Control and it asks us for this cell link. Now, I'm going to go here and click that arrow. I'm going to go back to Sheet1. For American cheese, I'm just going to use the first row. Which is going to be L1. So let's click OK down here. When I do this, if you select the check and we go back to Sheet1, you notice that it's put a TRUE into cell L1 because that was the cell that I linked it to. If I deselect this, American Cheese and I go back to Sheet1, you see that it's now FALSE. So we're going to use the state of the checkbox, either it's true or false, to govern what is going to be in this table and then we're going to link the information in this table back to our dashboard here to the data. So I'm going to right-click on ice cream, and I'm going to format this, cell link is going to be equal to M1 over here, and I'm going to do this real quick for yogurt and butter using the other two cells adjacent to those. So now, those four checkboxes are linked to cells L1 through O1. If I make changes here to the status of these checkboxes, then that automatically is updated over here on our Sheet1. Now what we're going to do is we're going to use the status of that checkbox, FALSE or TRUE to determine what is placed into these columns over here on the right. I'm going to put it simple if statement. If I can just say L1. I'm actually going to toggle this to be relative column, absolute row. So I can drag this down and the reference will always be row 1, and I can drag this over and it'll update to the column above it, to the row value up here above whichever we're looking at, ice cream, yogurt, or butter. So I'm going to do that. If that, and we can just say if that because if it's TRUE, then we have the second argument. If that's true, if it's been selected, we want to show American Cheese over here. So I'm going to select before. Otherwise, if that's not selected, we can use this function in Excel called NA. That just will give us the NA error there. I'll show you the importance of this here in a minute, but I can double-click that down. Because of the way I set this up, I can drag this over to apply that if statement. You see that because these three are true and the left one is false. We have the N/A for American Cheese, but we're showing the values there for the ice cream, yogurt, and butter. If I go back to the dashboard and I de-select yogurt. Now that is FALSE, N1 is FALSE, And we have the N/A. Let's apply this now to all of the different years. So I can double-click on this down. I've got all the information now that I need. I can go back to Dashboard and I can right-click on here, select Data. Now, it's just a matter of updating the reference is. For example, American Cheese I can edit that. I still want the same X values. So if we go over here to the X values, I still want those. Those are the same years and I haven't changed those. But now instead of the Y values being in column B here, I want to use column L. So I would go in here. I'm going to update this to L4 to L40, and I'm going to click OK. I'm going to do the same thing for ice cream, yogurt, and butter using columns M, N, and O respectively. So I've updated those references. So now instead of referring to the data on the left, I'm referring to the data on the right. Getting back to the reason why we put the N/A in here, any series that says N/A, any data within N/A, if you try to plot that on a plot, it's not going to plot it. So right now we have FALSE for American Cheese and yogurt. You notice here on the dashboard, because those are all N/As in those two columns, it's not plotting those. But if we go up and select American Cheese, then you see the data back on Sheet1 is no longer N/A because that's true. That's how we can tell Excel which the series to plot. So this is a really nice way to make a dynamic chart in Excel. I hope you enjoyed. Thanks for watching.
