WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Scroll Bars in Excel

Scroll Bars in Excel

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.

You can download this Scroll Bars Excel Template here – Scroll Bars Excel Template

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Scroll Bar in Excel Example 1

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.

Scroll Bar in Excel Example 1-1

  • 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.

Scroll Bar in Excel Example 1-2

  • 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.

Scroll Bar in Excel Example 1-3

  • Now, you will have ‘Developer’ as a tab option.

Scroll Bar in Excel Example 1-4

  • Now go to the Developer tab and click Insert. Under the Form Control section, you have to select the Spin Button (Scroll Bar).

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 Scroll Bar inserted in the spreadsheet.

Excel Example 1-6

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.

Excel Example 1-7

  • Resize the scroll bar 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. 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 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,

Excel Example 10

  • It will be =OFFSET(E3,$L$3,0) to be put 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 is linked to the scroll bars in excel. 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 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 –

    1. Use the Esc option and Undo.
    2. Delete the cells and save.
    3. Delete the cells and run the macro.
  1. 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 –

  • Scenario Manager in Excel
  • Checkbox in Excel
  • Combo Box Excel
  • Excel Tables
1 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Scroll Bars Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More