How to Create and Use Scroll Bar in Excel (3 Easy Ways)

Excel allows users to use a custom scroll bar so that they can limit the number of visible rows and columns in a large data table for convenience.

In this article, I’ll provide you with a descriptive article on scroll bar in Excel. A scroll bar is a tool for displaying huge data tables from top to bottom or left to right and vice versa.

Excel gives you a lot of freedom when it comes to changing the parameters of a scroll bar. Using the scroll bar, you may directly customize the frequency rate of data updates. It greatly simplifies the representation of a large data table. That being said, in this article, you’ll learn how to easily construct and use a scroll bar in Excel.

While writing this article, we applied all procedures using Excel 365, but they may be utilized with any Excel version.

Here is an overview of an Excel data table with a Scroll Bar.

Download Practice Workbook

Scroll Bar.xlsm

What Is a Scroll Bar in Excel?

A scroll bar is a slider in Excel that allows us to examine data from left to right or top to bottom. By clicking on the direction signs in a scroll bar, we can view data one by one. We can also drag within the scroll bar to go from top to bottom or left to right.

However, there are two types of scroll bars in Excel. They are,

Vertical Scroll Bar
Horizontal Scroll Bar

Vertical Scroll Bar: A vertical scroll bar is a tool for viewing data from top to bottom.

Horizontal Scroll Bar: A horizontal scroll bar is a tool for viewing data from left to right.

How to Create a Scroll Bar in Excel?

To create a Scroll Bar in Excel, just select Developer >> Insert >> Scroll Bar from the Form Control.
Next, draw the Scroll Bar, and drag it to give it the shape of a vertical or horizontal Scroll Bar.

In the next part of this article, we will show you some useful applications of Scroll Bars.

What Are the Ways to Create Scroll Bar in Excel

There are three ways to create scroll bars in Excel. You can use the features from Developer tab for this purpose. You will find the methods in the descriptions below.

How to Use Vertical Scroll Bar in Excel?

In the following image, you will see a part of a data table that contains 30 rows excluding the column headings. We cannot see all the rows in the Excel sheet because the number of visible rows for an Excel sheet is limited for monitors of different sizes.

When we scroll down the Excel scrollbar, the column headings become invisible or data in other columns cannot be seen. This creates a bit of inconvenience. Using the Scroll Bar from the Form Control is user-friendly in that case.

To show this data table with a Scroll Bar, we create a dataset with the same column headings and create a vertical Scroll Bar.
Next, right-click on the Scroll Bar and select Format Control from the Context Menu.

After that, in the Format Control dialog box, insert the parameter properly. In our case, we have a data table of 30 rows and 10 of them will be displayed by the Scroll Bar. So, we set the Minimum and Maximum Value to 0 and 20.
The Scroll Bar is linked to the H1 cell of the Data Table The value of this cell changes from 0 to 20 while clicking on the down or up arrows of the Scroll Bar.

Click the image to get a detailed view

Now, use the formula below to change data with the Scroll Bar. Apply Fill Handle to AutoFill the lower cells.

=INDEX('Data Table'!$B$5:$E$34,'Data Table'!$H$1+ROWS('Data Table'!$B$5:B5),COLUMNS('Data Table'!$B$5:B5))

Formula Breakdown

‘Data Table’!$B$5:$E$34: This refers to the range of cells B5 to E34 in the worksheet named Data Table. It represents the array from which we want to retrieve the value.
‘Data Table’!$H$1+ROWS(‘Data Table’!$B$5:B5): This part calculates the row offset for the INDEX function. It takes the value in cell H1 in the Data Table worksheet and adds the number of rows from B5 to the current row (B5 to B5, B5 to B6, B5 to B7, and so on). The value of the H1 cell changes and thus changes the data in the table with each Scroll Bar This creates a dynamic offset as the formula is copied down the column.
- Output: 1
COLUMNS(‘Data Table’!$B$5:B5): This returns the column offset for the INDEX It counts the number of columns from B5 to the current column. Similar to the previous step, this creates a dynamic offset as we copied the formula across the row.
- Output: 1
INDEX(‘Data Table’!$B$5:$E$34,1,1): Here, we used the INDEX function to retrieve the value at the specified row and column offsets within the given array. The row offset is determined by the value in cell H1 and the number of rows from B5 to the current row. The column offset is determined by the number of columns from B5 to the current column.
- Output: Laura

Copy the formula to other columns by Fill Handle to get all the elements of the corresponding table.

Now, scroll down by using the Scroll Bar, and you will see the lower rows of the table in the Data Table sheet.

Thus, you can use the Scroll Bar to scroll down easily in the Excel sheet.

How to Use Horizontal Scroll Bar in Excel?

We use a similar formula to make the horizontal Scroll Bar work. When your data table contains a lot of columns, using the horizontal Scroll Bar is useful.

Here is a part of the data table that contains 9 columns. We cannot display all the columns on one page of an Excel sheet.

In a new sheet, we made a blank data table with 5 columns. The above data will be displayed with a horizontal Scroll Bar in this table.
After that, we create a horizontal Scroll Bar and format its parameters accordingly. We showed the procedures for formatting the parameters of a Scroll Bar previously.

Now, we use the formula below to extract those data in the new sheet. Apply Fill Handle to copy the formula all over the data table which populates the blank cells with corresponding data.

=INDEX('Data Table (for Horizontal)'!$B$4:$J$14,ROWS($B$5:B6),'Data Table (for Horizontal)'!$L$3+COLUMNS($B$5:B6))

The formula is similar to the one we used for the vertical Scroll Bar.

Click on the right side of the Scroll Bar to see the adjacent columns.

Thus a horizontal Scroll Bar can be useful to observe a data table that contains a lot of columns.

How to Use ActiveX Scroll Bar in Excel?

Previously, we showed how to use a Scroll Bar from the Form Control group. Now you will see the use of an ActiveX Scroll Bar. The data range will be used from the Data Table sheet.

First, create a Scroll Bar from the ActiveX Controls group. Select Developer >> Insert >> ActiveX Scroll Bar.
Draw the Scroll Bar to a suitable size. Drag and resize if needed.

After that, open the Properties window for the Scroll Bar from the Context Menu.

Next, set up Linked Cell, Max and Min values like we did to create a Vertical Scroll Bar.

And copy the formula below for the Scroll Bar.

=INDEX('Data Table'!$B$5:$E$34,$K$1+ROWS('Data Table'!$B$5:B5),COLUMNS('Data Table'!$B$5:B5))

This is the same formula we used for the Vertical Scroll Bar. The only difference is the Linked Cell reference used in the formula.

Now, you can scroll down the ActiveX Scroll Bar to see the unseen data of the data table.

Thus you can use an ActiveX Scroll Bar. The advantage of using it is that it has a variety of options to customize according to the users’ choice.

How to Hide Default Scroll Bar in Excel?

To hide the default vertical or horizontal scroll bar, you need to disable it from the Options feature.

Select File >> Options to open the Options feature.
After that, select the Advanced option feature and scroll down to Display options for this workbook section.
Thereafter, uncheck the options marked as 2 in the image below. If you want to hide one of the scroll bars, just uncheck that.
And finally, click OK.

In the following picture, you can see that there is neither horizontal scroll bar nor the vertical scroll bar.

Thus you can hide the default scroll bar in Excel.

How to Resize Horizontal Scroll Bar in Excel?

You can resize the horizontal scroll bar by following the image below.

Drag the three-dot icon beside the horizontal scroll bar in the Excel sheet right or left. If you move this icon to the left, the scroll bar size will increase, otherwise, it will decrease.

We drag the icon to the left so the size of the horizontal scroll bar increased. See the image below.

How to Fix Missing Scroll Bar (Default) in Excel?

Sometimes, the default Scroll Bars of an Excel workbook may not be visible. To fix the issue, please follow the instructions below.

One reason for missing Scroll Bars can be that users may disable them in the workbook. To enable them, select File >> Options and scroll down to Display options for this workbook.
Make sure to check Show horizontal scroll bar and Show vertical scroll bar.
After that, you will see the Scroll Bars appear.

There is another reason why the Horizontal Scroll Bar may be missing. See the image below.

Just drag the marked icon to the left, and you will see the Horizontal Scroll Bar appears.

Which Things You Have to Keep in Mind?

To modify or interact with the scroll bar, ensure that you are in Design Mode. It allows you to edit the Scroll Bar
Make sure to set the appropriate range and value limitations for the Scroll Bar to ensure it covers the desired range of values and behaves as intended.
It’s essential to thoroughly test the Scroll Bar’s functionality, especially if it interacts with other elements or data in your Excel workbook. Validate that it works as expected under various scenarios.

Frequently Asked Questions

1. Is it possible to control the scroll bar using VBA in Excel?

Answer: Yes, you can use VBA to control the scroll bar. You can programmatically scroll to a certain location by using the Scroll method, or you can alter the position of the Scroll Bar by adjusting the Value property.

2. What is the purpose of the SmallChange and LargeChange properties of a scroll bar in Excel?

Answer: When you click the arrow buttons on the scroll bar, the SmallChange property determines the incremental value, but the LargeChange property determines the incremental value when you click on the Scroll Bar’s track area.

3. Is it possible to hide or disable a scroll bar in Excel?

Answer: We can hide or disable a scroll bar. To do so, right-click on the Scroll Bar, select Format Control, and then check or uncheck the Display as icon option in the Control tab to hide or show the Scroll Bar. You can disable the Scroll Bar by setting the Enabled value to False in VBA.

Conclusion

In a nutshell, we can surmise that you will learn clear basic knowledge on how to create and use Scroll Bar in Excel. We have shown the use of both Form Control and ActiveX Control Scroll Bars in this article. In my opinion, the ActiveX Control Scroll Bar seemed more user-friendly than the Form Control Scroll Bar. If you have any questions or feedback regarding this article, please share them in the comment section. You can visit our website for more queries.

Scrollbar in Excel: Knowledge Hub

<< Go Back to Excel Parts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!