Vertically Scrolling Dynamic Sparklines Alongside a Database

Charts

This Excel tutorial combines INDEX and Sparklines to create a window of vertically scrolling data and scrolling Sparklines. The “scrolling window” looks into a much larger database. The result is a very functional reporting technique that is cool and takes little space in Excel reports or dashboards. If you put the large database in a hidden worksheet and the scrolling window in the dashboard, then it’s cool, functional, and invincible.

Scrolling Dynamic Sparklines down a Vertical Database

Adding a scrolling data window with Sparklines adds functionality to your dashboard.

Click here to download the Advanced Sparkline tutorial.

The range F7:P15 is a scrolling window of data that displays data pulled from the larger database in E21:P70. The scroll bar in column Q enables the user to scroll the window up or down. Of course, to make your scrolling data window both usable and invincible you might want to put the large database in a hidden protected worksheet.

One of the cool tricks here is the scrolling Sparklines in range E7:15. As the data scrolls up or down in the window, the Sparklines scroll with their respective data. In reality it’s a trick. The Sparklines stay still and just refer to the data to their right which is scrolling up and down. (Like real magic the scrolling is just an illusion. Sorry Harry Potter fans!)

The database is a range of median family incomes for states in the United States in F7:P15. Of course, it could be much larger and on a separate, hidden worksheet. The appearance of a scrolling window is created by using INDEX functions to retrieve data from the database and display it in the window’s range F7:P15. The numbers in row 16 identify which columns of data are retrieved from the database. The number in cell S7 added to the row index numbers in C7:C15 give the row in the database to retrieve from. When the number in cell S7 changes the data in the “window” seems to move up or down. Dragging the scroll bar up or down changes the number in $S$7.

Click here to learn more about how to use INDEX to a scroll a window of data.

The syntax for the INDEX function is,

=INDEX(array,row_num,column_num)

The INDEX formula in cell F7 is,

=INDEX($E$21:$P$70,$S$7+$C7,F$16)

The array is the database that data is retrieved from $E$21:$P$70. The column_num for each column is in row 16. You’ll find it’s much easier and more flexible to enter column numbers like this than to hard code them in the formula.

The “magic” is done by the row_num specified by $S$7+$C7. The row_num is created by adding the changeable S7 to the Row Index in C7:C15. Dragging the scroll bar up or down changes the number in S7.

Click here to learn how to create a scroll bar or other form control like a drop-down menu. Creating a scroll bar uses the Developer tab and Form Controls just like a drop-down menu, but instead of creating a drop-down menu, you use a scroll bar.

The Format settings for the scroll bar are,

Current Value 14 (this is current position)
Minimum Value 1
Maximum Value 42
Incremental Change 1
Page Change 8
Cell Input $S$7

 

Click here to download the Advanced Sparkline tutorial.

Click here to learn more about how to use INDEX to a scroll a window of data.

Click here to learn how to create a scroll bar or other form control like a drop-down menu. Creating a scroll bar uses the Developer tab and Form Controls just like a drop-down menu, but instead of creating a drop-down menu, you use a scroll bar.

 

Share the power...

Leave a Comment:

Leave a Comment: