How to Create a Clickable Table of Contents for Every Worksheet in Excel

Thirty tabs at the bottom of your workbook and you can’t find anything.

In this video I’ll show you how to build a clickable table of contents that
lists every worksheet automatically — click a name, jump straight there. And
when you add a new sheet, it appears in the list by itself.

No VBA. It uses an old Excel 4.0 macro function that still works today, hidden
inside Name Manager.

Watch: link every worksheet in 7 minutes

Prefer reading? The same steps are written out below the video.

Step-by-step

When a workbook has dozens of sheets, scrolling through the tabs to find one is painful. A table-of-contents sheet that lists every worksheet with a clickable link lets you jump to any sheet in one click.

1. Create a named formula that lists the sheets

Add a sheet for the table of contents (the example calls it TOC). Go to Formulas > Name Manager > New, give the name something like WorksheetList, and in Refers to enter:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

GET.WORKBOOK(1) is an old Excel 4.0 macro function that returns each sheet’s full reference, such as [Book1.xlsx]Sales. FIND locates the closing bracket, and REPLACE strips everything up to it, leaving just the sheet names. Because it’s a macro function, save the file as an Excel Macro-Enabled Workbook (.xlsm).

2. Number the rows

On the TOC sheet, type 1 in A2 and 2 in A3, select both and drag the fill handle down past the number of sheets you have (the example goes to 35 for about 30 sheets).

3. Pull in the sheet names

In B2:

=INDEX(WorksheetList,A2)

This returns the first sheet’s name (the TOC sheet itself). Drag it down and every sheet name appears in order. Delete any extra rows below the last sheet.

4. Turn each name into a link

In C2:

=HYPERLINK(CONCAT("#'",B2,"'!A1"),"Go to sheet")

# tells the hyperlink to stay inside this workbook.
The single quotes around the sheet name make it work even when the name has spaces or special characters.
!A1 is where the cursor lands on that sheet. You can choose any cell.
Go to sheet is the clickable text.

Drag it down. Clicking any Go to sheet link now takes you straight to that worksheet, which is especially handy in workbooks with dozens or even hundreds of sheets.

Go further

My Excel Tips & Shortcuts: 100+ Powerful Hacks course collects a hundred time-saving techniques like this one.