How to Create a Table of Contents in Excel

The "30-tab workbook navigation" problem

Big workbooks turn into navigation puzzles. Twenty tabs and you're scrolling the tab bar with the little arrow controls, squinting to find "Q3 Forecast Region 4" before your meeting starts. A clickable table of contents solves this.

TL;DR — Key takeaways

• A manual TOC sheet with =HYPERLINK() formulas works but requires entering each sheet name.
• =HYPERLINK("#" & SheetName & "!A1", SheetName) as a formula creates jump links.
• A VBA macro generates a clickable index sheet at position 1 with hyperlinks to every other sheet.

Method 1: Manual HYPERLINK formulas

For each sheet you want to link:

=HYPERLINK("#'Sheet Name'!A1", "Sheet Name")

The # prefix means internal link. The single quotes are required when the sheet name contains spaces.

The catch: typing each sheet name is tedious. And as new sheets are added, you have to manually update the index.

Method 2: List sheet names manually, link manually

Type each sheet name in column A of a TOC tab. Then for each, right-click > Hyperlink > Place in This Document > pick the sheet.

The catch: painful to maintain.

Method 3: The free VBA macro

Download List All Sheet Names. Free .xlsm with one macro.

1. Open the workbook.
2. Alt + F8, pick the macro, click Run.
3. The macro creates a new sheet called Sheet Index at position 1 with a numbered list of every other sheet, each as a clickable hyperlink. Plus a third column showing whether each sheet is hidden.

One click. Refreshable by re-running.

What's in the generated index

#	Sheet Name	Visible
1	Q1 Summary	yes
2	Q1 Region 1	yes
3	Q1 Region 2	yes
4	Q1 Region 3	yes
5	Calc Engine	hidden
...	...	...

Each sheet name is a hyperlink that jumps to A1 of that sheet. The visibility column shows hidden tabs at a glance.

A common scenario: workbook handover

You're handing off a 47-tab budget workbook to a colleague during paternity leave.

1. Run List All Sheet Names on the workbook.
2. Sheet Index appears at position 1 with hyperlinks.
3. Save and send.

Your colleague opens the file, sees the index, clicks "Summary - All Stores", lands on the right tab. No "where's the consolidated view" emails.

Frequently asked questions

Does it list hidden sheets?

Yes. Hidden sheets show in the index with hidden in the visibility column. The hyperlink works (jumping to A1 of the hidden sheet) but doesn't unhide it. To unhide, see Unhide All Sheets.

Will it overwrite an existing index?

No. If Sheet Index already exists, the macro creates Sheet Index 2 instead. Delete the older one when done.

Can I refresh the index after adding new tabs?

Yes, by re-running the macro. The new run creates Sheet Index 2. Delete the old one.

What about Very Hidden sheets?

They're listed with hidden in the visibility column too. The hyperlink works the same way as for regular hidden sheets.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the new sheet creation.

What to do next

For workbooks where tabs are in random order, Sort Sheets Alphabetically makes the index more useful. For consistent navigation, Freeze Top Row on All Sheets keeps headers visible everywhere.