Create a Table of Contents in Excel

In an Excel file with lots of worksheets, how do you help users navigate through the workbook? Here are a few of the methods I've used. I'm sure you've found your own creative ways to deal with the problem, and I'd be interested in hearing about them.

Create a Workbook Menu

You can use programming to create a special menu with a list of sheets. Add the menu to the menu bar when the workbook opens, and remove the menu when the workbook closes. John Walkenbach has a nice example of an Excel menu maker.

Add a Drop Down List of Sheets

Another option is to install an add-in that lists all the sheets in the active workbook, in a toolbar dropdown list. Dave Peterson created a Navigation Toolbar for Excel 2003.

Ron de Bruin adapted the code to create a Navigation Command for Excel 2007.

NavToolbarSelect

Create List of Hyperlinks

A non-programming option is to create a list of sheets on a worksheet, then change each sheet name into a hyperlink.

HyperlinkClick

  1. Click on a cell that contains a sheet name.
  2. On Excel's Ribbon, click the Insert tab (In Excel 2003, click the Insert Menu.)
  3. Click Hyperlink, to open the Insert Hyperlink dialog box.
  4. In the Link To list, click on Place in This Document
  5. In the list of places in the document, click on a sheet name, then click OK.

It will take you a few minutes to set up all the hyperlinks in a large Excel workbook, but they'll make it easy to navigate through the sheets.

If you have a copy of the Power Utility Pak you can use it to create a table of contents, with either a list of hyperlinks or a set of navigation button.

PUPTOC

Based on sample code from Sam and Andrew in the reader comments below, I've posted an Excel Table of Contents workbook in which you can create a popup list or or a list with sheet hyperlinks.
______________________

