Author: Oscar Cronquist Article last updated on May 20, 2020

Scroll bar in detail 1

This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to scroll through a smaller section of the data set using the arrows or click and hold on the bar and then drag.

This enhances the user experience and makes the worksheet easier to use. The scroll bar can also be used to manipulate a dashboard or a chart.

This particular scroll bar is a Format Control scroll bar and there are several other tools in the Format Controls group that you can use as well, however, I will only describe the scroll bar (Format Control) in this article. The other group is ActiveX Controls and often requires VBA code to manipulate and use.

Insert Scroll Bar

Insert scroll bar 1

The animated image above demonstrates how to insert a scroll bar.

  1. Go to tab "Developer" on the ribbon. Search for "Show the Developer tab" and your Excel version in your favorite search engine if your Developer tab is missing.
  2. Click the "Insert" button and a pop-up menu appears.
  3. Click the "Scroll bar" button.
  4. Click and hold with left mouse button on the worksheet.
  5. Drag with mouse to size the Scroll Bar.
  6. Release left mouse button to create the Scroll Bar.

Back to top

Position and resize a Scroll Bar

Scroll bar sizing handles

The image above shows a selected Scroll Bar and the sizing handles.

  1. Press and hold CTRL key.
  2. Click with left mouse button on the Scroll Bar, release the CTRL key.
  3. The Scroll Bar is now selected, you can tell it is selected by the sizing handles that now has appeared.
    There is a handle in each corner and one on each side of the Scroll Bar. In total eight handles.
  4. Click and hold with left mouse button on the selected Scroll Bar.
  5. Drag with mouse to place the Scroll Bar on a new location.
Note, to move the Scroll Bar to another worksheet or workbook you right-click on it. Click on cut on the pop-up menu.
Go to the new worksheet, right-click on the location you want it to appear. Click "Paste".

Click and hold with left mouse button on one of the sizing handles, then drag to resize the Scroll Bar. The Scroll Bar disappears, however, an empty box or rectangle shows the new size.

Scroll bar rectangle

Release the left mouse button and the empty box is replaced with the Scroll Bar.

Scroll bar rectangle1

Back to top

Advanced resizing

Resize scroll bar shift key 1

The Shift key lets you keep the proportion between the height and width if you drag one of the four corner sizing handles.

  1. Press and hold SHIFT key.
  2. Click and hold with left mouse button on a corner sizing handle.
  3. Drag with mouse to change the Scroll Bar size.

Resize scroll bar shift key1

Press the CTRL key while dragging with mouse to resize parallel sides simultaneously. The animated image above shows resizing a Scroll Bar using the right sizing handle while holding the SHIFT key. Both sides moves while dragging the mouse.

  1. Press and hold CTRL key.
  2. Click on the Scroll Bar to select it.
  3. Release the CTRL key.
  4. Click and hold with left mouse button on a sizing handle.
  5. Press and hold the Shift key.
  6. Drag with mouse.
  7. Release left mouse button and the SHIFT key.

Resize scroll bar alt key

The Alt key lets you align a sizing bar with the cell grid.

  1. Press and hold CTRL key.
  2. Click on the Scroll Bar to select it.
  3. Release the CTRL key.
  4. Click and hold with left mouse button on a sizing handle.
  5. Press and hold the Alt key.
  6. Drag with mouse to a cell grid line.
  7. Release left mouse button and the Alt key.

Resize scroll bar alt key1

You can also move the entire Scroll Bar using you mouse and the press and hold the Alt key to align one of the sides to the cell grid.

  1. Press and hold CTRL key.
  2. Click and hold on the Scroll Bar to select it.
  3. Release the CTRL key.
  4. Press and hold the Alt key.
  5. Drag with mouse to a cell grid line.
  6. Release left mouse button and the Alt key.

Back to top

How to change Scroll Bar settings

Scroll bar settings

Here is how to access Scroll Bar settings.

  1. Right-click on the scroll bar. A pop-up menu appears, see image above.
  2. Click "Format Control...", a dialog box appears.

Scroll bar dialog box

There are five different tabs on the top menu.

  • Size
  • Protection
  • Properties
  • Alt text
  • Control

The Control tab has 7 settings you can change. The "Current value:" is what the Scroll Bar right now returns. The minimum and maximum value are the limits that the Scroll Bar can operate.

The incremental change is how many steps the scroll bar changes when the user clicks on the upper or lower arrow. The "Page change:" value lets you choose how many that change when you click below or above the bar.

The "Cell link:" value lets you pick a cell that then shows the current value.

Scroll bar dialog box Alt text

The "Alt text" tab lets you create alternative text meaning it guides people with visual impairments so they know what is shown on the screen. The text will be read by a screen reader.

