Ever wondered how to create an interactive Scroll Bar in Microsoft Excel? Instead of manually typing in a particular month or value, you can use the Scroll Bar control to select the months or values from a list, thus saving you time. In this week’s tip, we’ll show you how by using the Offset function along with the Scroll Bar.
The OFFSET function returns a cell or range of cells that is a specified number of rows and or columns within your spreadsheet. The range of cells is given a starting point with specified height (number of rows) and width (number of columns).You can use the OFFSET function to select a dynamic cell reference.
In this exercise, we’ll demonstrate how Months can be interactively selected with the Scroll Bar control and the OFFSET function.
Note: You are welcome to download the workbook to practice.
Applies To: Microsoft Excel 2010 and 2013
1. Activate the Developer Tab in your Excel spreadsheet. (Nb: The Scroll Bar control option is found on the Developer tab.)
- Select the File menu, then click on Options
- Select Customize Ribbon
- Tick the Developer check box, then click OK
2. Insert the Scroll Bar Control.
- Select the Developer tab, then select Insert under the controls group
- Then select the Scroll Bar from the Form Control group
- Cut and paste or place the control on the Dashboard worksheet, in the range Q2:Q4.
3. Format the Scroll Bar Control (Nb:To make the Scroll bar control interactive using the OFFSET formula, you first need to change some of the Scroll Bar control settings.)
- Right click on the Scroll bar control, then select the Format Control menu item
- Set the maximum value to 11(number of months, excluding the starting point which makes it 12)
- Click the red arrow (edit reference icon) in the Cell link
- On the Lists worksheet, select cell B14 (the output of the Scroll bar, a number representing the selected month)
- Then select the red arrow (edit reference icon) to return to the Format Control window, then select the OK button
4. Using the OFFSET function to return the Month name.
- Select the Dashboard worksheet, then select cell N3
- Select the Formulas tab, then select Lookup & Reference
- Select OFFSET
Complete the fields as follows:
-Reference: the specified starting point.
-Rows: Number of rows you want to move from the starting point.
-Cols: Number of columns you want to move from the starting point.
-Height: This is the size in rows of the range you want to be returned.
-Width: This is the size in columns of the range you want to be returned.
- Select the red arrow (edit reference icon) in the Reference
- Select cell B1 on the lists worksheet, then select the red arrow (edit reference icon) to return.
- Select the red arrow (edit reference icon) in the Rows
- Select cell B14 on the Lists, then select the red arrow (edit reference icon) to return.
- Place your cursor in the Cols box, enter 0 and select
The months can now be interactively selected by using the Scroll bar control.
