How to Create Dynamic Table of Contents in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

A dynamic table of contents in Excel is a very powerful thing that allows users to create an interactive and automated index for their workbooks. By linking sheet names and hyperlinks, it enables easy navigation within large Excel files. This feature provides a convenient way to organize and access data, improving efficiency and user experience in Excel. Today in this article, I am sharing with you how to create a dynamic table of contents in Excel. Stay tuned!

In the following, you will find an overview of how to create a dynamic table of contents in Excel.

Overview of a dynamic table of contents in Excel


Download Practice Workbook

You can download our practice workbook from here for free!


How to Create Dynamic Table of Contents in Excel: 3 Easy Methods

In the following, I have shared 3 simple and easy steps to create a dynamic table of contents in Excel. Suppose we have a dataset of a shop’s month-wise sales in multiple worksheets. Now we will create a dynamic table of contents for these multiple worksheets.

Sample dataset of a dynamic table of contents in Excel


1. Combining GET.WORKBOOK, HYPERLINK, INDEX, REPLACE, FIND, T, and NOW Functions to Create Dynamic Table of Contents

With the combination of GET.WORKBOOK, HYPERLINK, INDEX, REPLACE, FIND, T, and NOW functions you can create a dynamic table of contents. The main advantages include automatic updates as sheets are modified and clickable links for easy access.

Steps:

  • First, we will create a new worksheet where we want to create our table of contents.
  • Second, a name manager will be created which can be called inside the formula. To do so, click Name Manager from the Formulas tab.

Creating Name Manager from the Formulas tab

  • Now, press New from the Name Manager window.

Clicking New option to create a new name manager

  • Then, in the Name section put your own choice of name and in the Refers to section write the below formula.

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

Applying formula to Refer to section and naming it

Formula Breakdown:

  • GET.WORKBOOK(1)

This is an Excel function that retrieves the name of the currently open workbook. Argument 1 specifies the workbook name type.

  • FIND(“]”, GET.WORKBOOK(1))

This uses the FIND function to locate the position of the closing square bracket (“]”) within the workbook name obtained from GET.WORKBOOK(1). It searches for the closing bracket in the workbook name string.

  • REPLACE(GET.WORKBOOK(1), 1, FIND(“]”, GET.WORKBOOK(1)), “”)

This uses the REPLACE function to replace a portion of the workbook name string obtained from GET.WORKBOOK(1). It replaces the portion starting from the first character and extending to the position of the closing bracket (found using FIND). The replacement is an empty string (“”).

  • After that, choose a cell (B5) and write the below formula down.

=IFERROR(HYPERLINK("#'"&INDEX(Dynamic_Table_of_Contents,ROW(A1))&"'!A1",INDEX(Dynamic_Table_of_Contents,ROW(A1))),"")

Formula to create a dynamic table of contents in the worksheet

Formula Breakdown:

  • ROW(A1):

The ROW function will return the row number of Cell A1.

  • INDEX(Dynamic_Table_of_Contents,ROW(A1))

This part is using the INDEX function to retrieve the value from the Dynamic_Table_of_Contents array based on the row number of cell A1.

  • INDEX(Dynamic_Table_of_Contents, ROW(A1))&”‘!A1″ :

Here, we append the retrieved value from the previous step with the text “‘!A1”. This is used to construct a reference to cell (A1) in a specific sheet.

  • “#'”&INDEX(Dynamic_Table_of_Contents,ROW(A1))&”‘!A1” :

