Lucy asks how to make a chart interactively change from red to yellow to green based on an external selection. In today's Episode 776, we take a look at the basics of setting up such a chart. Tomorrow's episode will take a look at the Options button on the Form toolbar to enhance the chart.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, I have this really cool chart today sent in by Lucy.
Lucy needs to create this chart.
Now, basically, when we choose an option button, it will show just one series, and the interesting thing is the series changes color.
That makes it a little bit more difficult than most of these interactive charts that I've seen.
So, we're going to work on building this chart.
Let's go back here to just a blank worksheet where we have the original data set.
So, we have our data here with the three series.
I need to make a copy of this data.
Basically, I want to set up formulas that link back to this data, so if we change the data here, it'll change my secret copy, and so I select the data, I'm going to right-click on the border and drag down somewhere else, and choose LINK HERE.
Now, this only happens when we right click and drag, we get this new secret menu, LINK HERE, and basically we end up now with formulas that point back to the original cell.
So, if I would change something here, 1400, it'll automatically change in the bottom.
Okay, so, that's the first step.
Next, we're going to create a chart that is unlike the chart that Lucy actually wants, and this happens a lot with charting.
We head down the wrong path.
I'm going to go create a stacked area chart, click FINISH, and you'll see that we start out with not the right colors at all.
So, I'm going to format each one.
Let's see.
The OVER wants to be green, so I select that series and press CONTROL+1 for FORMAT CELLS and choose green.
The AVERAGE should be yellow.
I select that series, CONTROL+1, choose yellow, and then choose UNDER, and CONTROL+1, and choose red, and so we start out with a chart that has all three colors at once.
Now, I hate the gray background so I'm going to click on the plot area, FORMAT CELLS, and just change that back to a white.
Now, here's how we're going to make this work.
We're going to choose some cell, I'm just going to choose A7 here, and put the number 1 in.
That eventually is going to contain the numbers 1, 2, or 3, and I'll come down here and edit my formula, and say =IF cell A7 -- I want to put that in $ signs, $ sign A, $ sign 7 -- = 3, then I want to show the value from B3, otherwise I want to show an N/A. Now, usually, we hate the N/A errors but, in this case, we actually want it.
So, NA, () is the function that will return a true N/A error, and when I copy that throughout, basically we have N/As everywhere.
[ =IF($A$7=3,B3,NA()) ] Now, I'm going to edit the second formulas, the AVERAGE formulas.
Those all need to be 2, CONTROL+ENTER here to enter that formula in all 3 cells, and then, down here, the UNDERs, we only want to show the UNDER value when that is a 1, CONTROL+ENTER.
[ =IF($A$7=2,B4,NA()) ], [ =IF($A$7=1,B5,NA()) ] So, now what you'll see is that as I change these numbers here in A7 to 2 or to 3, I get a changing chart color each time.
Now, in tomorrow's netcast, we'll take a look at how to, instead of making them enter a number 1, 2, and 3, they setup option buttons that will allow them to just click the option button and have the chart change.
Thanks for stopping by.
We'll see you tomorrow for another netcast from MrExcel.
Well, I have this really cool chart today sent in by Lucy.
Lucy needs to create this chart.
Now, basically, when we choose an option button, it will show just one series, and the interesting thing is the series changes color.
That makes it a little bit more difficult than most of these interactive charts that I've seen.
So, we're going to work on building this chart.
Let's go back here to just a blank worksheet where we have the original data set.
So, we have our data here with the three series.
I need to make a copy of this data.
Basically, I want to set up formulas that link back to this data, so if we change the data here, it'll change my secret copy, and so I select the data, I'm going to right-click on the border and drag down somewhere else, and choose LINK HERE.
Now, this only happens when we right click and drag, we get this new secret menu, LINK HERE, and basically we end up now with formulas that point back to the original cell.
So, if I would change something here, 1400, it'll automatically change in the bottom.
Okay, so, that's the first step.
Next, we're going to create a chart that is unlike the chart that Lucy actually wants, and this happens a lot with charting.
We head down the wrong path.
I'm going to go create a stacked area chart, click FINISH, and you'll see that we start out with not the right colors at all.
So, I'm going to format each one.
Let's see.
The OVER wants to be green, so I select that series and press CONTROL+1 for FORMAT CELLS and choose green.
The AVERAGE should be yellow.
I select that series, CONTROL+1, choose yellow, and then choose UNDER, and CONTROL+1, and choose red, and so we start out with a chart that has all three colors at once.
Now, I hate the gray background so I'm going to click on the plot area, FORMAT CELLS, and just change that back to a white.
Now, here's how we're going to make this work.
We're going to choose some cell, I'm just going to choose A7 here, and put the number 1 in.
That eventually is going to contain the numbers 1, 2, or 3, and I'll come down here and edit my formula, and say =IF cell A7 -- I want to put that in $ signs, $ sign A, $ sign 7 -- = 3, then I want to show the value from B3, otherwise I want to show an N/A. Now, usually, we hate the N/A errors but, in this case, we actually want it.
So, NA, () is the function that will return a true N/A error, and when I copy that throughout, basically we have N/As everywhere.
[ =IF($A$7=3,B3,NA()) ] Now, I'm going to edit the second formulas, the AVERAGE formulas.
Those all need to be 2, CONTROL+ENTER here to enter that formula in all 3 cells, and then, down here, the UNDERs, we only want to show the UNDER value when that is a 1, CONTROL+ENTER.
[ =IF($A$7=2,B4,NA()) ], [ =IF($A$7=1,B5,NA()) ] So, now what you'll see is that as I change these numbers here in A7 to 2 or to 3, I get a changing chart color each time.
Now, in tomorrow's netcast, we'll take a look at how to, instead of making them enter a number 1, 2, and 3, they setup option buttons that will allow them to just click the option button and have the chart change.
Thanks for stopping by.
We'll see you tomorrow for another netcast from MrExcel.
