How to create a table of contents in Excel

Sometimes the number of sheets in an Excel workbook grows so large that switching between sheets becomes inconvenient. To speed up the work, create a table of contents: a list of all tabs with hyperlinks. This article offers 3 ways: a VBA macro and 2 free built-in tools of the !SEMTools add-in.

VBA macro: list of sheets on a separate tab

The classic way is a macro that collects all sheet names and creates hyperlinks to them. After you run it, a new sheet with a clickable table of contents appears at the beginning of the workbook.

Sub CreateTableOfContents()
    Dim ws As Worksheet, toc As Worksheet, r As Long
    Set toc = Worksheets.Add(Before:=Worksheets(1))
    toc.Name = "All sheets"
    For Each ws In Worksheets
        If ws.Name <> toc.Name Then
            r = r + 1
            toc.Hyperlinks.Add Anchor:=toc.Cells(r, 1), Address:="", _
                SubAddress:="'" & Replace(ws.Name, "'", "''") & "'!A1", _
                TextToDisplay:=ws.Name
        End If
    Next ws
End Sub

The new “All sheets” tab lists the names of all other sheets as active links: click a name to go straight to the sheet. The Replace part handles sheet names with an apostrophe, like “Q1 Kate’s report”, which would otherwise break the link. If a sheet named “All sheets” already exists, delete or rename it before running the macro again.

To use the code, create a VBA module in the workbook (Alt+F11 → Insert → Module) and paste it there. Save the file as .xlsm, otherwise the macro is lost.

You can also put the macro in your Personal Macro Workbook to use it in any file.

Without VBA: a formula with HYPERLINK

If you already have the list of sheet names in column A, a formula turns each into a link, no macro needed:

=HYPERLINK("#'"&A1&"'!A1",A1)

The “#” means a place inside the current workbook. The HYPERLINK function article has more examples.

Table of contents on the first sheet in one click

For those who don’t want to deal with VBA: if you have !SEMTools installed, forget about code. Click the button in the Find → Sheets menu on the add-in tab, and the table of contents is created automatically, with numbered hyperlinks to all sheets.

The procedure is available even in the free version of !SEMTools.

Table of contents in an interactive window

Besides the classic list, !SEMTools has an advanced tool: the list of all sheets opens in a separate window, without changing the workbook. It also lets you:

• quickly find the sheet you need with the search box;
• move through the list with the ↑ and ↓ arrows;
• go to the selected sheet with Enter.

This way is even more convenient when you have dozens or hundreds of tabs. The tool is also free and available to everyone who installs the add-in.

Summary

I hope you found what you were looking for on this page. A VBA macro creates a table of contents in any Excel version, and HYPERLINK does it with a formula if you have the list of names. With !SEMTools, it’s one click, and the interactive sheet list lets you jump between sheets without changing the workbook at all.