How to Create Table of Contents in Excel with Hyperlinks (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, the Excel workbook becomes large because of numerous worksheets. Because of having several worksheets, it is tough to overview all of them. In that case, a table of contents can be a good solution. This article will show how to create a table of contents in Excel with hyperlinks. Basically, using hyperlinks in a table of contents can make it dynamic. I hope you find this article very interesting and gain lots of knowledge regarding the topic.


Download Practice Workbook

Download the practice workbook below.


5 Easy Methods to Create Table of Contents in Excel with Hyperlinks

To create a table of contents in Excel with hyperlinks, we have found five different methods through which you can solve the problem quite easily. In this article, we would like to utilize several Excel commands, functions, and more importantly, a VBA code to create a table of contents in Excel with hyperlinks.


1. Utilizing Context Menu

Our Initial method is really easy to use. Here, we will write down each worksheet’s name and provide a link there. Then, if we click on the link, it will take us to that certain worksheet. To understand the method, follow the steps.

Steps

  • First, write down all the names where you would like to add links.

  • Then, right-click on cell C5.
  • It will open the Context Menu.
  • From there, select the Link option.

Table of Contents in Excel with Hyperlinks

  • Another way you can get the Link option
  • First, go to the Insert tab on the ribbon.
  • Then, select Link from the Links group.

Table of Contents in Excel with Hyperlinks

  • As a result, it will open the Insert Hyperlink dialog box.
  • Then, select Place in This Document from the Link to section.
  • After that, set any cell reference.
  • Then, select the place in this document. As we want to provide a hyperlink to the United States worksheet, so, select the United States.
  • Finally, click on OK.

Table of Contents in Excel with Hyperlinks

  • It will create the hyperlink on cell C5.

  • Follow the same procedure and add a hyperlink in every cell in the Table of Contents.

Table of Contents in Excel with Hyperlinks

  • Then, if you click on any link, it will take us to that certain worksheet.

  • Here, we click on the Australia hyperlink, and it takes us to the Australia worksheet. See the screenshot.

Table of Contents in Excel with Hyperlinks

Read More: How to Create Table of Contents Automatically in Excel


2. Using HYPERLINK Function

In this method, we will utilize the HYPERLINK function. By using the HYPERLINK function, we create a link with a certain worksheet. After that, if you click on the link, it will take you to that certain worksheet. To understand this method, follow the steps carefully.

Steps

  • First, select cell C5.
  • Then, write down the following formula.
=HYPERLINK("#'United States'!A1","USA")

Table of Contents in Excel with Hyperlinks

  • After that, press Enter to apply the formula.

  • Then, select cell C6.
  • Write down the following formula.
=HYPERLINK("#'France '!A1","France")

  • Then, press Enter to apply the formula.

Table of Contents in Excel with Hyperlinks

  • Do the same procedure for other cells to create a hyperlink for every worksheet.
  • Finally, we will get the following result.

Table of Contents in Excel with Hyperlinks

  • Then, if you select any hyperlink, it will take it to that worksheet.

  • Here, we select the France hyperlink, it will take us to the France worksheet. See the screenshot.

Table of Contents in Excel with Hyperlinks


3. Embedding VBA Code

You can utilize VBA code to create a table of content with hyperlinks. Before doing anything, you need to add the Developer tab on the ribbon. After that, you use the VBA code and create a table of content in Excel with hyperlinks. Follow the steps.

Steps

  • First, go to the Developer tab on the ribbon.
  • Then, select Visual Basic from the Code group.

  • It will open up the Visual Basic window.
  • Then, go to the Insert tab there.
  • After that, select the Module option.

  • It will open a Module code window where you will write your VBA code.
  • Write down the following code.
Sub Table_of_Contents()
    Dim worksheet_value As Worksheet
    Dim worksheet_name, Link_sheet, output_sheet_name As String
    Dim j As Integer
    Sheets.Add
    output_sheet_name = "Table of Contents"
    ActiveSheet.Name = output_sheet_name
    Range("B3") = output_sheet_name
    j = 0
    For Each worksheet_value In Worksheets
            worksheet_name = worksheet_value.Name
            Link_sheet = "'" & worksheet_name & "'!R1C1"
            Sheets(output_sheet_name).Cells(j + 5, 2) = worksheet_name
            Sheets(output_sheet_name).Cells(j + 5, 2).Select
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Link_sheet
            j = j + 1
    Next
End Sub
  • Then, close the visual basic window.
  • After that, go to the Developer tab again.
  • Select the Macros option from the Code group.

  • As a result, the Macro dialog box will appear.
  • Then, select the Table_of_Contents option from the Macro name section.
  • Finally, click on Run.

Table of Contents in Excel with Hyperlinks

  • As a result, it will give us the following result. See the screenshot.

Table of Contents in Excel with Hyperlinks

  • Then, if you select any hyperlink, it will take it to that worksheet.

  • Here, we select the Finland hyperlink, it will take us to the Finland worksheet. See the screenshot.

Table of Contents in Excel with Hyperlinks

Read More: How to Make Table of Contents Using VBA in Excel (2 Examples)


4. Use of Power Query

Our third method is based on using the power query. First of all, we open the Excel file on the power query. Then, using the HYPERLINK function, we will get the hyperlinks for each worksheet. To understand this properly, follow the steps.

Steps

  • First, go to the Data tab on the ribbon.
  • Then, select Get Data drop-down option from the Get & Transform Data group.
  • After that, select From File option.
  • Then, select From Excel Workbook.

  • After that, select your preferred Excel file and click on Import.

  • Then, the Navigator dialog box will appear.
  • Select the Table of Contents.
  • Finally, click on Transform Data.

  • As a result, it will open up the Power Query window.

  • Then, right-click on the Name title and select Remove Other Columns.

  • As a result, all other columns are removed.
  • Then, click on the Close & Load drop-down option.
  • From there, select Close & Load To.

  • Then, the Import Data dialog box will appear.
  • Select the place where you would like to put your data and also set the cell.
  • Finally, click on OK.

  • It will give us the following result. See the screenshot.

Table of Contents in Excel with Hyperlinks

  • Then, create a new column where you would like to put your hyperlinks.

  • After that, select cell C5.
  • Write down the following formula.
=HYPERLINK("#'"&[@Name]&"'!A1","USA")

Table of Contents in Excel with Hyperlinks

  • Press Enter to apply the formula.

  • Do the same procedure for all cells. After that, you will get the following result.

  • If you click on any name, it will take you to that certain worksheet.
  • Here, we click on the USA hyperlink. It takes us to the United States worksheet.


5. Applying Combined Formula

In this method, we utilize the Name Manager where we will define the name. After that, we will use a combined formula through which we can create the table of contents with hyperlinks. Before we get into the steps, here are the functions we are going to use in this method:

To understand the method clearly, now follow the steps.

Steps

  • First, go to the Formula tab in the ribbon.
  • Then, select Define Name from the Defined Names group.

  • It will open the New Name dialog box.
  • Then, in the Name section, put TabNames as the name.
  • After that, write down the following formula in the Refers to section:
=GET.WORKBOOK(1)&REPT(NOW(),)
  • Finally, click on OK.

Table of Contents in Excel with Hyperlinks

  • Then, select cell C5.
  • Write down the following formula using the combined formula.
=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)," "))

This formula was taken from Professor-Excel which helped us to give the following output.

  • Then, press Enter to apply the formula.

Table of Contents in Excel with Hyperlinks

  • After that, drag the Fill Handle icon down the column.

Table of Contents in Excel with Hyperlinks

  • Then, if you click on the hyperlink option, it will take you to that worksheet.

  • Here, we click on the United States hyperlink, and it takes us to the United States worksheet. See the screenshot.

Table of Contents in Excel with Hyperlinks

Read More: How to Create Table of Contents Without VBA in Excel


Conclusion

We have shown five different methods to create a table of contents in Excel with hyperlinks. In this article, we will try to focus on how to include the hyperlinks in the table content so that you can easily access each worksheet. To do this, we used several Excel functions and VBA code. All of these are fairly easy to understand. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo