Create A Scrollable List In Excel

This tip is really useful for Excel dashboards or anywhere that space is of a premium on a worksheet or, where your data set is just too large to comfortably display.

Let’s look at using a scroll bar to show only 10 records at a time of a data set.

Here we have a sheet of data which is way too large to display on a dashboard in a worksheet called Data.

Also in our workbook we have a worksheet called Dashboard and one called Calculation. the dashboard sheet will contain the scrollable 10 rows of data and calculation will keep track of our starting record and performs navigation of the records, so as we scroll our way through the 150 records.

We also have a sheet for displaying only the 10 rows- in this instance called Dashboard and another sheet which holds a Calculation in cell B3. This will keep track of our starting record. So as we scroll through to record 150, cell C3 will keep track.

So, lets insert a scroll bar

On the Developer Tab (if no developer tab is available – enable it by following these instructions)

Insert Form Control- Scroll Bar Form Control- Draw a Scroll bar

We need to format the scroll bar- Right Click and hit Format Control- Control Tab
Enter Maximum Value- in this case it is 138 as my data starts in row 2- and I want to display 10 rows
Cell Link is is Calculation Sheet cell B3
Hit Ok and that is the scroll bar done!

We now need to use the OFFSET function into each of the cells on the Dashboard sheet to display the snapshot of data from the larger data set.

Start in cell B4, type = OFFSET(

Enter the first reference for Excel which is cell A2, the start of the first record

Hit comma and next we need to decide is how many rows to move off that- that information is in Calculation Sheet, cell B3- make that reference absolute- so we can easily copy the function across the list we want to display and the cells will always reference B3