

Sometimes Excel workbooks become quite large: The more worksheets there are, the more difficult it is to keep the overview. A table of contents might help. In this article we’ll explore 4 ways of creating tables of contents in an Excel workbook.
Let’s say we want to create a new worksheet with a list of all other worksheets. Furthermore, each list entry should have a link to the corresponding worksheet so that you can easily click on it and will be taken to this worksheet at once. Basically, there are four methods for creating such table of contents: Do it manually, apply a complex formula, use a VBA macro or an Excel add-in.
Contents
Insert a table a contents manually
The first method is the most obvious one: Type (or copy and paste) each sheet name and add links to the cells. These are the necessary steps:
Define a named range first in order to insert a table of contents with formulas.
Thanks to Brian Canes, who posted this method as a comment: There is actually a way of using formulas and named ranges. Follow these steps:
Copy this formula down.
Copy this formula into any cell. As it refers to the cell A1, it’ll be the entry for the first worksheet in your workbook: =IF(ROW(A1)>SHEETS(),REPT(NOW(),),SUBSTITUTE(HYPERLINK(“#'”&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(TabNames,ROW(A1)),” “,CHAR(255)),”]”,REPT(” “,32)),32))&”‘!A1″,TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(TabNames,ROW(A1)),” “,CHAR(255)),”]”,REPT(” “,32)),32))),CHAR(255),” “))
Please note:
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
As the first method works but is quite troublesome – especially for large workbooks – we’ll take a look at a second method: A VBA macro. The macro is supposed to do the same steps: Walk through all sheets, create a list entry for each sheet and insert a hyperlink to each sheet.
Sub insertTableOfContents() Dim PROFEXWorksheet As Worksheet Dim tempWorksheetName, tempLink, nameOfTableOfContentsWorksheet As String Dim i As Integer 'Add a new worksheet Sheets.Add 'Rename the worksheet nameOfTableOfContentsWorksheet = "TableOfContents" ActiveSheet.Name = nameOfTableOfContentsWorksheet 'Add the headline Range("B3") = nameOfTableOfContentsWorksheet 'Initialize the counting variable i i = 0 'Go through all worksheets as described For Each PROFEXWorksheet In Worksheets 'Copy the current worksheet name tempWorksheetName = PROFEXWorksheet.Name 'Create the link from the current worksheet and link it to cell A1 tempLink = "'" & tempWorksheetName & "'!R1C1" 'Add the list entry Sheets(nameOfTableOfContentsWorksheet).Cells(i + 5, 2) = tempWorksheetName 'Select it for inserting the hyperlink Sheets(nameOfTableOfContentsWorksheet).Cells(i + 5, 2).Select 'Insert the hyperlink ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=tempLink 'Proceed with next entry and increase i therefore i = i + 1 Next End Sub
Insert a customized table of contents with the Excel add-in ‘Professor Excel Tools’
There are some Excel add-ins for creating a table of contents. We – of course – recommend our own add-in. It doesn’t only insert a table of contents but you can easily customize it:
Your last settings will be saved so that it’s very fast to update or create a table of contents. There is a 31-day free trial version with no sign-up available. You can just give it a try.
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 15
Brian Canes
Here is a way with an Excel4 defined name and a couple cell formulae.
Define TabNames=GET.WORKBOOK(1)&REPT(NOW(),)
In a cell enter the following formula
=”B O O K = “&TabNames&” “&” S H E E T S = “&SHEETS()
In the cell below that enter the following formula
=IF(ROW(A2)>SHEETS(),REPT(NOW(),),SUBSTITUTE(HYPERLINK(“#'”&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(TabNames,ROW(A2)),” “,CHAR(255)),”]”,REPT(” “,32)),32))&”‘!A1″),CHAR(255),” “))
and fill down until you get empties.
Regards
Brian
Henrik Schiffner
Dear Brian,
Thanks a lot, that’s a good way! I’ll add it.
Best regards,
Henrik
Hayden
Is there a way with Method 2 of then being able to hyperlink back from the sheets to the table of contents. That would be amazing.
Henrik Schiffner
Dear Hayden,
There are two simple approaches.
If you are sure that – let’s say – cell A1 is unused on each sheet, prepare a link to the table of contents in cell A1 on one of the sheets.
In order to achieve this, select the cell and press Ctrl + K on the keyboard. In the “Insert Hyperlink” window select “Place in This Document” and define the link to the table of contents. Confirm with OK.
Copy the cell and select all worksheets which should link to the table of contents. Paste the copied cell with Ctrl + v. Now, each worksheet should have a link to the table of contents.
Alternatively prepare a text box, also linking to the table of contents sheets (similar to the first method above). Copy it and paste it on each worksheet separately.
Does that help?
Best regards,
Henrik
nhetnai
For a faster way to create a table of contents, see Create a table of contents automatically.
Ralse
The VBA only seems to work just once and gives an error on ‘already existing name for sheet’ when used again.
Method 2 is more durable for end users unless method 3 refreshes itself when a new sheet is added.
i4laughlin.com
Repeat steps 3 and 4 for all the levels that you want to display in your table of contents.
Craig Seifferth
I tried Method 2: Use formulas for a table of contents multiple times and when pasting the second, longer formula that begins with
=IF(ROW(A1)>SHEETS(),REPT(NOW . . . .
all I get is this error in the cell: #NAME?
Any suggestions?
Henrik Schiffner
Hi Craig,
For me the method worked so I can’t really reproduce the error. But the #NAME error usually indicates – as the word says – a problem with a name. And because this method uses a named range, maybe there is a problem? Also have you checked if you’ve spelled all formula names correctly?
Best regards,
Henrik
kyle andrei mazon
i know this already…… but i learn some ideas in order to work fast…….!!!
Tomasz
Dear Henrik,
thank you very much for Method 3.
I have a question :
i was trying to make such TOC for sheets which are visible.
In excel file I have about 30 sheets, but depends from case I choose, for printing I have only 7-10 visible.
If I use method 3 then all sheets (about 30) are taken for TOC, but I would like to have only visible.
I’m not expert in VBA, I was trying to play with parameter .Visible but it doesn’t work.
If you can give some tip I will be very grateful.
Best Regards,
Tomasz.
Henrik Schiffner
Hi Tomasz,
Without trying it: You could add something like
If PROFEXWorksheet.Visible = True Then
after
For Each PROFEXWorksheet In Worksheets
and
End If
before
Next
Does that work?
Best regards,
Henrik
Henrik Schiffner
…and of course my add-in provides this functionality… 😉
Kgashane
Hi Henrik
Thanks a lot for this. How do I get it to list the contents of Cell A1 of each tab, instead of the tab name?
chris
any formula to show the page number?