In Excel, a scroll bar is a graphical tool that allows users to navigate through large sets of data by scrolling vertically or horizontally within a worksheet.
In this Excel tutorial, you will learn how to insert a scroll bar in a worksheet.
The scroll bar is necessary to navigate through large sets of data that don’t fit within the visible area of a worksheet. It allows users to move vertically or horizontally within the worksheet, providing a convenient way to access information beyond the immediate view.
Here is an overview of how to insert a scroll bar in the Excel worksheet using the Developer tab.
2 Ways to Insert Scroll Bar in Excel
There are two types of scroll bars in Excel:
- Form Control Scroll Bar
- ActiveX Control Scroll Bar
You will find the necessary discussion on how to insert these two scroll bars in the following sections:
Inserting Scroll Bar from Form Controls
To insert a scroll bar from Form Control, follow the steps below:
- Select Developer > Insert > Form Controls group > Scroll Bar.
- Hold the left mouse button and move the cursor horizontally or vertically to insert the scroll bar in the worksheet.
Here, a vertical scroll bar is created.
How to Use a Scroll Bar in Excel
After inserting a scroll bar, you need to follow certain steps to use it properly. Here, in the sheet named Data Table, we have a dataset of 30 rows in the range B5:E34. 10 of them will be visible by the scroll bar.
Company Owner |
Product | Number of Employee |
Yearly Revenue (M) |
Laura | Car | 100 | 10 |
James | Car | 120 | 20 |
Jane | Computer | 95 | 12 |
Peter | Garments | 500 | 10 |
Lara | Garments | 700 | 15 |
William | Oil | 250 | 5 |
Nicolas | Gas | 300 | 20 |
Haddin | Car | 110 | 22 |
Peterson | Chemicals | 350 | 25 |
Rob | Weapons | 300 | 10 |
Flintoff | Weapons | 100 | 10 |
Adam | Weapons | 120 | 20 |
Kiera | Chemicals | 95 | 12 |
Jason | Chemicals | 500 | 10 |
Ashish | Car | 250 | 22 |
Hiran | Car | 300 | 25 |
Dushan | Car | 110 | 10 |
Joy | Computer | 350 | 10 |
Kane | Garments | 700 | 10 |
Beer | Garments | 650 | 15 |
Mike | Weapons | 100 | 10 |
Gray | Weapons | 120 | 10 |
Tison | Chemicals | 95 | 20 |
Erikson | Chemicals | 500 | 12 |
Nicles | Car | 350 | 10 |
Brek | Car | 700 | 10 |
Drek | Computer | 650 | 15 |
Laren | Oil | 300 | 20 |
Jovan | Gas | 110 | 22 |
Flintar | Car | 350 | 25 |
To use a scroll bar, follow the steps below:
- Open a new sheet or choose another area in the worksheet.
- Insert the same column headings of your original dataset.
- Now, insert a scroll bar.
- Right-click on the scroll bar and select Format Control.
The Format Control dialog box will appear after that. The control parameters have some default values. - In the Format Control dialog box, set up the parameters properly:
-
- Minimum Value: 0
- Maximum Value: 20 (as we have 30 rows and we want to show 10 rows at a time, making the scroll bar value 20 will show the rows 21-30)
- Incremental Change: 1
- Page Change: 10
- Cell Link: ‘Data Table’!$H$1 (H1 cell of the Data Table sheet)
-
- To extract data from the original table to the new sheet, copy the formula in the first cell under the first column:
=INDEX('Data Table'!$B$5:$E$34,'Data Table'!$H$1+ROWS('Data Table'!$B$5:B5),COLUMNS('Data Table'!$B$5:B5))
Here, the B5:E34 range is in the Data Table sheet. In this sheet, the H1 cell is linked to the scroll bar. The content of the B5 cell of the Data Table should appear in the new table’s B5 cell after applying the formula. You can also use the formula below instead of using the INDEX function:=OFFSET('Data Table'!B5,'Data Table'!$H$1,0,1,1)
- Drag the Fill Handle icon downwards up to 10 rows and to the right to autofill the data table.
Now, your scroll bar is ready for application.
Thus, you can efficiently use a scroll bar in Excel.
Here is a short description of the Control values:
- Current value: This value refers to the position of the scroll box in the scroll bar. If the scroll box is on the top, the value becomes 0. If it’s at the bottom of the scroll bar, the value becomes equal to the Maximum value. And keeping it at the bottom of the scroll bar makes the Current value equal to the Maximum value. Each time you scroll up or down, the Current value changes from minimum to maximum value. You can see this change in the linked cell.
- Minimum value: This will be the value of the linked cell when the scroll box is at the top.
- Maximum value: This will be the value of the linked cell when the scroll box is at the bottom.
The area of the visible data table by the scroll bar can be maintained by the minimum and maximum values. So you need to set them properly. - Incremental change: This value determines the step amount each time you scroll up or down. Say you set the Minimum value and Incremental change to 0 and 2 respectively. If you keep clicking the scroll-down button, the value of the Current value will be 0, 2, 4, 6, and so on.
- Page change: This is the amount the scroll bar value changes when you click on the scroll bar track.
- Cell link: The scroll bar generates values while it’s triggered (scroll up or down). So a cell needs to be linked with the scroll bar to store these values. The linked cell contains the Current values of the scroll bar corresponding to the position of the scroll box. The values can be used in a formula to control the scroll bar.
Read More: How to Adjust Scroll Bar in Excel
Inserting Scroll Bar from ActiveX Control
Using a scroll bar from the ActiveX Controls group is similar to the scroll bar from the Form Controls group. It’s easier to use and its overall design can be formatted to the users’ preference.
To create a scroll bar from the ActiveX Controls group, follow the steps below:
- Select Developer > Insert > Scroll Bar from the ActiveX Controls box.
- Hold the left mouse button and move the cursor horizontally or vertically to insert the scroll bar in the worksheet.
- Next, select Properties from the Controls group.
- In the Properties window, select a linked cell and choose Max and Min values.
Here, Cell K1 of the Data Table sheet is the linked cell, Max and Min values are 20 and 0 respectively. You can see that there are other properties to format the scroll bar. - Disable the Design mode just by clicking on this button.
- Use the formula below and apply the Fill Handle like the previous method:
=INDEX('Data Table'!$B$5:$E$34,$K$1+ROWS('Data Table'!$B$5:B5),COLUMNS('Data Table'!$B$5:B5))
You can see that the formula is almost similar to the previous one. Only the cell reference of the Linked Cell is changed here.
Now, your scroll bar is ready for application.
Download Practice Workbook
Conclusion
To sum up, you can learn the basic procedures to insert a scroll bar in Excel after reading this article. The ActiveX scroll bar is more advanced than using the Form Control scroll bar. However, the Form Control scroll bar has simple parameters. If you have any questions or feedback regarding this article, feel free to share them in the comment section. Thank You!!!
Frequently Asked Questions
How do I enable scrolling in Excel?
Select the File tab and then click Options > Advanced. Then, select the Show horizontal scroll bar and the Show vertical scroll bar checkboxes under Display options for this workbook and click OK.
How do I remove a scroll bar in Excel?
To remove a scroll bar from the worksheet, right-click on the scroll bar to select the scroll bar and press the “Delete” key on your keyboard.
Can I customize the appearance of the scroll bar?
To customize the appearance of the scroll bar created from the Form Controls group, right-click on the scroll bar, choose “Format Control,” and make adjustments in the Format Control dialog box. Moreover, you have a wide range of options to format the scroll bar if you create it from the ActiveX Controls group. Just go to its Properties and modify the necessary parameters or appearance.
Related Articles
<< Go Back to Scrollbar in Excel | Excel Parts | Learn Excel