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
• Hit Comma and enter zero for columns as we don’t want the columns to move
• 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
• Close your brackets
• Copy your function across 3 cells in my example and 10 rows down
• Hit the scroll bar and test!

Enable The Developer Tab In Excel

Open A Spreadsheet To A Particular Sheet And Cell