When there are huge data rows in your excel worksheet, you had better add a scroll bar to navigate the data rows. This article will tell you how to add a scroll bar to a cell range to scroll your data rows easily in the excel cell range.
1. How To Add Scroll Bar In Excel.
1.1 Add Scroll Bar In Excel Example Description.
- Below is this example excel worksheet.
- Column A contains the month list which contains 12 months.
- Column C contains a cell range that displays part of column A.
- There is a scroll bar beside column C.
- When you click the arrow of the scroll bar, it will scroll to display different month values listed in column C in order.
- You can see there is an integer number value in cell $D$3.
- When you scroll the scroll bar, the integer number in cell $D$3 will increase or decrease accordingly.
1.2 How To Add Scroll Bar In Excel.
1.2.1 Add Scroll Bar Into Excel Worksheet.
- Click the excel Developer tab on the excel top area, if you can not find the tab, you can read the article How To Enable Developer Tab And Write VBA Source Code In Excel.
- Then click the Insert down arrow in the Controls group ( Windows ).
- Then click the ScrollBar control and drag & drop it into your excel worksheet ( Windows & macOS).
- Place the ScrollBar control on the right border of column B.
- When you right-click the ScrollBar, you can adjust the control width and height by dragging the border or corner square of the control.
- When the scroll bar width is bigger than it’s height, it will scroll horizontally, when the scroll bar width is smaller than it’s height, it will scroll vertically.
- In this example, we want the scroll bar to scroll vertically.
1.2.2 Configure The Scroll Bar Control Settings.
- Right-click the scroll bar, then click the Format Control… menu item in the popup menu list to pop up the Format Control dialog window.
- It will select the Control tab by default, if not, you should click the Control tab to display the scroll bar settings tab. You can configure the scroll bar properties in this tab.
- The scrollbar control has an integer number value property, when you scroll the scrollbar, the integer value will be changed also.
- You can set the scroll bar’s current integer number value in the Current value input text box, it usually is 0 or 1 when you design the scrollbar.
- The Minimum value text box can be used to configure the minimum integer value that the scroll bar starts to scroll.
- If you set the Minimum value text box to 5 then the beginning of the scrollbar number value is 5.
- The Maximum value text box is used to set the maximum integer number of the scrollbar.
- If you set the Maximum value text box to 10 then the ending of the scrollbar number value is 10.
- The Incremental change text box is used to set the step number when you scroll the scrollbar, if you set its value to 1, then the scrollbar’s current value number will add 1 when you scroll down the scroll bar for one step.
- The Page change text box contains the number value that will be added or minus from the scrollbar’s current number value when you click the area between the scrollbar’s up and down arrows.
- The last text box Cell link should contain an absolute cell address such as $D$3 ( you can input any cell’s absolute address in it ).
- When you scroll the scroll bar, the scrollbar’s current value ( an integer number ) will be stored in the cell that you input in the cell link text box.
1.2.3 Use ScrollBar To Scroll Data Cells.
- In this example, we want to display only 6 months in the scroll list.
- And then show other months in the list accordingly when you scroll the scroll bar.
- Input the formula =OFFSET(A3,$D$3,0) in cell C4.
- Click to select cell C4 and mouse over the small square in the bottom right corner of cell C4.
- When the mouse pointer is changed to a black cross icon, click the mouse pointer and drag it to cell C9.
- You can adjust the scrollbar’s height to the same height as between cell C4 to cell C9.
- It will add formulas in other cells of the cell range C4:C9.
- Select one cell such as cell C6, the formula in cell C6 is =OFFSET(A5,$D$3,0).
- The OFFSET function will return the target cell value.
- The target cell starts from cell A5 and moves $D$3 number of rows and 0 columns.
- We know that cell $D$3 contains the scrollbar’s current value number, so if the scrollbar’s current value number is 1, then the OFFSET function will return the value of cell A6.
- So it will display the text March ( from cell A6 ) in cell C6.
- Now when you scroll the scrollbar, the scrollbar’s current number value is changed, you can see this in cell $D$3.
- And each cell in cell range C4:C9 will recalculate the formulas in the cell and get and show the new text from cell range A4:A15 accordingly.
- Then you will find the text in cell range C4:C9 scroll up and down when the scrollbar scroll.