In this part, we concatenate the “#” symbol then retrieves value from the first step (sheet name), and the “‘!A1” reference to form a complete hyperlink destination.

  • HYPERLINK(“#'”&INDEX(Dynamic_Table_of_Contents,ROW(A1))&”‘!A1″,INDEX(Dynamic_Table_of_Contents,ROW(A1))),””) :

The HYPERLINK function is used here to create a clickable hyperlink. The first argument is the constructed hyperlink destination from the previous step, and the second argument is the display text for the hyperlink.

  • IFERROR(HYPERLINK(“#'”&INDEX(Dynamic_Table_of_Contents,ROW(A1))&”‘!A1”,INDEX(Dynamic_Table_of_Contents,ROW(A1))), “”) :

Finally, the IFERROR function is used to handle any errors that may occur. It attempts to create a hyperlink using the HYPERLINK function. If an error occurs, such as if the sheet name is not found in the “Dynamic_Table_of_Contents” array, it returns an empty string (“”).

  • Finally, hit ENTER and you will see the dynamic table of contents is created.

Final output by creating dynamic table of contents

  • In order to check, simply click any of the contents from the sheet.

Clicking the linked table of contents to check whether it’s working or not

  • Thus, you will jump to the pressed worksheet. It’s that simple.

Visiting the linked worksheet by using the table of content sheet

  • You can also change the worksheet name and it will automatically update in the table of contents wall. Here I have changed the worksheet name from “February” to “Feb”.

Changing worksheet name to check whether the dynamic table updating automatically or not

  • Coming back to the table of contents sheet you will see the worksheet name has changed confirming our successful creation of a dynamic table of contents.

Final result with changed worksheet name in the table of contents sheet


2. Using Formula to Create Semi-Dynamic Table of Contents

If you want you can also create a semi-dynamic table of contents combining the CELL, TEXTAFTER, VSTACK, and HYPERLINK functions. It simplifies maintenance, improves worksheet organization, and automatically updates as sheets are added, deleted, or rearranged.

Steps:

  • To start with, we need to select all the sheets to put the sheet name in a specific location of all the worksheets.
  • First, holding the CTRL key we will click the left arrow from the left.

Holding CTRL key to select all the sheets from the workbook

  • Next, holding the SHIFT key, click the first worksheet.

Pressing SHIFT key to choose all the worksheets

  • Thus all the sheets will be selected.

Selected all worksheets using the keyboard shortcut keys

  • Thereafter, select a cell (C15) and apply the below formula down and hit OK.

=TEXTAFTER(CELL("filename",C15),"]")

Formula to include the sheet name in all the worksheets

  • Following these steps will lead to getting sheet names in cell (C15) of all the worksheets.

Result with including sheet names in all the worksheets

  • Now, we will use the VSTACK function to arrange all the sheet names in one worksheet.

=VSTACK(January:Semi_Dynamic!C15)

Applying VSTACK formula to insert table of contents

  • Hitting ENTER will provide us all the sheet names just like the below image.

Created table of contents which includes all the sheet names

  • It’s time to link all the sheets. For that, choose a cell (B5) and put the below formula down with the HYPERLINK function.

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

Formula to link table of content cells

  • In conclusion, we have successfully created our dynamic table of contents in Excel.

Final output with a dynamic table of contents linking all the cells

  • Let’s check whether it’s working properly or not. In order to do that, simply press any linked content from the table.

Clicking a linked cell to check if it’s working properly or not

  • Immediately you will jump to the worksheet which you pressed.

Visiting the clicked worksheet from the table of contents sheet

  • On some points for working with more efficiency, you can add the main table of contents sheet to all the worksheets. So that, you can get back to the main content sheet with a single click from any sheet.
  • Simply, choose a cell (C17) and click Insert Link from the Insert tab.

Inserting a linked cell for the main table of content sheet

  • Then, choose the table of contents sheet where all the sheet names are placed. Put your own choice of name in the Text to display section and hit OK.

Linking a worksheet and naming it

  • This way, we have added the link for the table of contents sheet. Let’s check by clicking the newly created link.

Result with linked table of the content sheet in one single sheet

  • Within a glimpse of an eye, we have moved to the table of contents sheet.

Visiting the linked worksheet by clicking the newly created linked cell

  • Now, it’s time to copy this linked cell to all the worksheets so that we can jump to the main table of contents from any sheet. Simply, press CTRL+C to copy.

Copying the linked table of contents sheet for all the worksheets

  • Just like the previous steps, select all sheets from the workbook, choose cells (B17:C17), and hit CTRL+V to paste. Make sure the chosen cells are blank for all the sheets. Otherwise, it won’t work.

Selecting all the sheets and pasting the linked cell

  • Finally, we have our linked cells copied to each worksheet. Simple isn’t it?

Result with creating main table of content cell for all the worksheets


3. Embedding VBA Macro to Insert Dynamic Table of Contents

The main purpose of inserting a dynamic table of contents is to provide a convenient way to navigate through a document’s structure and quickly access specific sections. By applying VBA code you can automate your table of contents. After applying the code, you don’t need to refresh to update the content. Today in this part, I am sharing a VBA code which will automatically update whenever you make changes to your workbook.

Steps:

  • First, follow this link to open the VBA window and insert a module.
  • Inside the module, place the below code and click the Save icon.
Sub Dynamic_table_of_contents()
xAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set Sheet_TOC = Sheets("VBA")
On Error GoTo 0
If Sheet_TOC Is Nothing Then
Exit Sub
End If
I = 4
For Each WS In ThisWorkbook.Sheets
If WS.Name <> "VBA" Then
I = I + 1
Sheet_TOC.Hyperlinks.Add Sheet_TOC.Cells(I, 2), "", "'" & WS.Name & "'!A1", , WS.Name
End If
Next
Application.DisplayAlerts = xAlerts
End Sub

VBA code to create a dynamic table of contents

  • After that, we will insert the following code with a private sub inside the sheet. Don’t forget to change to Worksheet and Activate to run your VBA code properly.
Private Sub Worksheet_Activate()
Dynamic_table_of_contents
End Sub

VBA code with private sub inside the chosen sheet

  • After running the code, you will get to see we have successfully created our table of contents in Excel.

Final output with creating a dynamic table of contents using VBA

  • Now, let’s check if it’s working properly or not by changing the name of the worksheet to Jan from January.

Changing the worksheet name to check the dynamic table of contents sheet automatically updating or not

  • Going back to the main table of contents you will see the table of content has changed automatically.

Updated dynamic table of content with changed sheet name

  • Let’s add another worksheet by clicking the plus (+) icon following the below image.

Adding a new worksheet inside the workbook

  • As the new sheet is created, the table of contents has been updated by adding a new sheet in the list.

Automatic adding of new worksheet name inside the dynamic table of content sheet


Things to Remember

  • While creating multiple sheets ensure that the sheet names used in the table of contents match the actual sheet names in your workbook. Any discrepancies can lead to broken links and errors.
  • If you plan to dynamically update the table of contents as you add or remove sheets, be mindful of maintaining a consistent sheet order and structure.

Frequently Asked Questions

  • Can I automatically update the Table of Contents when I add or remove sheets in my workbook?

Yes, you can set up the table of contents to automatically update by using dynamic formulas, such as INDEX and INDIRECT.

  • Is it possible to customize the appearance and formatting of the table of contents?

You can customize the appearance and formatting by applying formatting styles, changing font sizes, adding colors, and modifying cell borders.

  • Can I create a hierarchical or nested table of contents with multiple levels?

Yes, you can create a hierarchical or nested table of contents by using indentation or multiple columns.


Conclusion

In conclusion, creating a dynamic table of contents in Excel can greatly enhance the organization and navigation of a workbook. By utilizing formulas, named ranges, and hyperlinks, users can automate the updating process and ensure that the table of contents reflects any changes made within the workbook. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo