Use INDEX to simulate a scrolling box of data in cells anywhere on your worksheet. It gives your spreadsheet users the ability to see and scroll through a large amount of data in a small viewing area. You might use a small viewing area like this below a chart to show data from a large database, records from an accounts receivable database, sales history, etc.
This cool trick works in Excel versions that go back as far as the first Excel that contained a scroll bar. It’s been to long for me to remember which one that was, but it’s been a while.
In this example, a Microsoft Balance Sheet is in the cells C24:H50. The balance sheet is far too large to fit into the small viewing area in C7:H17. Using the scroll bar to the right side of the viewing area, in column I, the user can drag the scroll bar down to scroll the viewing area through the entire database.
The large database can even reside on another worksheet. Using this technique you can put the simulated viewing window anywhere there are cells in which you can type the INDEX formulas.
A scrolling bar is used down the right side of the viewable area to make the contents appear to scroll. To create the scrolling bar you will need to have access to the Developer tab in Excel.
The cells K7:K17 specify which rows will be retrieved from the database. These cells are going to be under control of the scroll bar in I7:I17. Create these cells by typing 1 in cell K7. In cell K8 type =K7+1 and copy it down to K17. When the scroll bar changes the contents of cell K7 all the cells below.
Click here to learn how to draw form controls using the Developer tab. These instructions show you how to create a drop-down List, also known as a Combo list. Follow these same instructions, but add a scroll bar instead of a drop-down list.
Draw the scroll bar by clicking on the Developer tab. In the Control group select Insert, then in the Form Controls, click the Scroll Bar. Drag across the range I7:I17 to draw the scroll bar. Right click the scroll bar and choose Format Control to display the Format Control dialog box where you can set the limits for how the scroll bar works.
A vertical scroll bar with these settings controls INDEX to make the data appear to scroll through a database.
One you have created the scroll bar and the formulas in column K you should see the Scroll Index numbers in K7:K17 “scroll” as you drag the scroll bar up and down.
The index formula in cell C7 displays the word Asset from the balance sheet data in cell C24. Before discussing the long formula shown in the figure, let’s look at the part of the formula in C7 that retrieves the data,
In the figure above you can see that C$21 contains 1 for the first column of the data range, $C$24:$H$50 and $K7 contains 1 for the first row of the data range. The row 21 contains a column number for each column of the data range. As the INDEX formula is copied across it references columns 1 through 6 as specified in row 21.
If you are not familiar with how INDEX works, then
The magic part of the INDEX formula that makes it appear to scroll occurs as the row reference $K7 increases as the scroll bar is dragged down. The cells below K7 contain formulas that add one to the content of the cell above. As K7 increases the cells below also increase. When this is done for the entire viewing area then C7:H17 appears to contain scrolling data.
Enter the formula
in C7. Copy it across and then down to fill C7:H17.
Now, when you drag the scroll bar down the Scroll Index changes. That in turn changes the rows retrieved by the INDEX functions and the viewing area appears to contain scrolling data.
But, there’s one problem! Blank cells in the database appear as 0 (zero) in the scrollable viewing area.
If a blank cell is referenced by the INDEX formula, then 0 (zero) appears in the viewing area. If it’s going to look professional you don’t want that to happen.
Change the formula in C7 to,
Copy this formula across and down to fill the viewing area. All references to blank cells should now appear as blanks due to the “” in the IF formula.
This is a very handy and easy way to show a lot of data in a small area. One additional trick you can use that will really make everyone sure that you are an Excel superstar is to add a horizontal scrolling bar at the bottom of the viewing area. Link that horizontal scrolling bar to formulas that control the column references in C21:H21. You should be able to do this with what you know now.
Just so you can feel the sense of accomplishment, I’ll leave the horizontal scroll bar for you to do. 🙂