How to Create a Table of Content of all Spreadsheets Within a Workbook

You probably have one workbook that contains most of the spreadsheets that you use on a daily basis, and scrolling from left to right, and right to left to find the specific spreadsheet that you need wastes time. We know how to input the same data into multiple workbooks simultaneously now, too make your life easier, you can create an index of all the spreadsheets within one specific workbook to help you find a spreadsheet quickly. A sort of table of content. In this lesson, I’ll show in four easy steps how to manually set up an index of spreadsheets within a workbook.

1. We’ll use as an example the following workbook that has three spreadsheets – Jan 07, Feb 07, and Mar 07. First, place your cursor to the left of the first spreadsheet and right click it, and select the first option “insert.” This will insert a brand new spreadsheet. Now click on the new spreadsheet, right click it and select the option “Rename” and call it “index.”

table of contents1

2. Now starting in cell A1, write down the names of the spreadsheets in the workbook – in this example, first enter “Jan 07” and then enter the remaining two names going down column A as shown in the screen shot below.

table of contents2

3. Next, click on cell A1 “Jan 07” to select it and go to Insert>Hyperlink. A box labeled “Insert Hyperlink” will appear, select the second option on the right hand side called “Place in this Document” and enter A1 under “Type Cell Reference” or click “Jan 07” under “Or Select a place in this document.” Repeat this step for all the names you want to hyperlink, in this example, it would be Feb and Mar 07.

table of contents3

table of contents4

4. Finally, when you open the “index” spreadsheet you will see the names of all the spreadsheets highlighted in blue. You can click on any of them, and you will be linked directly to the spreadsheet that you select without having to scroll down to find it. Great! You just created a shortcut to finding your spreadsheets within a workbook.

table of contents5

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)