Shajratul Alam Towhid
When we have a huge dataset and want to find any data by scrolling in a confined space, the default scroll bar is not always enough. It’s because when you scroll using the default bar, the whole worksheet cells are also scrolled.
Wouldn’t it be easier if there were custom scroll bars close to your target data? 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.
The methods relating to inserting a scroll bar in Excel also help us to use our data without deleting any of them to maintain a confined space and help us to preserve our data from hijacking.
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 in Excel
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).
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.
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 )
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.
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.
This statement is true for other cells as well.
Read More: [Fixed!] Vertical Scroll Bar Not Working in Excel
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.
Step 02: Drag Scroll Bar and 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.
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
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 the most important. 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.
Download Practice Workbook
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 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.
