Using INDEX to Scroll the X-Axis in Excel Charts

Excel

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.

Use INDEX to scroll the X-axis in an Excel chart.

Use INDEX to scroll the X-axis in an Excel chart.

This looks cool and what is better, it makes your work look professional.

Download the example file for Using INDEX to Scroll the X-Axis in Excel Charts

Click here to learn the basics of how the INDEX function works.

Click here to learn how to add the Developer toolbar that contains the scroll bar and drop-down form controls.

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.

Download the example file for Using INDEX to Scroll the X-Axis in Excel Charts.

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.

Click here to learn how to use INDEX and MATCH to lookup data

Click here to learn how to use INDEX and MATCH with multiple criteria for charting

Share the power...

Leave a Comment:

Leave a Comment: