A vertical scroll bar is a tool that can help us visualize large data tables from top to bottom. Excel provides a ton of flexibility in modifying the specifications of a vertical scroll bar. You can literally control the frequency rate of the data updates using the vertical scroll bar. It helps portray a large data table a lot easier. With that being said, in this article, I will show you how to create a vertical scroll bar in Excel with ease.
What Is a Scroll Bar in Excel?
A scroll bar is a slider that enables us to view data in Excel from left to right or top to bottom. Using a scroll bar we can view data one by one by clicking on the direction signs in a scroll bar. We can also drag inside the scroll bar to directly navigate 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 that enables us to view data from top to bottom.
Horizontal Scroll Bar: A horizontal scroll bar is a tool that enables us to view data from left to right.
Why Use a Vertical Scroll Bar in Excel?
Here, I have a long data table that goes up to row 34. The problem is I can’t watch the whole data table in one go. For example, with 100% zoom, I can see up to row 29 only.
To see the rest of the rows of my data table, I’ve to scroll down manually using my mouse scroll button. This is irritating and disappointing as this interrupts my normal workflow.
To manage a convenient view of my data table, I’ve attached a vertical scroll bar beside my data table. Now I can see all of my data in the data table just by controlling the vertical scroll bar.
It’s really a convenient way to visualize a long-form of the data tables, right? Well, now let me show you how can you create a vertical scroll bar like this.
How to Create a Vertical Scroll Bar in Excel with Easy Steps
The scroll bars can be accessed from the Form Controls group. The Form Controls tool is located in the main ribbon of the Developer tab.
But the Developer tab is disabled by default. If you already have the Developer tab enabled in your Excel program, it’s great. You can skip the first step.
But if you don’t have the Developer tab enabled in your Excel, start from Step-1.
Step-1: Adding Developer Tab
Generally, the Developer tab is seen between the View and the Help tab. However, if you don’t have the Developer tab there,
❶ Right-click anywhere on the main tab.
A pop-up dialog box will appear.
❷ Select Customize the Ribbon from the dialog box.
This will open the Excel Options dialog box.
❸ Now select Customize Ribbon option from the left side of the Excel Options dialog box.
❹ Now check the option Developer in the Main Tabs box.
❺ After that hit the OK button.
Now check the main ribbon again. This time you will see the Developer tab is added to the main ribbon between the View and the Help tab.
Step-2: Draw & Create a Vertical Scroll Bar
Now it’s time to create the vertical scroll bar in the Excel sheet.
To create the vertical scroll bar,
❶ Go to the Developer tab first.
❷ Click on the Insert drop-down menu.
❸ From the Form Controls group select Scroll Bar (Form Control).
Now you will see a plus icon (‘+’) on your Excel sheet.
❹ Just draw a vertical bar by dragging the plus icon (‘+’) in your Excel sheet using the left-click button of your mouse.
When you release the left-click button of your mouse, you will see that the vertical bar you just drew has turned into a vertical scroll bar.
Step-3: Tweaking Format Control Dialog Box for Vertical Scroll Bar
In this section, I will show you how to activate the vertical scroll bar.
❶ At first, right-click on the vertical scroll bar that you’ve just created.
❷ Select Format Control from the context menu.
The Format Control dialog box will appear with the Control tab pre-selected.
In the Format Control dialog box, you will see several options to fill up.
The options are:
- Current value
- Minimum value
- Maximum value
- Incremental change
- Page change
- Cell link
Now let me explain what all these options actually mean.
How Does Vertical Scroll Bar Works?
Before explaining the options, you should know how the vertical scroll bar actually works.
The vertical scroll bar is tied to a cell with a numerical value. Using the Format Control dialog box, the value of the cell is determined. Also how the numerical value of the cell will change is determined by the Format Control dialog box.
So, when you click on the vertical scroll bar, the value of the cell increases or decreases which depends on which side of the scroll bar you are clicking.
Then a lookup formula is used to retrieve data from the source dataset. This lookup format is tied to the cell. Thus when the value of the cell changes, the lookup formula extracts different values responding to the cell value changes.
Now let’s try to understand, what the parameters of the Format Control dialog box actually mean.
Current value: Here you will set the initial value of the linking cell. It depends on your dataset.
Minimum value: The Minimum value should be equal to the Current Value or 0.
Maximum value: Maximum value refers to the highest value that the vertical scroll bar can have.
Incremental change: This determines the dataset update rate. Using 1 will update the dataset by 1 row each time you click on the scroll bar and so on.
Page change: It determines how many rows you want to update at a time when you drag the vertical scroll bar vertically.
Cell link: Here, you will mention the cell address where you want to keep the value corresponding to the vertical scroll bar.
Read More: How to Insert Scroll Bar in Excel
Calculating Maximum Value
The Maximum Value is very sensitive. If you choose it wrong, you will get the #REF! error.
You can use the following formula to determine the Maximum Value.
❶ At first, choose any blank cell and insert the following formula using the ROWS function.
=ROWS(B5:E34)-10Here, B5:E34 is the range of the dataset. Then 10 is the number of rows that you want to see at a time. The ROWS function calculates the total number of rows in the range B5:E34.
❷ Then press ENTER.
Now you will get the Maximum Value in the cell where you’ve inserted the formula.
Now set the options as follows:
- Current value: 0
- Minimum value: 0
- Maximum value: 20
- Incremental change: 1
- Page change: 10
- Cell link: $G$4
After setting all the values, hit OK.
Now go back and check the vertical scroll bar values. You will see that the minimum value is set to 0 and the maximum value is 20.
Step-4: Retrieve Data & Add Formula to Activate Vertical Scroll Bar
Now it’s time to connect the vertical scroll bar with the dataset.
❶ At first, just copy the formats of your dataset and paste them beside the vertical scroll bar.
As I’ve set the Page Change value to 10, I’ve taken 10 rows in total.
❷ Now insert the following lookup format in the first cell of the blank dataset.
The lookup formula consists of the INDEX, ROWS & COLUMNS functions.
=INDEX($B$5:$E$34,$G$4+ROWS($B$5:B5),COLUMNS($B$5:B5))
Formula Breakdown
- COLUMNS($B$5:B5): The COLUMNS function calculates the total number of columns in the range $B$5:B5. In the range, the first cell address is locked up but the second cell is not. Thus, when you will drag the formula, it will expand. So that you will get the total number of columns that your source data table has.
- ROWS($B$5:B5): The ROWS function calculates the total number of rows in the range $B$5:B5. In the range, the first cell address is locked up but the second cell is not. Thus, when you will drag the formula, it will expand. So that you will get ta serial number starting from 1. According to this serial number, the INDEX function will retrieve specific rows from the source data table.
- $G$4+ROWS($B$5:B5): The range in the ROWS function will expand up to $B$5:B14 which can return a maximum of 10. The source data table has a total of 30 To retrieve all the rows $G$4 is added to the ROWS($B$5:B5). Cell $G$4 possesses numbers ranging from 0 to 20. So adding $G$4 with ROWS($B$5:B5) can produce up to 30. That’s why the formula can retrieve all the rows from the source data range which is $B$5:$E$34.
- $B$5:$E$34: This is the data range of the source data table.
- INDEX($B$5:$E$34,$G$4+ROWS($B$5:B5),COLUMNS($B$5:B5)): The INDEX function pulls specific rows specified by $G$4+ROWS($B$5:B5) and specific columns specified by COLUMNS($B$5:B5) from the range $B$5:$E$34.
❸ Then press ENTER.
Now you will see Laura in the cell where you have applied the formula.
❹ Drag the lookup formula all over the blank data table that you’ve created using the Fill Handle.
So, you’ve successfully completed the task to create a vertical scroll bar and also connected it with a secondary data table.
Now just click the vertical scroll bar and the data in the data table will keep changing like the following animation.
Read More: How to Adjust Scroll Bar in Excel
Show & Hide Default Vertical Scroll Bar in Excel
So far I have discussed creating a synthesized vertical scroll bar in Excel. This section is not about how to create a vertical scroll bar in Excel but rather show or hide the default bar.
Besides that, Excel has a built-in vertical scroll bar that you will find on the right side of the Excel window.
Sliding that vertical scroll bar, you can also view the data table from top to bottom or bottom to top.
However, you can hide or show the built-in vertical scroll bar. I’m showing you two ways to do that.
1. Using Advanced Options
❶ First, go to the File tab.
❷ Then select Options.
This will open the Excel Options dialog box.
Hide Vertical Scroll Bar
To hide the vertical scroll bar,
❶ Select Advanced.
❷ Uncheck ‘Show vertical scroll bar’ under the Display options for this workbook section.
❸ Then click OK.
Now check the right part of your Excel window. You will see that the vertical scroll bar has disappeared.
Show Vertical Scroll Bar
To show the vertical scroll bar,
❶ Select Advanced in the Excel Options dialog box.
❷ Check ‘Show vertical scroll bar’ under the Display Options for this workbook section.
❸ Then click OK.
Now check your Excel window. You will see that the built-in vertical scroll bar is visible again.
2. Using VBA
If you want to show or hide the built-in vertical scroll bar using VBA,
❶ Go to the Developer tab first.
❷ Select Visual Basic from the Code group.
This will open the Visual Basic Editor.
Alternatively, you can press ALT + F11 to open the VBA Editor.
If that doesn’t work, then try ALT + Fn + F11. I hope, this shortcut key will surely work to open the VBA Editor.
❸ Now select the Insert tab.
❹ Choose Module from the drop-down menu.
A new module will open.
Hide Vertical Scroll Bar
To hide the built-in vertical scroll bar,
❶ Copy the following VBA code.
❷ Paste it on the VBA Editor.
❸ Save the code.
❹ Now press the F5 key to run the VBA code.
Option Explicit
Private Sub Hide_Vertical_Scroll_Bar()
With ActiveWindow
.DisplayVerticalScrollBar = False
End With
End Sub
Now check your Excel window. You will notice that the vertical scroll bar has disappeared.
Show Vertical Scroll Bar
To show the built-in vertical scroll bar,
❶ Copy the following VBA code.
❷ Paste it on the VBA Editor.
❸ Save the code.
❹ Now press the F5 key to run the VBA code.
Option Explicit
Private Sub Show_Vertical_Scroll_Bar()
With ActiveWindow
.DisplayVerticalScrollBar = True
End With
End Sub
After running the VBA code, you will see that the vertical screen bar is visible again.
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed the steps of how to create a vertical scroll bar in Excel. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries as soon as possible.
