In this video, I demonstrate how to create a scrollable table in Excel. This might be useful in the context of a dashboard where you do not have the space to display the whole table and only want to display, say, ten rows at a time. Download the featured file here.
Here are the steps for creating a scrollable table in Excel.
1) The scrollable table will need a data source which is normally placed on a separate sheet to your dashboard. Link cells in your dashboard sheet to the column headings in your data source sheet. This way the column headings for your scrollable table will be visible. To create the link, enter an equals sign and then click on the first column heading in the data source sheet. You can then copy that formula across to pick up all the other column headings.
2) The next step is to create a scroll bar control. You will need to display the Developer tab on Excel’s Ribbon to do this. To display the Developer tab, right-click on a visible Ribbon tab – the View tab for example, and select Customise the Ribbon in the shortcut menu. In the Excel Options dialog box tick Developer in the list of tabs. Then click on OK to confirm.
3) On the Developer tab, which should now appear on your Ribbon, open the Insert menu in the Controls group, and select the Scroll Bar control under Form Controls. You can then draw a scroll bar onto your worksheet.
4) The scroll bar outputs a value to a cell in your workbook. Right-click on the scroll bar and select Format Control. There are two changes you need to make at this stage.
– Set the Minimum value to one
– In the Cell link box, select an empty cell in the data source worksheet (in my example that’s cell K1).
5) In the dashboard worksheet, click in the cell under your first column heading – in my example that’s cell A2 and type the following formula =OFFSET(Data!A1, Data!$K$1,0). This formula uses cell references and sheet names in my example. Data is the name of the data source worksheet and A1 is the first column heading in the data source table. K1 holds the output from the scroll bar. To learn how the OFFSET function works please watch the video. Note the dollar signs in the K1 reference: this is necessary to create a locked reference to cell K1. Now copy this formula across for the number of column headings in your table and down for the number of records you want to show at one time.
6) Now if you scroll you will see that it changes which records are visible on your worksheet. There is a problem though: if you scroll all the way to the bottom, you start to display zero value records. To avoid this scroll down as far as you can without displaying the first zero value record and note the first record number displayed in the table (if you don’t have record numbers – you are going to have to count the position of that record in your table).
7) Right-click on the scroll bar and select Format Control and enter the number you noted in the previous step into the Maximum value box. Click OK and now when you scroll down it will no longer display zero value records.