Combine the navigational features of hyperlinks with the organizational capabilities of a table of contents, and you get a powerful new way to manage large Excel workbooks. You can create an Excel TOC whose entries point to your workbook's worksheets. People can then click any entry in the TOC and jump to its corresponding worksheet without scrolling horizontally to find the worksheet they're seeking.
Launch Excel and open a workbook that has at least two worksheets. Click the "New Sheet" button on the Worksheet bar at the bottom of the Excel window to create a new worksheet tab.
Right-click the new worksheet tab, select "Rename" and type "Table of Contents." Click the tab and drag it to the left so that it appears in front of the other tabs. This tab becomes your Table of Contents worksheet.
Click the Table of Contents worksheet's "A1" cell to select it and then click "Insert" from the ribbon. Click "Hyperlink" to open the Insert Hyperlink dialog box. Click "Place in This Document" and note that the dialog box displays a list of the workbook's worksheet names.
Click the worksheet that sits to the right of your new Table of Contents worksheet and type that worksheet's name in the "Text to Display" text box. Click "OK" and Excel adds a new hyperlink to cell A1 in the Table of Contents worksheet. This hyperlink's name matches the name you typed in the "Text to Display" text box.
Click cell "A2" and click "Insert" again. Click "Hyperlink," click "Place in This Document" and click the next worksheet that sits on the worksheet bar. Type its name in the "Text to Display" text box and click "OK." At this point, your Table of Contents worksheet contains two clickable entries that represent two of your workbook's worksheets.
- These steps describe creating a vertical table of contents by adding hyperlinks to rows in the same column. Make a horizontal TOC by placing your hyperlinks on the same row.
- You don’t have to use a worksheet’s name as an entry in your table of contents. You can type any meaningful name in the “Text to Display” text box. Instead of typing “Sales,” you could type “Sales Figures for July” if you thought that name might better indicate what’s in the worksheet. You could also change the name of your Table of Contents worksheet tab to a shorter name, such as TOC, to conserve space on the worksheet bar.
- If you ever want to change a hyperlink's display text, right-click the hyperlink, select "Edit Hyperlink" and type a new name in the "Text to Display" text box.
- BananaStock/BananaStock/Getty Images