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

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.

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.

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.

• Now, press New from the Name Manager window.

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

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

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

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

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

• 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”.

• 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.

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.

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

• Thus all the sheets will be selected.

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

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

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

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

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

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

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

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

• 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.

• 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.

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

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

• 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.

• 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.

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

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

• 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

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

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

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

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

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

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.