Excel Scrollbar (Table of Contents)
Scrollbar in Excel
A scroll bar in Excel is just like the one you have seen at many places while using a computer system. This is a thin bar mainly displayed in most of the computer applications to move the viewing area of computer applications, left, and right or up and down. Based on the direction of movement, the scrollbar is of two types:
- Horizontal Scroll Bar (Moves left and right)
- Vertical Scroll Bar (moves up and down)
How to Insert Scrollbar in Excel?
To create the SCROLLBAR in the Excel, follow these steps:
First, you need to enable the Developer tab visible on the ribbon so you can get to the VBA and the ActiveX control commands. To get the Developer tab, follow below steps
- Click on the Excel option to bring up the Excel Options dialog box
- Click Customize Ribbon.
- Select the Developer Check box.
- Click on the Excel Ribbon then select the Developer tab.
- Click on Insert then click the SCROLLBAR control to insert the new list box in excels worksheet.
- After that draw a rectangle in the excel worksheet to insert a ScrollBar.
If you will move the rectangle spread more horizontally then Horizontal Scroll Bar control will be inserted in Excel
If the rectangle is spread more vertically then a vertical scrollbar will be inserted in Excel.
Setting up a Scrollbar for a data set
In this section, we will understand a few properties of ScrollBar.
After inserting the scroll bar based on the instructions explained in the previous section, just right click on the scroll bar and select FORMAT CONTROL option:
This will open a dialogue box as shown below:
In the above box there are following options:
- Current Value: Scrollbar is always has a numeric value associated with it. Current Value field defines the current value of the scrollbar. Basically, it will define the current position of the scrollbar.
- Minimum Value: This defines the minimum position value of the scrollbar
- Maximum Value: This defines the maximum value possible of the scrollbar. The current value will always lie between the Minimum and Maximum Value.
- Incremental Change: This defines the number of values to be changed in one click movement of the scrollbar. If this is set to 1 that means if you will click the down arrow of scrollbar then its current value will increase by 1.
- Page Change: Provide the amount in Page change that the value increases or decreases and the degree to which the scroll bar moves if you click between the scroll box and any end arrow of a scrollbar. Let us take an example, in a scroll bar which has minimum value 0 and maximum value 10, if user put 2 in Page change property, the value of scrollbar will increase or decrease by 2 (in the same case, 20% of the value range of the scroll bar) when you click the area between the scroll bar and any of the scroll arrows.
- Cell Link: This value will contain a cell name, which will hold the current value of a scrollbar. You will use this value in other formulas to respond to the positioning of the scrollbar. Because of value in this cell now ill automatically get updated whenever you will move the scrollbar.
How to Use Scrollbar?
In the above section we have seen how to insert the scroll bar and set up its parameters, now we will create a simple scrollable area from a larger data set
Let’s just take an example of a simple data set of all 26 alphabets:
If you will create the above data set in an excel sheet it will consume 27 rows (1 for heading and 26 for each alphabet).
But with the help of scroll bar now we will try to adjust the above data set into fewer rows. To do so follow these steps:
- Create a space where you would like to display the data set with scrollbar:
- Click on the Excel Ribbon then select the Developer tab
- Click on Insert then click the SCROLLBAR control to insert the new list box in excels worksheet near the newly formatted space where you would like to display the scrollable data, as shown below.
- Now right click on the scrollbar and select Format Control option
fill following data for scrollbar parameters:
- Current Value: 0
- Minimum: 0
- Maximum: 16
- Incremental Change: 1
- Page Change: 2
- Cell link: Any empty cell (we are taking H1)
- Now in E2 cell type in the following formula:
- You can read more about the OFFSET formula later, but in Excel OFFSET function can be used when you want to get a reference which offsets the specified number of rows and columns from the starting point.
- Now copy paste the above formula in all cells of the desired area.
- This will make the value in cells updated each time you will move the scrollbar.
Recommended Articles
This has been a guide to Scrollbar in Excel. Here we discuss basic concepts like how to insert scrollbar in Excel, How to use the scrollbar and setting up a scrollbar along with practical examples and downloadable excel template. You can also go through our other suggested articles –