Scroll bar dialog box properties

The "Properties" tab lets you choose how Excel will position the Scroll Bar.

  • Move and size with cells
  • Move but don't size with cells
  • Don't move or size with cells

"Move and size with cells" is greyed out by design, you need to insert a Scroll Bar from the ActiveX Controls to use this setting.

"Move but don't size with cells will change the Scroll Bar location if you adjust column or row width/height on the worksheet but not it's size.

The next option is "Don't move or size with cells" and that will leave the Scroll Bar as it is no matter what you do to your cells.

Scroll bar dialog box protection

The "Protection" tab contains a checkbox that lets you lock or unlock the Scroll Bar. The default value is locked but it won't be protected until you protect the worksheet. Go to the "Review" tab on the ribbon to access the "Protect sheet" button.

Scroll bar dialog box size

The "Size" tab in the dialog box allows you to adjust the height and width using arrow buttons or enter a value. There is also an option to scale the Scroll Bar based on percentages and to lock the aspect ratio.

The aspect ratio is the proportion between the height and width, if you lock it the proportion stays the same when you change the height and width values. This is the same as holding the SHIFT key while resizing the Scroll Bar with your mouse.

Back to top

Example 1 - Scroll Bar manipulates data

Scroll bar

The image above shows a scroll bar in cell range B3:B5, it has an arrow pointing up and another arrow pointing down. There is also a bar indicating where in the data you are.

Row 3 contains a record that is fetched using a formula and based on a number that the scroll bar returns, that number is shown in cell A3. The number changes when you click the arrows on the scroll bar or click and drag the bar.

Here is how I did it

Insert a scroll bar

  1. Go to tab Developer on the ribbon
  2. Click "Insert" button
    insert a scroll bar
  3. Click Scroll bar
  4. Drag on sheet to create a scroll bar
  5. Right click on scroll bar
  6. Click "Format control"
    scroll bar format control
  7. Change cell link to $A$3
  8. Click OK

Array formula in cell C3:F3:

=INDEX(Table1,A3,0)
  1. Select cell range C3:F3
  2. Type =INDEX(Table15,A3,0)
  3. Press and hold CTRL + SHIFT
  4. Press Enter

Back to top

Use event code to automatically change scroll bar maximum range value

Activating the sheet runs the macro and sets the maximum value for the scroll bar. For example, if you add values to the table and it now has 250 rows. The event macro changes the scroll bar maximum range value to 250.

You don't need to adjust the maximum value for the Scroll Bar, the macro does that for you. This will save you time and make the scroll bar more user friendly.

'Event code is run if the worksheet is activated
Private Sub Worksheet_Activate()

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveSheet.Shapes("Scroll Bar 1").ControlFormat

    'Minimum value is set to 1
    .Min = 1

    'Maximum value is set to the number of rows in the Excel Table
    .Max = Range("Table1").Rows.Count

    'Incremental value is 1
    .SmallChange = 1

    'Page change value is the total number of rows in Excel Table Table1 divided by 10
    .LargeChange = Range("Table1").Rows.Count / 10

    'Scroll Bar is linked to cell A3
    .LinkedCell = "$A$3"
End With
End Sub

Back to top

Where to put the code?

Where to put the code

  1. Copy the above event code.
  2. Right-click on the worksheet name you want to deploy event code to. A pop-up menu appears.
  3. Click on "View Code". This will open the Visual Basic Editor, see image above. The corresponding worksheet module opens. The Project Explorer window shows the selected worksheet name meaning it is open.
  4. Paste event code to the worksheet module.
  5. Exit VB Editor and return to Excel.

The following example demonstrates scrolling through 10 records at a time. Clicking the scroll bar arrows changes the values in cell range C2:F12.

scroll through a data set1

Formula in cell C3:

=IFERROR(INDEX(Table2,$A$3+ROW(A1)-1,COLUMN((A1))),"")

Copy cell C3 and paste to cell range C3:F12.

Formula in cell A4:

=A3+1

Copy cell A4 and paste to cell range A5:A12

Back to top

Example 2 - Scroll Bar combined with a chart

scroll through a data set2

This example shows a scrolling Excel x y scatter chart. The chart data source is linked to cell range E3:F12, when the user clicks on the Scroll Bar the data in cell range E3:F12 changes.

The chart shows this change instantly.

Back to top

Example 3 - Scroll Bar and Conditional Formatting

scroll through a data set3

The last example shows conditional formatting highlighting current record.

Conditional formatting formula

=($C8=$C$3)*($D8=$D$3)*($E8=$E$3)*($F8=$F$3)

Back to top

Download Excel file


Scroll-through-records.xlsm