Software

Create a table of contents in an Excel sheet using hyperlinks

Show users how to create a quick table of contents in Excel using the power of hyperlinks.

Hyperlinks have been around for a long time, but some Excel users just don't use them. One way your users can put them to good use is to create a simple table of contents sheet. Using the table of contents sheet is a good alternative to browsing sheet tabs-and is a good option for your users that aren't quite as familiar with sheet navigation as your power users. The good news is, this is something users can do for themselves.

To create a quick table of contents sheet, do the following:

  1. Insert a sheet and select a cell.
  2. Press [Ctrl]+k to display the Insert Hyperlink dialog.
  3. In the left pane, click Place In This Document.
  4. Find the sheet you want to link to under Cell Reference.
  5. Highlight the sheet. In the Text To Display control, Excel will combine the sheet name with a reference to cell A1.
  6. Highlight the cell reference and press Delete. That way, the link will display only the sheet name.
  7. Click OK.

Repeat the instructions above until you've added a hyperlink for each sheet. To improve readability, you can turn off the gridlines, headings, and the Formula bar. (Click the View tab for these options.) Also, remove the underline from the hyperlinks.

In just a few minutes, your users can create a reasonable table of contents page for easy sheet navigation. It doesn't have to contain all the sheets, just the ones they use the most. In addition, they can display any text they like. In step 6, above, I mentioned deleting the cell reference, but they don't have to display the sheet names. They can display any descriptive text they like.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks