Add a scroll bar to a data table (and other features)

Imagine you have a table of monthly results.  Imagine that this table is really large and that it’s difficult to distinguish the results that you really want to see.  Instead of having rows and rows of data that is difficult to look through, you can add conditional formatting, sort the data and add a scroll bar to allow you to see the results you really want to see.

Basically how can you turn this:1

Into this:2

Step 1: Make the developer tab visible

If it isn’t already visible, you will need to make the developer tab visible on the ribbon UI.

Click File > Options > Customise ribbon.
Ensure that the developer tab is ticked in the Popular commands section.3

Step 2: Create the basic data tab

  • Firstly import your data into an xlsm file into a tab called “data”.  (See attached file: example data table with scroll bar)
  • Then add four rows above the data.
  • In cell A1 type “Slider Calculation” as a title
  • In cell A2 type “Start Position”
  • In cell A3 type “Maximum Position”
  • In cell B2 type 1
  • In cell B3 type =COUNTA(A6:A3000)-9 (You can of course adjust this if you have more than 3,000 rows of data)

These cells will be used to manage the slider bar once it’s added.

Step 3: Add the button & vba to sort the table automatically

  • Click on Developer > Insert > Button



  • Draw the button on the sheet inside the top few rows.
  • Right click on the new button and choose “Edit Text”
  • Type “Update table”
  • Then click on Developer > Macros
  • Type in the name “Macro1” and then click on the create button


  • Once visual basic has opened, copy and paste the text below.

Sub Macro1()

‘ Macro1 Macro
ActiveWorkbook.Worksheets(“Data”).Sort.SortFields.Add Key:=Range(“F6:F43”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Data”).Sort
.SetRange Range(“A5:H43”)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
End Sub

  • Save the macro and exit visual basic.  (Please note, I’ve written the macro so that it sorts the data by column F from minimum to maximum and that the table ends at row 43 and column H.  You can of course alter this to meet your needs.)
  • Right click again on the button and choose assign macro
  • Ensure macro 1 is selected and press the OK button
  • Test the button and ensure that it works.

Step 4: Create the Dashboard tab

  • Then create a new tab called “Dashboard”.
  • In cell A2 type =OFFSET(Data!D6,Data!$B$2,0,1,1)
  • D6 is the first line of data of the first column of data you wish to display (I’ve chosen the person’s first name).
  • In cell A3 type =OFFSET(Data!C6,Data!$B$2,0,1,1)
  • C6 is the first line of data of the second column you wish to display  (This time I’ve chosen the surname).
  • Repeat this configuration until you can see the first line of data you wish to see in the order you wish to see it.
  • Highlight the entire row and position the cursor over the bottom right corner.  Click and drag the cursor until you can see 10 rows of data.
  • Add your titles and units to the table as needed
  • Insert a column for the scroll bar.
  • Click on Developer > Insert > Scroll bar


  • Draw the scroll bar in the empty column, then re-size the column to fit the slider width.
  • In cell D1 type: =IF(Data!$B$2>1,”▲”,””)
  • In cell D12 type: =IF(Data!$B$2<Data!$B$3,”▼”,””)
  • (You may need to copy and paste the symbols from character map or similar)
  • These two formulae will display and up and down arrow to let the user visually see if there is more data above or below the current lines showing.
  • Right click on the slider bar and choose “Format control”


  • Add the data as shown above:
    • Current value: 1
    • Minimum value: 1
    • Maximum value: (value from Data!B3)
    • Incremental change: 1
    • Page change: 10
    • Cell link: Data!$B$2
  • Then press the OK button.

Step 5: Conditional formatting

  • Add the lines and borders to the table as you wish
  • Then highlight the table and click on Home > Conditional formatting > New rule
  • Then select “Use a formula to determine which cells to format”


  •  In the rule description type: =$F2>=0.041666666667 (the $ sign tells the rule to always look at column F regardless of which column it is formatting.  I’ve used this number as it is the value of 1 hour as a decimal value in Excel.)
  • Then choose the format you wish to apply.  I added green text and a light green fill

Your table should now be complete.  Make sure you save it as an xlsm file to allow the macros to run.  Don’t hesitate to download the file below or post a comment if you need further explanation.

example data table with scroll bar

12 thoughts on “Add a scroll bar to a data table (and other features)

  1. I like the valuable information you provide in your articles.
    I will bookmark your blog and check again here regularly.
    I am quite sure I’ll learn lots of new stuff right here! Best of luck for the next!

  2. I think that is an interesting point, it made me think a bit.
    Thanks for sparking my thinking cap. Sometimes I get so much in a rut that I just feel like a record

  3. This is really interesting, You are an excessively skilled blogger. I have joined your rss feed and look ahead to in the hunt for extra of your fantastic post. Also, I’ve shared your web site in my social networks

  4. Pingback: Homepage
  5. I’d like to sort a response surface model, including the scroll bars.

    So I have a large number of inputs and one or more response metrics. I’ve done all sorts of clever tomfoolery to understand what the sensitivities of my response metrics are to the inputs. It’s easy to arrange all the sensitivities in a column and it’s easy to add a scroll bar next to each of them to manipulate the value of the input, so another user can use the sensitivites I’ve calculated, experimenting with the values to get an output without needing access to my original model. So far, so good – I have no problem in doing this.

    But here’s the problem. I’d like to arrange my column of sensitivities in a sorted order. Let’s say largest first. Or alphabetically by name. Or something. I can manually sort them by selecting one row at a time, cutting and pasting it somewhere else, mobing them around like some kind of Chinese puzzle. But if I select the area including the scroll bars and sort it, the associativity between the scroll bars and the rows falls apart.

    So will the solution be to code some kind of bubble sort cut and paste macro, or is there some other way?

    Opinions gratefully received!

    1. Send me your sheet. Depending on the way it’s laid out will depend on the answer.

      You could add vba and some buttons, so that it automatically sorts the data based on the button you chose, but I’d need to look at it to be sure.

      I suspect that you can get round this by creating a pivot table in the background, and your data in the scrolled table is fed from this pivot! You can then sort the data in the pivot table without losing the scroll bars – but I’d need to see it to be sure!

  6. I’ve looked at your sheet and you will need vba. I would create a button. That button should do several things:
    – sort the data based the column of your choosing
    – delete any existing sliders which will no longer work once you’ve sorted the data
    – recreate the sliders in the correct position with the correct inputs / outputs
    – refresh any calculations

    You should be able to modify the vba in the post above to do most of this. To delete the sliders you can add something like this:

    Sub deletesliders()

    ActiveSheet.Shapes.Range(Array(“Scroll Bar 1”)).Select
    ActiveSheet.Shapes.Range(Array(“Scroll Bar 2”)).Select
    ActiveSheet.Shapes.Range(Array(“Scroll Bar 3”)).Select

    End Sub

    Don’t hesitate to record a macro for the code you don’t have. Good luck!

Leave a Reply

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

You are commenting using your 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