The dynamic scroll bar belongs to Form Controls. With its use we open up many great opportunities in Excel. Its biggest advantage appears when we work with a large data table. With its help we will be able to manage any size lists in a small space.
Sounds good, isn’t it?
Do you still remember the rule of the one-page excel dashboard? Very important to work in a space-saving mode. With this method we gain free spaces which are very valuable.
The advantage of this that we’ll have more opportunities to use great charts! In today’s step-by-step tutorial, we’ll show you how to manage a large dataset easily.
Create Dynamic Scroll Bar in Excel
For today’s tutorial we set up a larger list.
The list contains 30 elements. Our task is to create a list that only contains 10 elements and in on interactive mode.
The trick is that with the use of the dynamic scroll bar we can display all values in this window. The end result will look like this:
Follow these steps to create an interactive control
1. First let’s look at the starting data table.
Place a list containing 30 elements to any worksheet. The list is a simplified sales database.
The table contains the following fields:
Regions: the name of the place where the given manager works.
Manager: the name of the managers, we filled the columns with names.
Sales: marks the income that was generated by the given managers.
2. Go to Developer Tab –> Insert –> Scroll Bar (Form Control).
3. Click on Scroll Bar button. You can find it on Form Controls section.
Now click anywhere on your worksheet.
This will insert a Scroll Bar in the active Excel worksheet.
4. Right click on the control.
Click on ‘Format Control’. This will open a Format Control dialogue box.
5. In Format Control dialogue box go to ‘Control’ tab, and make the following changes:
• Current Value: 1
• Minimum Value: 1
• Maximum Value: 21
Explanation: The maximum value is 21, because we will display 10 elements in the list. You can get the idea of this form the example that if the value of the N5 cell is 21, than the displayed values in the list will be between 21 and 30. This is exactly 10 rows that we have planned to begin with.
• Incremental Change: 1
• Page Change: 5
• Cell Link: $N$5
Explanation: We should talk a little bit about the N5 cell! We have linked this cell to the scroll bar. The value of the cell will vary between 1 and 21. Its role is important, because this will be one of the parameters of the OFFSET function. We will fill out the now empty table with its help.
6. Set the size of the scroll bar and place it on the right hand side of the table. On the picture below you can see what the table looks like after the procedure.
Next write the following into the H5 cell:
As a result we get the first element of the “Regions” column.
Finally copy this formula into all of the empty cells! And we are done with the dynamic scroll bar and table. You can see this on the next picture:
What will make the list dynamic?
The OFFSET formula uses the D4 cell of the original list as a benchmark. With the help of the N5 cell determines the appropriate position. But what is the appropriate position?
In the N5 cell (as we mentioned before) the values vary between 1 and 21. If we move the scroll bar up and down, than the value of the N5 cell will change.
The operation of the =OFFSET(D4;$N$5;0) formula:
We use the D4 cell as a base, for benchmark. Currently the value of the N5 cell is 1. This value will go into the H5 cell, that is 1 cell away downward from the D4 cell.
How the OFFSET formula works in Excel?
Now let’s move the scroll bar, so that the tenth element of the original list be the first element of the new list!
Probably this is too hard to imagine at first, so take a look at the picture below and everything will be clear:
Let’s see what has changed!
The value of the H5 cell is determined by the following formula:
How can this be when the formula didn’t change?
The formula did not change indeed, but the value of the N5 cell did. Because of the downward movement of the scroll bar its value has changed from 1 to 10. How did this effect the end result?
We need a little explanation here! The starting point is again the D4 cell. The value of it is 10. As the effect of the OFFSET formula starting from the D4 cell we jump exactly 10 rows downward. Because of this the first position of the new list will be the tenth element of the old list.
We have tried to portray this on the next picture, after all most of us rather a visual type.
The Form Controls group supports many useful tools in Excel. Read our another OFFSET tutorial too!
Very good example of this is the UserForm introduced in our previous article where we demonstrated the possibilities of data entry.
Stay with us in the future, soon you can meet with new Excel tips and tricks! You can try the operation of the operation of the scroll bar with the help of the downloadable workbook.