Scroll Bar Chart

series-chart.jpg

This is a very neat advanced Microsoft Excel technique that enables the spreadsheet user to alter the visible range displayed. This can be achieved by defining the start point within the spreadsheet data series and also the number of data points to display in the Excel spreadsheet chart.

All the examples are available to examine in the Microsoft Excel example spreadsheet: Scroll Bar Chart

scroll-pic-a.jpg

In this example I have a full years data displayed in the worksheet range $A1:$C53.

scroll-pic-b.jpg
We need to create the two slide bars on the Excel worksheet.

Open the forms toolbar within Excel and select the scroll bar option and then drag out the form of a horizontal bar. You can move this around to finalise both size and positioning.

Right click over the selected scroll bar and choose Format Control.
In the box called Cell link write $L$19. This is the cell in which the incremental value of the slide bar will be recorded.
Repeat the process by drawing another scroll bar and assign its cell link to $L$21.

If you now move the arrows either side of the two scroll bars you will see that the values in the corresponding cell link will change. These will form the values for the height of our displayed values and the starting point respectively, and as such they will appear in our offset function defining the y values. To make these offset formulas easier to read I suggest we name the cell links.

To add a name to a specific cell or area – from the Insert menu, choose Name and then Define or use the keyboard shortcut CTRL F3. In the “Names in workbook” box type in a descriptive name for your cell, in this case I have used ZoomValue.
In the “Refers to” box we simply type $L$19.

Similarly for ScrollValue the name is defined as:

scroll-pic-c.jpg

We need to set a dynamic range for the x and y values. So, taking the y values as our base we can use the offset function to define the values to be plotted.

The OFFSET function is in the following format:

ref,rows,cols,height,width.

Choosing $C$2 as our reference cell, we now require a measure of our starting point (given by our SCROLL slide bar), the cols section = 0, the height relates to the number of rows of data to be charted and is returned by our ZOOM slide bar and the width = 1.

So define the named range =

OFFSETSheet1!$C$2,ScrollValue,0,ZoomValue,1)

The x values or labels can simply be referenced from the y values:
scroll-pic-d.jpg

When you create the graph you need to the dynamic ranges for the x and y values, so in this example the formulas would be:

x axis values = Sheet1!XValues
y axis values = Sheet1!YValues

xval-chart.jpg

series-chart.jpg

This is your dynamic chart completed. Try the arrows on both scroll bars to see how they alter the display.

You can hide your data table so long as you remember to change the chart options so that hidden cells are plotted and the cell links can be formatted so they too are not visible.

See the Microsoft Excel spreadsheet example Scroll Bar Chart.

You may also like

  • http://N/A Bernie

    I have used the techniques described here to create my first “Scroll Bar Chart”. It’s a wonder to see the chart in this dynamic way, rather than the normal static way.

    I had some difficulties getting my chart to work, especially when looking at multiple data types (High and Low temperatures).

    The “trick” here is to define the data types, each as a series. Once you pickup on that concept, then the example “Scroll Bar Chart” provided on this blog should make for easy dynamic charting.

    I am honored to post the first response to Angela’s new blog. Thanks Angela…