How to Insert Scroll Bar in Excel (2 Suitable Methods)

We deal with the problem of how to insert a scroll bar in Excel when we have a huge dataset and want to find any data by scrolling in a confined space. The methods relating to insert a scroll bar in Excel helps us to use our data without deleting any of them to maintain a confined space and helps us to preserve our data from hijacking. In this article, we will show how to insert a scroll bar in Excel in both horizontal and vertical ways with a single cell and multiple cells.


Download Practice Workbook


2 Methods to Insert Scroll Bar in Excel

The first step is to organize our data. For this purpose, we have made a dataset of company profiles in the USA including the Name of Owner, Number of Employees, and Yearly Revenue like the below image depicts. The following dataset continues to Row 34 (B34:E34 cells).


1. Creating a Vertical Scroll Bar by Adding Form Control

We need to follow the following steps to make a vertical scroll bar.

Step 01: Choose the Form Control Option

Firstly, select the Developer tab > Click Insert >  Click Scroll Bar from the drop-down menu (it is called Form Control).

how to insert scroll bar in excel

If we can’t find the Developer tab that means it is not activated, so we need to activate it first. For this the steps are:

Click View > Click Customize the Ribbon.

how to insert scroll bar in excel

Next, click on the Developer tab.

Note: If it already exists, we don’t need to follow this step.

Step 02: Drag Scroll Bar & Open Format Control Option

After completing the first step, we have to click anywhere on our worksheet with the Scroll Bar (Form Control) button. A Scroll Bar will be added to the Excel worksheet because of this.

Thirdly, we need to Right-click the Scroll Bar and select ‘Format Control’ from the drop-down menu.  And, we shall see a Format Control dialog box.

Step 03:Manage Format Control Dialogue Box

In the Format Control dialogue box, we need to go to the Control tab, and make the following changes:

Current value: 0

Minimum value: 0

Maximum value: 15(We display 15 rows at a time, so it’s 15 here. As a result, if the user sets the scroll bar to 15, rows 15-30 are displayed.)

Incremental change: 1

Page Change:15

Cell link: VERTICAL!$G$5(  Here VERTICAL refers to the sheet name from where we have selected the cell )

how to insert scroll bar in excel

Step 04:Resize the Sheet and Add Formula 

In this step, we need to resize the scroll bar. Then we need to apply the OFFSET function like this.

=OFFSET(DATASET!B5,VERTICAL!$G$5,0,1,1)

Here, DATASET!B5 is the reference cell taken from the Dataset sheet, and VERTICAL!$G$5 is the reference cell. We have taken it through selecting the G5 cell in the Dataset sheet and then pressing the F4 button. And 0,1 and 1 are Columns, Height, and Width. This OFFSET function mainly uses a reference which is DATASET!B5 cell here. Then, we offset it by the G5 cell in the Dataset sheet. Since we have linked G5  to scroll bar value,  the formula refers to the first state name at the time the value of the scrollbar is 1. Similarly, the indication of the second state happens when it becomes 2.

how to insert scroll bar in excel

In this case, the OFFSET function depends on cell VERTICAL!$G$5, which is mainly linked to the scroll bar.

Now, the scroll bar set is ready.

And this statement is true for other cells as well.

Read More: [Fixed!] Vertical Scroll Bar Not Working in Excel (10 Possible Solutions)


2. Inserting a Horizontal Scroll Bar in Excel

A horizontal scroll bar will change the value in excel horizontally. The steps to create it are the same as the steps described for creating a vertical scroll bar. It is only the change in making the Format Control dialog box horizontally. The cursor of the mouse needs to move in a way such that the horizontal length should be larger than the vertical length. And lastly, another difference is that we need to select the cell whose value needs to be scrolled. The steps are like this:

Step 01: Choose the Form Control Option

Select Developer tab > Click Insert > Click Scroll Bar from the drop-down menu (Form Control) > Put in on a horizontal manner.

how to insert scroll bar in excel

Step 02: Drag Scroll Bar & Open Format Control Option

In this step, we need to right-click the scroll bar and select Format Control from the drop-down menu. A Format Control dialog box will appear.

how to insert scroll bar in excel

Step 03:Manage Format Control Dialogue Box

In the Format Control dialogue box we need to go to the Control tab, and make the following changes:

Current value: 0

Minimum value: 0

Maximum value: 300(So we can scroll the value up to 300)

Incremental change: 1

Page Change:10( It actually doesn’t have any significance here)

Cell link: $D$5( The cell whose value we need to change by using the scroll bar)

Read More: [Fixed!] Excel Horizontal Scroll Bar Not Working (8 Possible Solutions)


Things to Remember

  • The two methods described above are slightly different. We shall apply the first one i.e. vertical scroll bar for a whole dataset while we shall apply the horizontal scroll bar for scrolling only one cell’s value.
  • We need to add a scroll bar for every single cell individually in the case of horizontal scrolling described above.
  • In the case of vertical scrolling, we need to use the OFFSET function which again needs a reference cell.
  • The Maximum value, Incremental change, Page change, and Cell link in the Format Control box are of the most importance. For vertical scrolling, the sum of the Maximum value and Page change equals the Total Row Number of the dataset. Otherwise scrolling the bar will give zero value for the last cells.

Conclusion

We can scroll in Excel by using scroll bars in both Vertical and Horizontal ways. And also, for both the whole dataset and for a single cell. When we add the scrollbar in a vertical manner for the whole dataset then we have to use the OFFSET function to link the dataset with the scroll bar. But in the case of a single cell scrolling, we don’t need to use the OFFSET function because one single cell doesn’t need to link using this formula. We can use Excel to scroll in any manner easily.


Related Articles

Towhid
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo