There are two scroll bars in excel, one is a vertical scroll bar which is used to view the data in excel from up and down and another scroll bar is horizontal scroll bar which is used to view the data from left to 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 option to hide scroll bars.
Scroll Bars in Excel
If you are having a huge set of data that has been entered in the Microsoft Excel spreadsheet, then you will surely need to use the Function of Scrollbars in Excel. The interactive scrollbar in Microsoft Excel is very much beneficial for users to use Excel when filled with lots of data. You don’t have to type a particular value manually to go to the required cell. You can easily use the scroll bar for selecting the values from a supplied list, which also saves your time. Let us show you how to create a scroll bar in Microsoft Excel.
How to Create a Scroll Bars in Excel?
Let’s understand the process of creating scroll bars in excel with examples.
I 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 complete format on a single screen.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Now, we will create scroll bars in excel for the above set of data, 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. Let’s follow the procedures, which have been explained with screenshots for better understanding.
- You have to get your data in place in a managed way. You can see it in the screenshot given below.
- Now, you have to activate the Developer Tab in excel in your excel spreadsheet, in case it’s not activated yet.
- To activate the Developer Tab, just right-click on any of the existing Excel tabs and select Customize the Ribbon in Excel.
- You will see the Excel Options dialogue box. On the right-hand side, you have to check the Developer option under the Main Tabs pane. Consider the screenshot below.
- Now, you will have ‘Developer’ as a tab option.
- Now go to the Developer tab and click Insert. Under the Form Control section, you have to select the Spin Button (Scroll Bar).
- You have to click the Scroll Bar in the excel option and then click any cell of your Excel spreadsheet. You will see the Scroll Bar inserted in the spreadsheet.
Now you have to the right-click on the inserted Scroll Bar in excel and select ‘Format Control.’ You will see a Format Control Dialogue Box.
- Move on to the ‘Control’ tab of the Format Control Dialogue 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.
- Resize the scroll bar excel and place it to fit the length of 10 columns. See the screenshot below.
- Now, you have to enter the following OFFSET formula in the first cell of the data, i.e., H4. Formula is =OFFSET(C3,$L$3,0). You have to copy this formula to fill all the other cells of the column.
- Similarly, you have to fill the OFFSET formula in the I column and J column. 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) to be put in column J4. Copy the formula to the other cells of the column.
- The above OFFSET formula is now dependent on cell L3 and is linked to the scroll bars in excel. 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 get arise from the tiny scrollbar. It is very well known that the scroll bars, be it horizontal or vertical, both of these are set by the used range cell size. Sometimes, this used range can become very large due to lots of data sets, and due to this, the scrollbar becomes tiny. This tiny scrollbar issue is so weird that it can make it more difficult for you to navigate around the worksheet.
Let’s know why does this error happen? This 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 actually needed. This potential human error is responsible for this error to be happening. There are four ways by which you can fix this problem –
-
- Use the Esc option and Undo.
- Delete the cells and save.
- Delete the cells and run the macro.
- Do it all with a macro.
Things to Remember
- The scrollbar function is very useful for the users of Microsoft Excel when there are a lot of datasets to be viewed in a single window.
- You can easily use the scroll bars excel for selecting the values from a supplied list, which also saves your time.
- You have to activate the Developer tab if it’s not yet activated.
- An error known as ‘tiny scrollbar’ occurs due to human error.
Recommended Articles
This 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 example and downloadable excel templates. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
