Create Scrolling Table In Excel

Let’s understand how to create a scrolling table in excel as shown below.

Capture

The data that is used to create the scrolling table is as below.

Capture

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)

Capture

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.

Capture

Step 3

Now format the table by using Format as Table option in the Home tab in excel.

Capture

Our scrolling table looks standard now.

Capture

Step 4




Let’s insert the scroll bar for our scrolling table from Developer->Insert->Form Controls->Scroll Bar.

Capture

Manually adjust the scroll bar control to fit to the table.

Capture

Step 5

Now right click the scroll bar and go to format control to set the properties.

Capture

Step 6

We will set the below mentioned properties to the values as per our requirement.

Capture

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.




Capture

Hope this helped.

Share The Knowledge

Random Posts

  • Insert Minus Sign In Excel

    There are two types of challenges here first being inserting a minus sign in excel(or hyphen in string) and the […]

    Share The Knowledge
  • Delete/Remove Pivot Table In Excel

    Let’s see how to delete a pivot table in an excel sheet. Sometimes we want to delete the existing pivot […]

    Share The Knowledge
  • VBA Loop Is Missing Rows While Deleting Rows

    vba loop is missing rows while deleting records,. excel vba loop is skipping rows to be deleted, vba loop is deleting records but is also missing records, vba to delete records in loop based on some condition

    Share The Knowledge
  • Count Number Of Braces/<> In Excel

    The post demonstrated how we could count number of braces “<>” in Excel. I have personally seen people HTML developers […]

    Share The Knowledge

Leave a Reply