Scroll Bars In Excel

What Is Scroll Bars In Excel?

The Scroll Bars are the thin bars displayed on any computer applications to move the viewing area from left to right, or up to down. There are 2 Scroll Bars based on the movement, Horizontal Scroll Bars, and Vertical Scroll Bars.

The Scroll Bars in Excel, different from the general Application Scroll Bars, are a feature in Excel that is inserted from the “Developer” tab while working on a large dataset to make the dataset visible by scrolling up and down and to increase or decrease the cell values.

What Is Scroll Bars In Excel?

A Scroll Bar in Excel is a feature that helps users insert the scrolling button on a large dataset to view the required data or rows.
Unlike the general Scroll Bars that move horizontally and vertically, we can only move the Scroll Bar in Excel up and down, or increase and decrease the data. There is no horizontal movement possible.
When we link any cell using the “Format Control” option on the inserted “Scroll Bar”, we can increase or decrease the cell value of only that particular linked cell. The other cell values do not change.
To refer to the whole dataset or to move the whole rows or columns in a dataset, we can use the OFFSET(), or the INDEX() functions in Excel.

How To Create Scroll Bars In Excel?

We can Create Scroll Bars in Excel as follows:

Select the “Developer” tab → go to the “Controls” group → click the “Insert” option drop-down → select the “Scroll Bar (Form Control)” option from the “Form Controls” category, as shown below.

How to Create Scroll Bars in Excel - Step 1

Now, insert the Scroll Bars where we want in the worksheet by dragging the cursor.

How to Create Scroll Bars in Excel - Step 2

Now, right-click on the Scroll Bar, and select the “Format Control” option.

How to Create Scroll Bars in Excel - Step 3

The “Format Control” window appears. Enter the values in the argument fields, as shown below.

How to Create Scroll Bars in Excel - Step 4

The argument options are explained as follows:

Current value: The current numeric value or the current position of the scrollbar. The current value is always between the Minimum and Maximum values.
Minimum value: The Minimum limit set for the Scroll Bar.
Maximum value: The Maximum limit set for the Scroll Bar.
Incremental change: The incremental change defines the number of values to be changed in one click movement of the Scroll Bar. For ex, if it is set to 1, when we click the up and down arrow of the Scroll Bar, the current value will increase and decrease by 1, respectively.
Page change: Page change set value moves the page for the set value on a percentage basis. For example, suppose a Scroll Bar has a minimum value of 0 and a maximum value of 10. In that case, if we put 2 in the Page change field, the value of the Scroll Bar increases or decreases by 2 (in the same case, 20% of the value range of the scroll bar).
Cell link: The cell link value contains a cell address or reference, which will hold the current value of a scrollbar.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Let us take a basic example to learn more about this.

We will create Scroll Bars in Excel from the given data of the Names and Prices of fruits.

In the table, the data is,

Column A shows the Fruits.
Column B contains the Price.

The steps to insert the Scroll Bar in Excel are as follows:

First, let us enable the “Developer” tab.
Right-click on any of the tabs, here, the “Home” tab → select the “Customize the Ribbon…” option from the list, as shown below.
The “Excel Options” window opens. Click the checkbox of the “Developer” tab from the “Customize the Ribbon” list → click “OK”.
Now, the “Developer” tab is visible on the ribbon.
Select the “Developer” tab → go to the “Controls” group → click the “Insert” option drop-down → select the “Scroll Bar (Form Control)” symbol from the “Form Controls” category, as shown below.
Let us place the Scroll Bars where we want by dragging the cursor.
Right-click on the Scroll Bar, and select the Format Control option from the list.
The “Format Control” window opens. Set the values as follows, and click “OK”.
Current value: 100, Minimum value: 1, Maximum value: 20, Incremental change: 1, Page change: 2, and Cell link: $B$2.
The cell link value of cell $B$2 decreases by clicking the down arrow of the Scroll Bar.
The cell link value of cell $B$2 increases by clicking the up arrow of the Scroll Bar.

Examples

We will consider some scenarios to create Scroll Bars in Excel.

Example #1

We will create Scroll Bars in Excel from the given data of the Company’s turnover.

In the table, the data is,

Column A shows the Company Name.
Column B contains the Turn Over.

The steps to insert the Scroll Bar in Excel are as follows:

Step 1: Select the “Developer” tab → go to the “Controls” group → click the “Insert” option drop-down → select the “Scroll Bar (Form Control)” symbol from the “Form Controls” category, and insert the Scroll Bars by dragging the cursor.

Step 2: Right-click on the Scroll Bar, and select the Format Control option from the list.

Step 3: The “Format Control” window opens. Set the values as follows, and click “OK”.

Current value: 30000, Minimum value: 0, Maximum value: 30000, Incremental change: 1, Page change: 2, and Cell link: $B$2.

Step 4: The cell link value of cell $B$2 increases and decreases by clicking the up and down arrow of the Scroll Bar, respectively.

Example #2

We will create Scroll Bars in Excel from the given data that show the marks of John of all subjects.

In the table, the data is,

Column A shows the Subjects.
Column B contains the Marks of John.

The steps to insert the Scroll Bar in Excel are as follows:

Step 1: Select the “Developer” tab → go to the “Controls” group → click the “Insert” option drop-down → select the “Scroll Bar (Form Control)” symbol from the “Form Controls” category, and insert the Scroll Bars by dragging the cursor.

Step 2: Right-click on the Scroll Bar, and select the Format Control option from the list.

Step 3: The “Format Control” window opens. Set the values as follows, and click “OK”.

Current Value: 90, Minimum Value: 0, Maximum Value: 90, Incremental Change: 10, Page change: 2, and Cell link: $B$6.

Step 4: The cell link value of cell $B$6 increases and decreases by clicking the up and down arrow of the Scroll Bar, respectively.

Uses Of Scroll Bar In Excel

The uses of Scroll Bar in Excel are,

It saves time and space in a worksheet when we work with a large dataset.
It helps to view the required data when we perform cell links, and modify the data.

Important Things To Note

The Scroll Bar in Excel is useful for viewing data in a single window just by scrolling the data and not the worksheet.
The “Developer” tab is enabled first if it is not found on the ribbon.
An error occurs when the cell is not linked correctly in the “Format Control”.
Insert the correct Scroll Bar button from the “Form Controls” category. Do not get confused with the “Spin Bottle (Form Control)” button option, which is similar to the “Scroll Bar (Form Control)”.

Frequently Asked Questions (FAQs)

1. What does the Scroll Bar function do?

The Scroll Bar in Excel helps the user see the content by moving it up and down. It helps the user to consume less time and work efficiently. We can jump to any row easily.

2. How to fix if Scroll Bar not working?

The possible solutions if the Scroll Bar in Excel is not working are as follows:
• Check if the Scroll Bar Cell link reference is correct.
• Insert the exact Scroll Bar button option from the “Form Controls” category. Do not get confused with the “Spin Bottle (Form Control)” button option, which is similar to the “Scroll Bar (Form Control)”.

3. Where is Scroll Bar located?

The Scroll Bar in Excel is found as shown below.
Select the “Developer” tab → go to the “Controls” group → click the “Insert” option drop-down → select the “Scroll Bar (Form Control)” option from the “Form Controls” category, as shown below.

Download Template

This article must help understand Scroll Bars in Excel with its formula and examples. You can download the template here to use it instantly.

Scroll Bars In Excel Download

Scroll Bars In Excel