Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
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.
Excel Scroll Bar (Table of Contents)
Scroll Bars in Excel
If you are having a huge set of data which 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 the screenshot below that the data is not viewable in the complete format on a single screen.
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 your excel spreadsheet, in case it’s not activated yet.
- To activate Developer Tab, just right-click on any of the existing Excel tabs and select Customize the Ribbon option.
- 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 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 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 I column will be =OFFSET(D3,$L$3,0) to be put on cell I4 and for J column,
- It will be =OFFSET(E3,$L$3,0) to be put in column J4. Copy the formula to the others 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 in 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 of 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.
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 –