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 and 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.

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

Post a Comment

Blogger Tips and TricksLatest Tips And TricksBlogger Tricks


Cloud Power For You

Website Hosting At Low Price


Empire Views