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:
You can download the file here and follow along. When you get a preview, look for Download in the upper right hand corner.
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.
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 14 so I can see two weeks of data at a time. Then I set up a data range area to display those 14 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” in cell I1 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 “F” 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. You don’t need to enter anything here.
Minimum value is 1, the first row in your table.
Maximum value is set at 487. We have 500 rows in our data table and we want to display 14 at a time, so we take the maximum number and subtract the number displayed plus 1.
Increment change is how many rows will move with each click of the scroll bar arrows.
Page change is how many rows will change each time we click within the scroll bar, in our case 1.
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
All OFFSET does is return the value 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 D2 is =OFFSET(A1,$I$1,0). A1 is the top of the list of our 500 items. It is our reference point. $I$1 is the counter to indicate the number of rows the OFFSET function is to go down. Every click on the arrow of the scroll bar control will increase that number by 14, thus displaying the next group 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. You may need to format these cells as dates.
Now, enter this formula into cell E2: =OFFSET(B1,$I$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 14 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:
The chart will display the 14 values in the data range and dynamically change as you scroll through the data!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!