There are times where your spreadsheet will expand and have several worksheets within the same spreadsheet file. Normally, you would click the worksheet tabs (or next / previous arrow buttons) to navigate between worksheets. That’s okay, but…if you have more than 15 of them, it will be rather troublesome to sort through the list of tabs in your file just to find that particular worksheet!
A good way to navigate between multiple worksheets is to create a table of contents (TOC). However, unlike Word where you can automatically create a TOC using the built-in feature, Excel doesn’t have one. You have to build one yourself using hyperlinks. Let’s look at this example. There are 17 tabs in a spreadsheet:
You need to insert a new worksheet called “Table of Contents” and start building the hyperlinks that link them back to the tabs. Here’s how:
1. Right-click the Introduction tab and select Insert from the pop-up menu.
2. In the Insert dialog box, select Worksheet and click the OK button.
3. Double-click the new worksheet tab Sheet1, rename it as Table of Contents and hit the Enter button.
4. Give your worksheet a proper heading. For example, in cell B2, type “Table of Contents” and format it as bold with 16pt font size.
5. Let’s start building the first hyperlink to the Introduction tab. Right-click cell B4 and select Hyperlink from the pop-up menu.
6. A new Insert Hyperlink dialog box appears. Click the Place in This Document button, then select Introduction from the list.
7. Delete !A1 from Text to display text box. Then delete A1 from Type the cell reference text box. Click the OK button.
8. You have successfully created the first hyperlink. To test, click the Introduction hyperlink. It should jump straight to the Introduction worksheet.
Repeat the same steps, i.e. Step 5 to 7, to continue building the hyperlinks to the remaining worksheets.
9. Note that when you try to create the second hyperlink onwards, the apostrophe symbol ‘’ appears, surrounding the worksheet name. Delete those too (besides deleting the !A1 and A1).
10. Your final TOC will look like this:
Now that you have completed building the TOC in the first worksheet tab, you should also create a link back to the TOC for each of the worksheet. For example, in the Introduction worksheet, type “<<Back” in cell P2 and right-click the cell. Select Hyperlink from the pop-up menu.
11. A new Insert Hyperlink dialog box appears. Click the Place in This Document button, then select Table of Contents from the list. Delete A1 from Type the cell reference text box. Click the OK button.
12. You have successfully created a link back to the main TOC. To test, click the <<BACK hyperlink. It should jump straight to the Table of Contents worksheet.
But wait! If you have like a gazillion worksheets in the file, the above method may not seem ideal. You need to create a macro to help you automate the tasks. These two references below are excellent sources to teach you how to do so:
http://blogs.office.com/2011/01/13/add-a-table-of-contents-to-your-workbook-its-easy-i-promise/
http://sniptools.com/vault/auto-generate-a-table-of-contents-in-excel