Scroll Bar Chart

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.

Dynamic Chart

This Microsoft Excel tutorial, shows an example of how to create a dynamic chart by moving the charting range as data is added to the spreadsheet.
Here we start with monthly data for Retailer A, showing Petcare sales complete up to July.

pic-a.jpg

A standard Excel line chart of this data would be shown thus:

pic-b.jpg

To reduce the displayed area to the existing data within the actual column, while allowing it to expand automatically as more months figures are added, we use defined names.

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 chart data, in this case I have used ChartActual.
In the “Refers to” box we use the offset function to define the data within column C.

pic-c.jpg

The offset function is written in the following format:

=OFFSET(Ref,rows,column,height,width)

The COUNTA function is the vital part that makes the range responsive to the number of cell entries in the Actual column. This function returns the number of non-blank cells in a range. It is written in the height section of the OFFSET function and therefore determines the number of rows that will make up the name ChartActual.

In this example the reference is $C$1 which corresponds to the “Actuals” title, the resulting area starts 1 row down and 0 columns across. The number of rows corresponds to the number of entries in column C less 1 for the title entry and the total column width is 1.

The same procedure is now used to define names for the ChartLabels and ChartTargets.

pic-d.jpg

The OFFSET function is again used, this time referencing the newly defined ChartActuals. So ChartTarget starts on the same row as ChartActuals but is 1 column to the left of it. When the height and width component of the OFFSET function is not stated the height and width will be the same as the reference.

pic-e.jpg

The final stage involves changing the graphs data source.
Right click within the graph and choose Source Data.

pic-f.jpg

Select Actual in the Series box and in the Values box type in the newly defined name, in this case Sheet1!ChartActuals.
Perform the same action for Target and use Sheet1!ChartLabels to define the Category (X) axis labels.
Click OK and the chart should now look like this but is in addition fully responsive to new entries.

pic-g.jpg