How to Use a Scroll Bar in Excel

Using a scroll bar with an Excel list is an extremely useful feature when you have limited space to play with. A popular example would be when creating dashboards in Excel (Learn how to create dashboards in Excel).

In a dashboard you want to try to display as much information as possible in a small amount of space. By using a scroll bar in Excel you can compress thousands of rows into 5, 10, 15 rows giving you more space, and allowing the user to still navigate the list.

Scroll Bar in an Excel list
Scroll Bar in an Excel list

Set up the Workbook

In the example used in this tutorial the workbook has 3 sheets. One for the list, one for the dashboard, and one for calculations.

Download the scrollbar.xls file used in this tutorial.

The Calculation sheet contains the start point in the list. For the start point enter the number 1 to start at the first record. This will later be linked with the scroll bar so that when you scroll the list changes.

Insert the Scroll Bar

To insert a scroll bar onto an Excel worksheet you need to have the Developer tab on the Ribbon. If you cannot see this tab, follow the instructions below:

Excel 2007

  1. Click the Office button
  2. Click the Excel Options button
  3. Check the Show the Developer tab on the Ribbon box
  4. Click Ok

Excel 2010

  1. Right click anywhere on the Ribbon and select Customize the Ribbon
  2. Check the Developer box in the list on the right of the window
  3. Click Ok

Insert the Scroll Bar

  1. Select the Developer tab on the Ribbon
  2. Click the Insert button in the Controls group
  3. Click the Scroll Bar (Form Control) button
  4. Draw the scroll bar onto the worksheet. The scroll bar can be moved and resized later so don’t worry about being too accurate

Insert a Scroll Bar in Excel
Insert a Scroll Bar in Excel

Format the Scroll Bar

The scroll bar needs to be linked to the cell on the Calculation sheet to keep a track of where the list on the Dashboard sheet should start.

  1. Right mouse click on the scroll bar and select Format Control from the shortcut menu
  2. Select the Control tab
  3. Enter last record for the end point in the Maximum value box. In this example 791 has been entered. This is because there are 800 records and I am showing 10 records at a time in the scrollable list. The first record when you get to the last 10 will therefore be 791
  4. Click in the Cell link box and select the cell on the Calculation sheet being used to store the start point in the list
  5. Click Ok

Link the scroll bar to a cell
Link the scroll bar to a cell

Write the Offset Function

The Offset function will be used in the cells containing the list on the Dashboard sheet. The Offset function will return the required data from the List sheet depending on the position in the scroll bar. Below is the formula used in cell B3.

=OFFSET(List!A1,Calculation!$C$3,0,1,1)

List!A1 – Starting from the first row of data on the List sheet

Calculation!$C$3 – The number of rows down to the cell containing the data to return. Absolute reference is used so the formula can be copied to all the other cells of the list

0 – Don’t change column

1,1 – Return data from this cell only. Cell range is 1 cell high and 1 cell wide

Video Tutorial

More by this Author


3 comments

priyanka 5 years ago

Starting from the first row of data on the List sheet


raj 3 years ago

I tries so, but only first cell of the scrollbar sheet works !


Johna963 2 years ago

The data are part of an autumn of just about 16 targets in Lib Dem provide because of the 2010 commander selection, before Huhne scooped 46.5 with all the electionHermes kelly felix bags didn can far in order to move benefit dagfcgededfd

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working