Sometimes you just want to show a small section of your data set, like in a dashboard or a chart. With a scroll bar or spin buttons you can accomplish that easily.
Clicking the up or down arrows on the scroll bar changes the current record in cell range C3:F3. Below the cell range is the excel table where all the data is extracted from.
Here is how I did it
Insert a scroll bar
- Go to tab Developer on the ribbon
- Click "Insert" button
- Click Scroll bar
- Drag on sheet to create a scroll bar
- Right click on scroll bar
- Click "Format control"
- Change cell link to $A$3
- Click OK
Array formula in cell C3:F3:
- Select cell range C3:F3
- Type =INDEX(Table15,A3,0)
- Press and hold CTRL + SHIFT
- Press Enter
Use an event (vba) to automatically change scroll bar maximum range value
Activating the sheet runs the macro and sets the maximum value for the scroll bar. Example, if you add values to the table and it now has 250 rows. The event macro changes the scroll bar maximum range value to 250.
Private Sub Worksheet_Activate() With ActiveSheet.Shapes("Scroll Bar 1").ControlFormat .Min = 1 .Max = Range("Table1").Rows.Count .SmallChange = 1 .LargeChange = Range("Table1").Rows.Count / 10 .LinkedCell = "$A$3" End With End Sub
- Right click on sheet name
- Click on "View Code"
- Paste vba code to module
- Exit VB Editor and return to excel
The following example demonstrates scrolling through 10 records at a time. Clicking the scroll bar arrows changes the values in cell range C2:F12.
Formula in cell C3:
Copy cell C3 and paste to cell range C3:F12.
Formula in cell A4:
Copy cell A4 and paste to cell range A5:A12
This example shows a scrolling excel chart.
The last example shows conditional formatting highlighting current record.
Conditional formatting formula
Download excel *.xlsm file
Functions in this post
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.