Let’s understand how to create a horizontal scrolling table in excel as shown below.
The data that is used to create the horizontal scrolling table is as below, it has quarterly sales data from 1980 to 2010.
Step 1
Enter 1 in the cell B14 to increment the column by 1 in the scrolling table when we click on the arrow symbol.
Enter the below formula in cell B8 as shown below.
=OFFSET(A2,0,$B$14)
Step 2
Now we want our scrolling table to show only 5 columns so drag the formula to show only 5 columns in our table across quarter rows.
Step 3
Now format the table to make it visually appealing.
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.
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 column when the arrow in the scroll bar of scrolling table is clicked once.
Set Page Change to 5 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 5 number of columns in our scrolling table.
Set Maximum Value to the number calculated by following formula
Maximum Value=No of columns in your data-(Page change-Incremental Change).
I have 30 columns of data in my dataset that’s why I have given 31-(5-1) =27
Set Cell Link to the reference $B$14.
Now your scrolling table in excel is ready.
Hope this helped.
