Excel Tutorial How To Create A Scrollable List In Excel

Space on an Excel worksheet is usually at a premium, in particular when you are designing and creating a Dashboard. By it’s very nature a dashboard needs to display the most important pieces information to enable the user to make informed business decisions.

You could make your charts or graphs larger to accommodate for data, but this is not always feasible or your Excel window may not be large enough if you have data that trends over a long period of time, say one year and it spanning four quarters.

So, if you have a table of data you want to show, but do not want to show all of your data set, say only one months worth, or a business quarter. One method of doing this is to make your data scrollable, so the user can scroll through the table however many rows of data at a time that you (or they) choose.

Let’s go ahead and make a scrollable list and work through an example.

In this example we have our data set on one Excel sheet named DATA. The data contains weekly sales volumes of 150 rows.

We then have another worksheet called CALCULATION.

A third sheet will be our DASHBOARD display sheet.

The Dashboard sheet will contain 10 rows of scrollable data, the calculation sheet will keep track of our starting record, as well as performing the navigation of our records as we scroll thought them.

OK let’s firstly put a scrollbar on our Dashboard display sheet:-

1. 
   On the Developer Tab -Insert Form Control- Draw A Scroll Bar


2. 
   Format the Scroll Bar- Right Click- Format Control- Control Tab


3. 
   Enter Maximum Value in this case it is 138 as my data begins in row 2 and my data contain 150 rows


4. 
   Hit OK and the Scroll Bar is completed and ready to use

Now, let’s move onto setting up the OFFSET function. We need to insert this in each of the cells on the Dashboard sheet that we want to use to display the 10 rows of scrolling data.

1. 
   Starting in cell B4 begin by typing =OFFSET(


2. 
   Enter the first reference we require which is DATA!A2, this is the start of the data set we want to display (the DATA sheet and cell reference A2).


3. 
   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-using the F4 key, so we can easily copy the function across the list we want to display and the cells will always reference B3


4. 
   Next two references are 1 and 1 for one cell value I.e the height and width of what we want to display is just the 1 cell


5. 
   Close your brackets then copy your function across 3 cells in my example and 10 rows down


6. 
   Hit the scroll bar and test!

You should now have a scrolling set of data in Excel. Well done.

Excel Tutorial How To Create A Scrollable List In Excel