Insert a Scroll Bar (Form Control) in Excel Worksheet

A Typical Scroll bar allows a user to scroll through a range of values when he drags the scroll box or clicks on either of the Arrows of the scroll bar. The user can move through the range of values by clicking on the portion between the scroll box and either of the scroll arrows. The user also has an option, where he can type any associated value directly into a cell of the worksheet and then use the scroll bar to adjust a large or small range of values for the associated value. The tutorial mentioned below will guide on how to insert a scroll bar and its range of values in the Excel 2010 Worksheet with an illustration.

Firstly, before inserting the scroll bar, you need to Insert the “Developer” tab in the Ribbon and then proceed with the following steps mentioned below.

1) Click on the “Developer” tab and then under the Developer tab, in the ‘Controls’ category, click on the ‘Insert’ icon.

Figure 1

2) Then, under the Insert icon, in the ‘Form Controls’ group, click on the ‘Scroll Bar (Form Control)’ icon.

Figure 2

3) Now, click on the portion of the worksheet where you intend to insert the Scroll bar. You can increase or decrease the size of the scroll bar using the Size handlers and you can also change the orientation of the Scroll bar by dragging any of the size handlers in a diagonal manner.

Figure 3

4) Now, Right click on the inserted Scroll bar in the worksheet and select the option that says ‘Format Control’.

Figure 4

5) In the ‘Format Control’ Dialogue box thus opened, you need to specify the values that you want the Scroll bar to use.

In the ‘Current value’ Box, enter a value that should be initially displayed by the Scroll bar.

In the ‘Minimum Value’ box, enter the lowest minimum value that the scroll bar can take within it. You have to make sure that, the minimum value is not greater than the Current value.

In the ‘Maximum value’ box, enter the Highest Maximum value that the Scroll bar can take within it.

In the ‘Incremental change’ box, enter a value by which the increment has to take place each time when you click on either of the scroll bar arrows.

In the ‘Page Change’ box, enter a value by which you want the scroll bar to make a jump each time when you click on the portion between the Scroll box and either scroll arrow.

In the ‘Cell link’ box, enter the particular cell where you want the range of values of the scroll bar to be displayed in the Worksheet.

In our illustration, we have used the scroll bar to scroll through the calendar years with the current value as ‘2010’, minimum value as ‘1900’, maximum value as ‘2100’, incremental change as ‘1’, the Page change as ‘10’ and the cell link as ‘B4’. Once you have specified all the values, click on the “OK” button.

Figure 5

6) Now you can observe that, the cell ‘B4’ will be filled with the current value ‘2010’.

Figure 6

7) Now, if you want to scroll through a particular calendar year, you can click on the either arrows of the Scroll bar or hit on any portion between the Scroll box and either scroll arrow.

Figure 7

So, this is how you can insert a Scroll bar and its values in the Excel worksheet.

See also How to Insert a List box in excel 2010 Application.

Read Other Applications