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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of Inserting Scroll Bar


2 Ways to Insert Scroll Bar in Excel

There are two types of scroll bars in Excel:

  1. Form Control Scroll Bar
  2. 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:

  1. Select Developer > Insert > Form Controls group > Scroll Bar.
  2. Hold the left mouse button and move the cursor horizontally or vertically to insert the scroll bar in the worksheet.
    Inserting Scroll Bar in a Sheet

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:

  1. Open a new sheet or choose another area in the worksheet.
  2. Insert the same column headings of your original dataset.
  3. Now, insert a scroll bar.
    Choosing Scroll Area
  4. Right-click on the scroll bar and select Format Control.
    Opening Format ControlThe Format Control dialog box will appear after that. The control parameters have some default values.
  5. 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)

    Setting up Format Control

  6. 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)
  7. Drag the Fill Handle icon downwards up to 10 rows and to the right to autofill the data table.
    Populating Data Table from Original Table

Now, your scroll bar is ready for application.

Use of Scroll Bar

Thus, you can efficiently use a scroll bar in Excel.

Note

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:

  1. Select Developer > Insert > Scroll Bar from the ActiveX Controls box.
  2. Hold the left mouse button and move the cursor horizontally or vertically to insert the scroll bar in the worksheet.
    Creating Scroll Bar from ActiveX Control Group
  3. Next, select Properties from the Controls group.
  4. In the Properties window, select a linked cell and choose Max and Min values.
    Setting up Necessary Parameters for ActiveX Scroll BarHere, 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.
  5. Disable the Design mode just by clicking on this button.
    Disabling Design Mode
  6. 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))
    Formula to Populate DataYou 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.

Use of ActiveX Scroll Bar


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo