Tip #100! Scroll Bar With Scrolling Chart!

In order to use the scroll bar control, you must first add the Developer tab to Excel.  Go here to learn how to do that.  It’s quite simple. 

Let’s say you have a large amount of data and want to be able to “scroll” through it without having to page down dozens of times.  Maybe you also want a chart that dynamically adjusts as you scroll through the data.  Excel can do that! 

Here is a list of 500 dates and sales for each day: 

The first step is to decide how many rows of data you want to see at a time.  I’ve chosen 10.  Then I set up a data range area to display those 10 values: 

I also need to set up a cell that will provide the counter for our OFFSET function we will use (more on OFFSET later): 

I’ve entered a “1” to indicate the number of rows down from our reference point that the scrolling data will display.  This will make more sense in a minute.

Now we need to add the scroll bar control.  Click on the Developer tab, then from the Insert option of the Controls group, select the scroll bar control: 

Now, in the column “E” next to the small table we created, click and drag your cursor to insert the scrolling control bar.  If you hold down the Alt key while you do this, it will auto-fit nicely within the column:

We’re only a couple steps away, so hang in there!  Next we need to enter the correct information into the Format Control for the scroll bar.  Right click on the scroll bar and select Format Control: 

A pop-up will appear.  Enter the values as I have here: 

Here is what this all means:
Current value is the starting point for your scrolling control.  
Minimum value is 1, the first row in your table.
Maximum value is set at 490.  We have 500 rows in our data table and we want to display 10 at a time, so we take the maximum number and subtract the number displayed.
Increment change is how many rows will move with each click of the scroll bar.
Page change is how many rows display at a time, in our case 10.
Cell link will show what row is at the top of our list, which is the cell we set up as our starting point.  That cell will have a dual purpose with our OFFSET function. 

Now we need to enter our formulas in the list area.  We will use the OFFSET function.  The syntax for the OFFSET function is =OFFSET(reference,rows,cols,[height],[width]).  All OFFSET does is return the values of a cell location based on the directions you give it.  Reference is your starting point.  Rows is how many rows down the function is to go.  Cols is how many columns over it is to go.  Height and width are in brackets since they are optional and we won’t be using them here.

So, our OFFSET formula that we will enter in cell F2 is =OFFSET(A1,$J$1,0).  A1 is the top of the list of our 500 items.  It is our reference point.  $J$1 is the counter to indicate the number of rows the OFFSET function is to go down.  Every click of the scroll bar control will increase that number by 1, thus displaying the next row down in the list.  The 0 tells the function to move zero columns over.  Note that we made the counter cell location an absolute value by including a $ in front of both the row and column reference.  To learn more about absolute vs. relative values, click here.

Now, enter this formula into cell G2: =OFFSET(B1,$J$1,0):

Notice how it is pulling the data from the original 500 row chart.  Now copy those cells down to fill up the rest of the 10 rows.  You can now scroll down using the scroll bar control and the data will change as you click: 

Finally, highlight the small data range and insert a chart.  To do that in 1 click, see my post here

The chart will display the 10 values in the data range and dynamically change as you scroll through the data!  Nice!

I hope you find many uses for this feature. 

Happy Excelling! 

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply