Let’s understand how to create a scrolling table in excel as shown below.
The data that is used to create the scrolling table is as below.
Step 1
Copy the header of the columns to the place where we want to create our scrolling table.
Enter 1 in the cell P3 to increment the row by 1 in the scrolling table when we click on the arrow symbol.
Enter the below formula in cell G4 as shown below.
=INDEX(A4:A$103,$P$3)
Step 2
Now we want our scrolling table to show only 10 records so drag the formula to show only 10 records in our table across the columns.
Step 3
Now format the table by using Format as Table option in the Home tab in excel.
Our scrolling table looks standard now.
Step 4
Let’s insert the scroll bar for our scrolling table from Developer->Insert->Form Controls->Scroll Bar.
Manually adjust the scroll bar control to fit to the table.
Step 5
Now right click the scroll bar and go to format control to set the properties.
Step 6
We will set the below mentioned properties to the values as per our requirement.
Leave Current Value as it is.
Set Minimum Value to 1.
Set Incremental Change to 1, means the increment will be of 1 record when the arrow in the scroll bar of scrolling table is clicked once.
Set Page Change to 10 means if the area within the scroll bar is clicked whether up or down from the selection, it is going to increment or decrement by 10 number of records in our scrolling table.
Set Maximum Value to the number calculated by following formula:
Maximum Value=No of rows in your data-(Page change-Incremental Change).
I have 100 rows of data in my dataset that’s why I have given 100-(10-1) =91
Set Cell Link to the reference $P$3 which is highlighted in pink.
Now you scrolling table in excel is ready.
Hope this helped.
