Scroll through time in your Excel charts by combining an INDEX function with a scroll bar. This is a great way to give your dashboard users the ability to scroll through a lot of data so they can see how data changes over time.
This looks cool and what is better, it makes your work look professional.
Click here to learn to insert and create a scroll bar or drop-down list. These instructions describe how to insert a drop-down list (Combo list), but the instructions are the same for inserting a scroll bar, just select the scroll bar from the Form Controls instead of selecting the Combo list.
The concepts of how this works are very straightforward. The drop-down list is used to select a state. The number of that selection, shown as 6 in G12, is used to select the row from the database of data. This works because of the source for the drop-down list is the left column of the data so the number of the selection from the drop-down matches the same row in the database.
A slider control, which you can see at the bottom of the chart, changes the number in G16 when the slider is moved. Moving the slider to the right increases the number in G16. Because the slider is full left in the figure, G16 is 0 in the figure.
The slider in this example has control form values for the width of the data in this example. When you create your slider you will need to change the maximum, minimum, incremental, and page values. To do that right click the scroll bar and choose Format Control. This slider uses the values,
Minimum Value 0
Maximum Value 5
Incremental Change 1
Page Change 5
Cell Link $G$16
Notice that the Maximum Value is 5 to prevent the user from scrolling past the right edge of data which would produce an error in the chart. The number 5 was used because there are 5 additional cells of data to the right of those that were plotted.
The data to be charted is in F19:L20, but this not static data. The years in row 19 and the state name and data in row 20 are selected from the database with the use of the INDEX function. When a state is selected from the drop-down, the row number in G12 changes and INDEX selects data from the row in G12. When you move the slider it changes the increment in G16 and that changes the starting column for the first year which is in G20. The years after the first are just incremented by +1. Drag the slider and the data changes which changes the chart.
Here are the formulas,
G14 State =INDEX($B$24:$B$75,$G$12,1)
G19 First Date =INDEX($C$23:$M$23,1,$G$16+1)
H19 Second Date =G19+1
I19…L19 More Dates Copy H19 to the right
F20 State =G14
G20 First Data =INDEX(db_MedianIncome,$G$12+1,C$22+$G$16)
H20 Second Data =INDEX(db_MedianIncome,$G$12+1,D$22+$G$16)
I20…L20 More Data Copy H20 to the right
In this example the database has the range name, db_MedianIncome. You could also use the absolute reference $B$23:$M$75.
Combining this technique of scrolling through time with the INDEX MATCH combination to select and chart data creates amazingly useful, powerful and animated charts. Your users will be able to lookup data, chart it, and then scroll through time periods to see change.