Video: Using Controls to Change Chart Data
Posted on July 30, 2007One of the best things about Excel is its seemingly infinite flexibility. In this video I demonstrate how you can use controls from the Forms toolbar to change the data in charts. There are so many ways to use this technique for presentations that it was difficult to choose one. I decided to show a very simple example that compares some financial ratios for GE over time to the industry averages.
The basic idea is that you use a control (a drop-down list in this example) to choose which of several data series to display in the chart. As part of the formatting of the control, you will select a “cell link” where the control will communicate to you which item has been selected. I use this number in the Offset function to grab the data. When you select other items from the list, the chart updates automatically.
In this video I also demonstrate how to create a dynamic chart title. That is, the title of the chart will change to reflect the data that is charted.
I have two versions of the video: a larger, Flash-based version is hosted on my academic Web site, and I have another version posted on YouTube. The YouTube version is presented here. I will post a written tutorial on the Web site soon.
Comments:
Wow! Thanks for this video… I was asked to do exactly this function for a project I was working on, and you basically did the work for me! THANKS!
Fantastic! Thanks for taking the time and effort to create and post this video.
Next entry: Chart the Yield Curve Using Yahoo! Finance Data
Hi
Just want to thank you for putting that video on improved ways to present ratios/financial data. I found it really useful.
Many thanks
Cherie