Scroll Bars in Excel

Last Updated :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

There are two scroll bars in Excel: a vertical scroll bar is used to view the data in Excel from up and down, and another scroll bar is a horizontal scroll bar used to view the data from left to the right. We can hide or unhide the scroll bar from the "Options" category in the "Files" tab, where we will find the "Advanced" tab and the option to hide scroll bars.

Scroll Bars in Excel

If you have a huge set of data inserted into the Microsoft Excel spreadsheet, you will surely need to use the function of scrollbars in Excel. The interactive scrollbar in Microsoft Excel is very beneficial for users to use Excel when filled with lots of data. You do not have to type a particular value manually to go to the required cell. You can easily use the scroll bar to select the values from a supplied list, saving your time. Let us show you how to create a scroll bar in Microsoft Excel.

How to Create Scroll Bars in Excel?

Let us understand the process of creating scroll bars in Excel with examples.

We have taken the data of 35 states in India, according to the census 2011. You can see in the screenshot below that the data is not viewable in the full format on a single screen.

Scroll Bar in Excel Example 1

Now, we will create scroll bars in Excel for the above data set, with the help of which a window will display only 10 states at a time. When you change the scroll bar, the data will change dynamically. For better understanding, let us follow the procedures, which have been explained with screenshots.

  • You have to get your data in place in a managed way. You can see it in the screenshot given below.
Scroll Bar in Excel Example 1-1
  • You must activate the Excel "Develope" Tab in your Excel spreadsheet in case it is not yet started.
  • To activate the "Developer" tab, right-click on any existing Excel tabs and select Customize the Ribbon in Excel.
Scroll Bar in Excel Example 1-2
  • You will see the "Excel Options" dialog box. Next, you must check the "Developer" option under the "Main" tab-pane on the right-hand side. Consider the screenshot below.
Scroll Bar in Excel Example 1-3
  • Now, you will have "Developer" as a tab option.
Scroll Bar in Excel Example 1-4
  • Go to the "Developer" tab and click "Insert." Then, under the "Form Control" section, you have to select the "Spin Button (Form Control)."
Scroll Bar in Excel Example 1-5
  • You have to click the scroll bar in the Excel option and then click any cell of your Excel spreadsheet. You will see the scrollbar inserted in the spreadsheet.
Excel Example 1-6

You must right-click on the inserted scroll bar in Excel and select "Format Control." You will see a "Format Control" dialog box.

  • Move on to the "Control" tab of the "Format Control" dialog box and make the below-given changes:
  • Current Value – 1
  • Minimum Value – 1
  • Maximum Value – 19
  • Incremental Change – 1
  • Cell Link – $L$3

See the screenshot below.

Excel Example 1-7
  • Resize the scroll bar in Excel and place it to fit the length of 10 columns. See the screenshot below.
Excel Example 1-8
  • Now, you have to enter the following OFFSET formula in the first cell of the data, i.e., H4. The formula is =OFFSET(C3,$L$3,0). You have to copy this formula to fill all the other cells of the column.
Excel Example 1-9
  • Similarly, you must insert the OFFSET formula in the I and J columns. The formula for the I column will be =OFFSET(D3,$L$3,0) to be put on cell I4 and for the J column.
Excel Example 10
  • It will be =OFFSET(E3,$L$3,0) in column J4. Copy the formula to the other cells of the column.
Excel Example 11
  • The above OFFSET formula is now dependent on cell L3 and linked to Excel's scroll bars. The scrollbar is all set for you in the Excel spreadsheet. See the screenshot below.
Excel Example 12

Resetting the Scroll Bars in Excel – Tiny Scrollbar Error

Sometimes, an issue might arise from the tiny scrollbar. For example, it is very well known that the used range cell size sets both scroll bars, horizontal or vertical. Often, the used range becomes very large due to lots of data sets. Therefore,  the scrollbar may become tiny. This tiny scrollbar issue is so weird that it can make it more difficult for you to navigate around the worksheet.

Let us know why this error happens? It is always caused due to user error only. It can occur if you accidentally stray into the cells way outside of the area, which is needed. This potential human error is responsible for this error to be happening. There are four ways by which you can fix this problem:

  1. First, use the "Esc" option and "Undo."
  2. Second, delete the cells and save.
  3. Third, delete the cells and run the macro.
  4. Finally, do it all with a macro.

Things to Remember

  • The scrollbar function is useful for Microsoft Excel users when viewing many datasets in a single window.
  • We can easily use the scroll bars in Excel for selecting the values from a supplied list, which also saves your time.
  • We must activate the "Developer" tab if it is not yet started.
  • An error known as "tiny scrollbar" occurs due to human error.

This article has been a step-by-step guide to scroll bars in Excel. Here, we discuss its uses and how to create scroll bars in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: -

  • Scenario Manager in Excel
  • Checkbox in Excel
  • Combo Box Excel
  • Excel Tables