21 comments to Create a Table of Contents in Excel

  • You left out my favorite, built-in method: Right-click on the transport controls to the left of the sheet tabs. Granted, that won't work as well if you have more than 19 worksheets, but it works great for my needs.

  • Tim, thanks for mentioning that technique. I like it too, and the workbook that I'm working with now has too many sheet to fit in that list.

  • Debra, the problem is that you do REAL work. I'm an academic. :-)

  • sam

    I have the below macro assigned to a shortcut key - Ctrl+q
    It shows a very interesting thing....
    The behaviour of SendKeys is different not only between 2003 and 2007 but also between XP and VISTA....

    Sub Sheet_Index()
    If Application.CommandBars("workbook tabs").Controls(16).Caption Like "More Sheets*" Then
    Application.ScreenUpdating = False
    If WINDOWS_VER > 5 Then
    If Application.Version = "12.0" Then
    Application.SendKeys "{end}~"
    Application.CommandBars("workbook tabs").ShowPopup
    Else
    Application.SendKeys "{end}~"
    Application.CommandBars("workbook tabs").Controls(16).Execute
    End If
    Else
    Application.SendKeys "{end}~"
    Application.CommandBars("workbook tabs").ShowPopup
    End If
    Application.ScreenUpdating = True
    Else
    Application.CommandBars("workbook tabs").ShowPopup
    End If

    Application.ScreenUpdating = True
    End Sub

    Public Type OSVERSIONINFO
    dwOSVersionInfoSize As Long
    dwMajorVersion As Long
    dwMinorVersion As Long
    dwBuildNumber As Long
    dwPlatformId As Long
    szCSDVersion As String * 128
    End Type

    Public Declare Function apiGetVersionEx Lib "kernel32" _
    Alias "GetVersionExA" _
    (lpVersionInformation As Any) _
    As Long

    Public Const VER_PLATFORM_WIN32_WINDOWS = 1
    Public Const VER_PLATFORM_WIN32_NT = 2
    Public WINDOWS_VER

    Sub WinVer()
    Dim osvi As OSVERSIONINFO
    Dim strOut As String

    osvi.dwOSVersionInfoSize = Len(osvi)
    If CBool(apiGetVersionEx(osvi)) Then
    With osvi
    ' Win 2000

    If .dwMajorVersion > 5 Then
    WINDOWS_VER = 6
    End If
    End With
    End If

    End Sub

  • Andrew

    Below is a macro that creates a Table of Contents sheet and puts a hyperlink to every sheet that isn't hidden and is not the current sheet.

    The links do not work for sheets that are graphs and I do not know how to either make them work or test that they are graphs and not include them. If anyone knows how, let me know.

    Sub CreateTableOfContents()

    Dim shtName As String
    Dim shtLink As String
    Dim rowNum As Integer
    Dim newSht As Worksheet

    Set newSht = Sheets.Add
    newSht.Name = "Table Of Contents"
    newSht.Select

    newSht.Range("A1").Value = "Table of Contents"

    rowNum = 2
    For i = 1 To Sheets.Count
    'Does not create a link if the Sheet isn't visible or the sheet is the current sheet
    If Sheets(i).Visible = True And Sheets(i).Name ActiveSheet.Name Then
    shtName = Sheets(i).Name
    shtLink = "'" & shtName & "'!A1"
    newSht.Cells(rowNum, 1).Select
    'inserts the hyperlink to the sheet and cell A1
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    shtLink, TextToDisplay:=shtName
    rowNum = rowNum + 1
    End If
    Next i

    End Sub

  • Thanks Sam and Andrew! WordPress changed all the quotes to curly quotes, which messes up the code, so I put your sample code in a file here: Create an Excel Table of Contents

  • Andrew

    I found some code to test if a sheet is a chart or not at http://www.vbaexpress.com/kb/getarticle.php?kb_id=389

    I modified my code to not link chart sheets as well:

    Option Explicit

    'Sample code posted by Andrew
    'http://blog.contextures.com/archives/2008/12/17/create-a-table-of-contents-in-excel/#comment-1267

    Sub CreateTableOfContents()
    'Below is a macro that creates a Table of Contents sheet and
    'puts a hyperlink to every sheet that isn’t hidden and is not the current sheet.
    'The links do not work for sheets that are graphs and I do not know
    'how to either make them work or test that they are graphs and not include them.

    Dim shtName As String
    Dim shtLink As String
    Dim rowNum As Integer
    Dim newSht As Worksheet
    Dim i As Long

    Set newSht = Sheets.Add
    newSht.Name = "Table Of Contents"
    newSht.Select

    newSht.Range("A1").Value = "Table of Contents"
    rowNum = 2

    For i = 1 To Sheets.Count
    'Does not create a link if the Sheet isn’t visible or the sheet is the current sheet
    If Sheets(i).Visible = True And Sheets(i).Name ActiveSheet.Name And IsSheet(Sheets(i).Name) Then
    shtName = Sheets(i).Name
    shtLink = "'" & shtName & "'!A1"
    newSht.Cells(rowNum, 1).Select
    'inserts the hyperlink to the sheet and cell A1
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    shtLink, TextToDisplay:=shtName
    rowNum = rowNum + 1
    End If
    Next i

    End Sub

    Public Function IsSheet(cName As String) As Boolean

    Dim tmpChart As Chart

    On Error Resume Next
    Set tmpChart = Charts(cName)
    On Error GoTo 0
    IsSheet = IIf(tmpChart Is Nothing, True, False)

    End Function

  • Thanks Andrew -- I updated the sample file.

  • Peder Schmedling

    Hi guys!
    Great code in the comments here..

    @Sam: thanks for the very neat trick, never thought of using the "workbook tabs" popup in this way, definitively going to use this

    @Andrew: if you loop through the Worksheets collection (instead of the Sheets collection) there is no need to test if the Sheet is a chart or not..

    Also you should refrain from using the ActiveSheet object. You've already declared a object variable (newSht) use this instead, this way your code will be explicit and more robust..

    As a small tip, I would also suggest to implement a check as to whether a worksheet named "Table of Contents" already exists, optionally replacing this worksheet. The way you've written the procedure now, it fails if you run it more than once..

  • [...] created a table of contents based on sheet names, in other workbooks, but hadn’t tried to index a sheet’s contents. In this case, all the [...]

  • Jeffrey weir

    There's a similar blog entry from April 2009 at http://www.dailydoseofexcel.com/archives/2009/04/22/previous-sheets-stack/ that has some ideas re this to. I posted an idea of my own there, which I've copied below:

    You could store often-used sheets as custom views, and then use the custom views dialog box to select them (which can be done without the use of the mouse if you like). An added bonus is that you can then save multiple views of the same spreadsheet in the case that you've filtered or hidden rows. For instance, in a workbook I use, I have a 'Customer information' customer view, and then have 'Customer info - customer x' view for any specific customers I might want to check on often.

    The downside is that this can be slow, as it not only takes you to the sheet concerned, but then does a whole heap of filtering/unfiltering...even if none is needed.

    Sometimes I also use named ranges for navigation. I start any functionality-based named ranges with ZZ_ so that they appear at the bottom of the list.

  • Thanks Jeffrey, interesting ideas.

  • John Kirkwood

    Thanks for all the excellent tips. If you also make the 'Web' toolbar visible, you can use the 'Back' button to jump back to the Table of Contents after hyperlinking off to another sheet (also works in Word with any internal TOC or crossreference links).

  • [...] a year ago, we talked about creating a table of contents sheet in Excel. In that post, one of the suggestions was to type a list of sheets on a worksheet, then change each [...]

  • Michele L Langlo

    I need a code that will update a TOC when a new workbook is created... any advice i can get would be great!

  • parag

    plz i m looking databse code
    i have made userform through VBA in Excel
    but i unable tract the code as i made in list as name ,location,date of joing ,and basic salry
    its lake simple data base,
    we need enter data through userfrom and will enter dirctly that excel sheet
    plz forward me some
    code for this

    hoping for posotive reply

  • Eden SW

    Can I create a content page within one worksheet? I have one workshet and it is very long.

  • [...] Debra Dalgleish on: Create a Table of Contents in Excel [...]

  • Matt

    I would like to know how to create a TOC where I can link to the Heading2 styles in another sheet in the same workbook.
    To explain this better, I have 500 subtopics that I have given the style Heading2 to. I need to be able navigate to each of the subtopics via the TOC.
    Alternatively, if a TOC is not possible, I need to know how to include the name of the Heading in the Header.
    Thanks for all assistance...

  • A simple solution is to deselect "Allow editing directly in cells". Then, when you double-click on any cell that contains a formula that refers to one or more other cells, you are taken to the first such reference. In my ToC, each such formula consists of just an equals sign and the address of a cell that contains a string that identifies the target area, but the formula may include text, and the reference may be to an empty cell. Deselecting that option is a good idea for another reason: when one is entering a formula, the option makes it impossible to click a neighboring cell.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>