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.

3 comments
jgfelicio
jgfelicio

Paste this code in the first (leftmost and blank) tab. Right click on the tab and "show code" It wil create a dynamic index and wil put a "Back to Index" link in each tab cel H1. Excel 2003 and 2007. Private Sub Worksheet_Activate() Dim wSheet As Worksheet Dim M As Long M = 1 With Me .Columns(1).ClearContents .Cells(1, 1) = "INDEX" .Cells(1, 1).Name = "Index" End With For Each wSheet In Worksheets If wSheet.Name Me.Name Then M = M + 1 With wSheet .Range("H1").Name = "Start" & wSheet.Index .Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index" End With Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name End If Next wSheet End Sub

ian
ian

Having a TOC is great if you have a lot of tabs. It also allows for a better description than you may have on the tab. I also use a1 on every sheet as 'Contents' which hyperlinks me back to the TOC. Always have it open at the TOC sheet. The only thing I have not been able to do, is link to hidden tabs, such as my SourceData tab. Is this possible?

ssharkins
ssharkins

You're right, as is, hyperlinks won't work with a hidden sheet. You can probably usurp the link using the Worksheets_FollowHyperlink method.

Editor's Picks