Setting up the data validation

This is one of these funny features in Excel that when you copy and paste the chart and then if you change the data series that the formatting reversed back to the standard formatting. So we are going to delete that and I’m going to show you how to get over this problem. If you go to file and options and in options, if you go to advanced. Now in advanced you want to scroll down to charts and under charts, we have this properties. Follow chart data points for current workbook. We want to unselect this. Now when we unselect this, I’m gonna go back and I’m going to copy this chart again. We will paste this chart.

I’m going to go straight ahead and change the data series in this chart. So we’re going to change our division 1 actual to be our division to actual in our module series named, we are going to reference the sheet, delete everything back to the apostrophe and put in and division to actual values. Then we can do the same for our budgets. We go in and we will select our budget as our header will then select the worksheet for our budget value and will delete back to get to the apostrophe and enter in our budgets and say. Okay. Now we can see that the chart formatting that we applied earlier on has remained in place.

We do however need to go back in and make the other changes we had made earlier which was changing the gradient fill from a blue to a yellow. And we also want to go in here to our heading and change our heading to division two. Now, we’re going to take a another copy of this because we need to have three charts. We need to have on for division 1, division 2, and then also for the total so we can change our title in here to just actual versus budget expenses. We can then go ahead and change our formatting of these data series as well. So we’re going to color this one in, in a gray color.

So we’ll select this gray here and then we need to change the data points on this. So we select our data, division 2 to actual, we are going to change to our total actual we can then select our sheet as well and select our total actual as our heading F3 to get into our names and then we can paste in our total actual formula and then we can do the same for our division 2 budget. So we’re going to rename this to the total budget. We can then select the sheet for the total budgets, just to get the sheet name F3 and add in paste in our formula.

So now we have three charts, we have a chart for our division 1, actual we were charged for our division 2 actual and we have a chart for our total actual. The next step is to create a drop-down so we can select between these different charts. Now I’ve created a lookup table over here and I’ll explain this lookup table to you in a moment. But the names of the charts that we want people to use in the drop-down included in this. So we’ve got division 1, division 2 and total so we’re going to come over here. And we are going to insert, we are going to go to data.

And we are going to go to data validation, I’m going to change our data validation to a list, and we are going to select in our source are fret user friendly names. Now you’ll understand this in a couple of minutes, these are our user-friendly names that we want people to use on our dashboard. So we don’t want to have anything that doesn’t make sense in here. We need to have these labels that people understand. So I’ve added in our data validation so we can now select between these different dropdowns. Now I’m going to slightly format that cell so if we go to our home ribbon we can change this to an input cell.

If we want and over here we can say select chart. So now we have this drop-down that we need to connect to the charts so we can view different chart. [END]