O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #53. Interact with Your Charts Using Custom Controls

To make your chart truly interactive, you can use one or more dynamic ranges in your chart and then use either a scrollbar or a drop-down list from the Forms toolbar to reveal the figures your readers want to peruse.

As you saw in the previous hack, you can use dynamic named ranges to add flexibility to your charts. But you also can use dynamic named ranges to create interfaces controlling which data the chart plots. By linking dynamic named ranges to custom controls, you enable users to change the chart data by using the control, which simultaneously will update the data in the worksheet or vice versa.

Using a Dynamic Named Range Linked to a Scrollbar

In this example, you will use a scrollbar to reveal monthly figures over a 12-month period. The scrollbar is used to alter the number of months reported. The scrollbar's value also is used in a dynamic range, which in turn is used as the data source of the chart.

To begin, set up some data similar to that shown in Figure 5-9.

Worksheet data for dynamic chart linked to scrollbar

Figure 5-9. Worksheet data for dynamic chart linked to scrollbar

Create a dynamic named range by selecting Insert Name Define and call it SALES_PERIOD. In the Refers To: box, type the following:

=OFFSET($B$5,0,0,$C$5,1)

By using the OFFSET function, you can use cell $C$5 to force the referenced range for SALES_PERIOD to expand both up and down as the ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required