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
In this example I have a full years data displayed in the worksheet range $A1:$C53.
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:
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:
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 =
The x values or labels can simply be referenced from the y values:
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
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.