Scroll Bars In Excel Uses Examples How To Create A 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.

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.
You must activate the Excel “Develope” TabActivate The Developer Tab In ExcelEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more 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 ExcelRibbon In ExcelRibbons in Excel 2016 are designed to help you easily locate the command you want to use. Ribbons are organized into logical groups called Tabs, each of which has its own set of functions.read more.
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.
Now, you will have “Developer” as a tab option.
Go to the “Developer” tab and click “Insert.” Then, under the “Form ControlForm ControlExcel Form Controls are objects which can be inserted at any place in the worksheet to work with data and handle the data as specified. These controls are compatible with excel and can create a drop-down list in excel, list boxes, spinners, checkboxes, scroll bars.read more” section, you have to select the “Spin Button (Form Control).”
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.

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 – 1Minimum Value – 1Maximum Value – 19Incremental Change – 1Cell Link – $L$3

See the screenshot below.

Resize the scroll bar in Excel and place it to fit the length of 10 columns. See the screenshot below.
Now, you have to enter the following OFFSET formulaOFFSET FormulaThe OFFSET function in excel returns the value of a cell or a range (of adjacent cells) which is a particular number of rows and columns from the reference point. read more 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.
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.
It will be =OFFSET(E3,$L$3,0) in column J4. Copy the formula to the other cells of the column.
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.

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:

First, use the “Esc” option and “Undo.”Second, delete the cells and save.Third, delete the cells and run the macro.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.