A Scroll Bar in Excel is what you need when you have a huge dataset and you don’t want it to hijack your entire screen’s real estate. It’s a great tool to use in an Excel Dashboard where you have to show a lot of data in a limited space. In this step-by-step tutorial, I will show you how to create a scroll bar in excel. You will also learn how to link a dataset to this dynamic scroll bar, such that when a user changes the scroll bar, the data accordingly changes.
Creating a Scroll Bar in Excel
For the purpose of this tutorial, I have taken the data for 28 states in India, along with each state’s area and population (census 2001). Now, I want to create a data set that displays only 10 states at a time, and when the user changes the scroll bar, the data dynamically changes. Something like this shown below:
Click here to download the example file
Steps to Create a Scroll Bar in Excel
Note that this OFFSET formula is dependent on cell L3, which is linked to the scroll bar. Now you are all set with a Scroll Bar in Excel. How does this work? The OFFSET formula uses cell C3 as the reference cell and offsets it by the values specified by cell L3. Since L3 is linked to scroll bar value, when the scrollbar value becomes 1, the formula refers to the first state name. When it becomes 2, it refers to the second state. Also, since C3 cell has not been locked, in the second row, the formula becomes =OFFSET(C4,$L$3,0) and works the same way. Try it yourself.. Download the file
Create Dynamic Labels in Scroll Bar in Excel. How to Turn OFF Scroll Lock in Excel? Adjust Scroll Bar Maximum Value based on a Cell Value in Excel. How to Insert and Use a CheckBox in Excel. How to Insert and Use Checkmark and Crossmark symbols in Excel. How to Insert & Use a Radio Button in Excel. Creating Dynamic Filter in Excel.
I am very much pleased to see your work in excel vba. I have worked a lot on my worksheet but at a particular stage…I have a problem that I have a list of Months from March to February…that I want to scroll down from button ………So would you help me to set a such a button and vba code to that button that should work as I want and would you send me to bhaiswarpravin@gmail.com Please sir…….. For example, now i have 18 sets of data and i only want my scroll bar to have a maximum of 18 data but as i add on more data, is there a way that my scroll bar can detect it?
