A scrolling chart is a useful to display a long timeline of data.
For example, trying to view the sales for a whole year might not fit well on the screen, and the chart can become cramped and hard to read. A scrolling chart allows a smaller population of data to be displayed at once, but retains the ability to move through a longer time period. This can be done by using a scroll bar, and without having to amend any of the underlying data.
How to Create the Chart
This process requires several components:
Original data – this is all the data to be displayed for all the periods, in this example, this data is located in cells A1:B60, this contains the sales for the first two months of the year.
Chart table – decide how many dates do to display at the same time in the chart. This dataset has 59 days, but we can set the chart to only display 20 rows at once. Therefore this table needs to be 20 rows high.
A scroll bar can be used to select which 20 days to display in the chart. Several steps are required to prepare the data for the scroll bar.
In D1, set the starting row for the chart, by entering a 0. A 0 will indicate to Excel to display 20 rows from the first data available in the chart. Similarly enter a 1 to see 20 dates from the 2nd date, 6 from the 7th date and so on.
Now use the Offset formula to create the data for our chart based on the value of this cell:
Enter in cell F2 (the first cell of the data for the chart) the following formula:
The Offset function will return the value of a cell, in reference to another cell.
The arguments that this function requires are:
Reference- this is the cell reference to get the value of another cell, A2 in this example
Rows- specify how many rows, up or down in relationship to the reference cell (A2), the required value is. Here we will enter cell D1 (using absolute reference as this cell won’t change when we drag the formula down)
Columns- enter 0 as the column does not need to be offset from A2
The formula is displaying the cell value that is 0 rows below cell A2 (this is the value of A2 itself)
Change the value in cell D1 to 1 to display the value of the cell one row below cell A2
Change the value in cell D1 to 6 display the value of the cell 6 rows below cell A2:
Drag (or copy and paste) the formula down to the end of your chart data as well as on the right to get the sales not only the dates. The table should look like this:
Chart – now insert a chart that will use this table as the data source.
Try and change the cell D1 value to change the date range displayed in your chart.
A scroll bar – Instead of changing the value of cell D1 manually, a scroll bar can be added for ease of use. To add a scroll bar go to : Developer in the toolbar, Click Insert in the Controls section and click on the Scroll Bar:
Once you click, you should be able to draw a rectangle.
To customise the scroll bar, right click on the scroll bar and click on Format Control:
In the Format Control window define:
Current value - leave this as a 0, it’s the initial position of the scroll bar
Minimum value – this is the lower position of the scroll bar, so if the scroll bar is fully on the left, what value to display. For this example, this is 0, the very first value
Maximum value – this is the upper value of the scroll bar. Remember the scroll bar (and the value of cell D1) will decide the first position of the data that to display. In this example there’s a total of 59 rows of data, and the chart displays 20 values at a certain time, so the starting position for the highest range that to display is 39 (59 less 20 rows).
Incremental value – this will define how many values to move every time the arrows left or right are clicked. Enter one, so each click on the arrows left or right of the scroll bar will move one date at a time.
Page change – to move the data with the scroll bar either click on the arrows left or right, or actually click in the scroll bar itself. The incremental value sets the change for arrow clicks, but this Page change variable sets the change for each click on the scrollbar itself. This can be a larger number, 10 for example.
Cell link – enter D1, so the scroll bar will automatically change the value of cell D1, thus changing the data of the chart.
If you right click on the scroll bar, it will enter the edit mode so that it can be drag positioned on the sheet. Position your scroll bar under the chart and resize to the chart width
We are all set!