Home > Excel Tutorial > Create A Dynamic Scrolling Table In Excel

Create A Dynamic Scrolling Table In Excel


If you have too large worksheet table , in such a table ,It is difficult to examine the table and to distinguish the results .

We can create a scrolling table using scrollbar control to overcome this problem. This is a great way to allow more data in a small space. When a user changes the scrollbar, the data accordingly changes.

– Before ,a scrollbar is added to the worksheet. A scrollbar to add to the sheet :

Go to Developer Tab –> Insert –> Scroll Bar (Form Control).

excel scrolling table

Click on Scroll Bar (Form Control) button and click anywhere on your worksheet.

Right click on the Scroll Bar and click on ‘Format Control’. This will open a Format Control dialogue box.

In Format Control dialogue box go to ‘Control’ tab, and make the following changes:

Current Value: 1

Minimum Value: 1

Maximum Value: Enter any value (It will be created with codes in worksheet module)

Incremental Change: 1

Page Change: 10

Cell Link: $K$2

–  Column headings are entered with formulas starting from cell B2 (=Data!A1,=Data!B1)

– The following formula is entered in the first cell (B3) and copied it to fill all the other cells:

 =OFFSET(Data!A2;$K$2;0;1;1) .  OFFSET formula is dependent on cell K2.

– Following Formula is entered to cell K4 :

=COUNTA(Data!$A:$A)-1

– Lastly following codes are entered to worksheet module in VBA Window to create dynamic  scrollbar (for scrollbar max value) :

     Private Sub Worksheet_SelectionChange(ByVal Target As Range)

     Set Target = Range(“K6”)

    ActiveSheet.Shapes(“Scroll Bar 1”).ControlFormat.Max = Target.Value

    End Sub

Click Here To Download Template

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: