Excel Indexing and Spinner Controls

​By Shawn LaRoche, Research Analyst, Mount Wachusett Community College

Myriad options exist for selecting subsets of data and analyzing them in Excel. Many analysts use complicated tables and often need coding and/or programming skills to make data meaningful. However, other analyses are simple and do not require any complex formatting, programming, or coding. An Excel “spinner" control used in tandem with indexed data is a useful option for simple analysis of small- or medium-sized datasets.

The only Excel-specific skill needed is “indexing” Many online overviews of indexing exist, including a Microsoft support page. There are two types of indexing; array indexing and reference indexing. Examples of both and how they work with spinners are provided in this article.

Note: The instructions provided are applicable for Excel versions 2007 and 2010. Minor variations may exist for older and/or newer versions. Users will need to ensure the spinner control is enabled. In Excel versions 2007 and newer, the Developer tab must be active. If the Developer tab is not active, follow these steps:

1. Open File and open Options.

2. Open Customize Ribbon.

    a. Ensure Developer and all subsections are checked. If not, select Developer and all subsections from box on left and add to box on right.

3. Return to the Home screen and Developer will appear as a tab.


Let’s start off with a simple analysis: headcount by program over time. The figure below shows sample, cross-tabbed headcount data by program over a five-year period.


Begin by indexing the data. Because the headcount example is contained in a set of rows and columns and is limited to only one variable (i.e. program), use the array indexing option (i.e. =INDEX([array],[row_num],[column_num]). In the example, the array = A3:F9 because that is where the dataset resides. The row_num and column_num vary by data point:

  • Row_num
    • Will be an “absolute reference,” meaning we are going to refer to the same cell in all formulas
    • To create an absolute reference, use a dollar sign before the column letter and before the row number of the cell being referenced (i.e. $A$1, $B$2, etc.)
  • Column_num
    • Refers to the number of the column we are referencing
    • In the headcount example, there are six columns (i.e. Column A = 1, Column B = 2, etc.)

Next, select any cell in the worksheet and insert a number 1. For reference, I chose cell G12. Include the headings row in the row above the cell selected and to the right by one column. This can be done by copy and paste or more preferably, by referring to the cells as seen below.


Now that the header row and cell references are in place, enter index formulas. In cell H12, enter the formula as, =INDEX($A$3:$F$9,$G$12,1). Repeat the formula in cells I12 through M12, changing only the column number, which will increase incrementally by one. 


With the data indexed and header rows in place, a spinner control can now be inserted. An Excel spinner control is essentially an up and down arrow that allows users to scroll through results. To insert a spinner control:

1. Open the Developer tab, select Insert, and choose Spin Button from the Form Control options.

2. Insert the Spin Button into the worksheet and chose Format Control. 

3. A pop-up box will appear and allow users to set control parameters

Note: All parameters included below are specific to this example and should be changed to meet user needs:

  • Current Value = 1
  • Minimum Value = 1inimum Value = 1
  • Maximum Value = 7 (there are seven rows of data)
  • Incremental change = 1
  • Cell link = $G$12

Note: The ‘Page change’ option is not used with spinner controls

4. Once the control parameters are entered, click OK and begin using the spinner control.

An additional benefit of using this method is the ability to graph results and quickly change from one program, or whatever data concept is in use, to another. Simply graph the results using the indexed data and when “spinning” through the results, the graph will change along with the data in the cells. The graph should be set up with scaling, graph type, etc., to meet user needs.

An additional example shows how users can use reference indexes to compare multiple data concepts. Building on the previous example, let’s add in campus as an additional variable. For the sake of simplicity, this analysis will compare four programs across two campuses.


To create a reference index, we follow almost all of the same steps as an array index. However, our dataset includes an additional variable, and the output will require two rows of indexed data rather than one. Follow these steps:

The formula will now include a "reference" and an "area number" rather than an array and should read as follows; =INDEX([reference],[row_num],[column_num],[area_num]). The cells in the reference section will access the data points specified by the user. In our example, the dataset resides in cells A3:G10. However, we need to include two data ranges since we are analyzing two separate sets of information (i.e. data by program and campus). Thus, the reference range will need to appear as A3:G6,A7:G10. The row_num, column_num, and area_num vary by data point:

  • Row num – Will be an absolute reference as described in the prior example
  • Column num
    • Refers to the number of the column we are referencing
    • In the example, there are seven columns (i.e. Column A = 1, Column B = 2, etc.)
  • Area num
    • Selects a range in the reference from which to return the intersection of row_num and column_num
    • The first area selected or entered is numbered 1, the second is 2, and so on.
    • This example uses two distinct reference ranges

Next, select any cell in the worksheet and insert a number 1. For reference, I chose cell H12. Follow this by including the headings row in the row above the cell selected and to the right by one column (as described in prior example).

Enter index formulas for the first row of data (see figure 12). As noted, the formula should read =INDEX([reference],[row_num],[column_num],[area_num]). In cell I12, enter the formula as =INDEX($A$3:$G$6,$A$7:$G$10,$H$12,1,1). Repeat the formula in cells J12 through O12, only changing the reference the column number which will increase incrementally by one. The formula shown in figure 12 will return the value for the 7th column (in the example, the data for 2014). Repeat these steps in cells J13 through O13 and change the area_num from 1 to 2.


Now create a spinner control that will control the row that is referenced in the formula allowing users to scroll through program results for campus X and campus Y. Follow the same steps as described in the array example EXCEPT be sure the cell link is updated and the number of rows is correct:

  • Current Value = 1
  • Minimum Value = 1
  • Maximum Value = 4 (there are eight rows of data overall but only four rows per data range)
  • Incremental change = 1
  • Cell link = $H$12

Note: The ‘Page change’ option is not used with spinner controls

There are now two rows of data available that will produce a headcount of the same program by campus. The first row will produce data for the first area of the dataset (i.e. cells A3:G6) and the second row will produce data for the second area of the dataset (i.e. cells A7:G10). Using the spinner control will update data in both rows and update any graphs using these cells.


Note: when using spinner controls, users must have output for all scenarios the variable may produce, even if there is no data. For example, see the data for program B at campus Y: all fields are 0. If this row of data is not included in the data, users will encounter issues since the spinner control will provide data for the next available row. In this example, users compare program B at campus X, to program C at campus Y, if the row with zeros is not included.

Using indexes in tandem with spinners is a useful and simple way to look at small- to medium-sized datasets without extensive formatting and it does not require extensive coding or programming.

Good luck using it with your own data and please share ways you may have used this methodology in other analyses, below.

 

 Comments

 
To add a comment, Sign In
Total Comments: 2
 
Lee posted on 5/18/2015 3:34 PM
Thanks Shawn! This is an interesting and helpful example - I learned some new Excel tricks!
Barbara posted on 5/19/2015 5:26 PM
Excellent. I've already passed it on! Barbara.