This tutorial demonstrates how to create a slider bar in Excel.
Create a Scroll Bar
Apart from the built-in scroll bar in Excel, which allows you to scroll the whole worksheet, you can also create your own scroll bar that will scroll a range of cells you want. This can be very useful when you have large sets of data and want to display only a fixed number of rows, and scroll through data. Say you have the following data set containing sales data.
Now you can create a separate range of the same data that will display only 10 rows, and update displayed rows as you scroll up or down.
- In the Ribbon, go to Developer > Insert > Scroll bar (in Form Controls).
Note: Add the Developer tab to the Ribbon if you don’t already have it.
- Click in the worksheet where you want to insert a scroll bar.
- Once you inserted the scroll bar, resize it to fit Column F, 10 rows.
- (1) Right-click the scroll bar, and (2) choose Format Control…
- In the Format Object window, set 1 for Current value and Minimum value, and 19 for Maximum value. Incremental change should be 1, while page change is 5. For cell link set F1. In this cell, you will get the value of the scroll bar (as a user scrolls it).
- Now format the header of the new table (G1:I1), just like in the source dataset. In cell G2, enter the formula:
The OFFSET Function will take a value that is one row under B1 (because the value in F1 is 1). When a user scrolls down once the scroll bar, the value in F1 will be 2, and the function will return the value 2 rows under B1, etc.
- Now expand the formula to Columns H and I, and through Row 11 in order to populate all data from the initial data set.
As you can see, the scroll bar is in the initial position (1 in F1) and 2–11 from the source data set are displayed. If you now scroll the slider 2 times down, Rows 4–13 are displayed: