MrExcel's Learn Excel #776 - Interactive Chart

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 30, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top