See all How-To Articles

How to Create a Slider Bar (Scroll Bar) in Excel

This tutorial demonstrates how to create a slider bar in Excel.

 

create scroll bar final data

 

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.

 

create scroll bar initial 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.

  1. In the Ribbon, go to Developer > Insert > Scroll bar (in Form Controls).

 

create scroll bar 1

 

Note: Add the Developer tab to the Ribbon if you don’t already have it.

  1. Click in the worksheet where you want to insert a scroll bar.

 

create scroll bar 2

 

  1. Once you inserted the scroll bar, resize it to fit Column F, 10 rows.

 

create scroll bar 3

 

  1. (1) Right-click the scroll bar, and (2) choose Format Control…

 

create scroll bar 4

 

  1. 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).

 

create scroll bar 5

 

  1. Now format the header of the new table (G1:I1), just like in the source dataset. In cell G2, enter the formula:
=OFFSET(B1,$F$1,0)

 

create scroll bar 6

 

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.

  1. Now expand the formula to Columns H and I, and through Row 11 in order to populate all data from the initial data set.

 

create scroll bar 7

 

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:

 

create scroll bar 8

See all How-To Articles