Get FREE Advanced Excel Exercises with Solutions!

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. • Another way you can get the Link option
• First, go to the Insert tab on the ribbon. • 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. • It will create the hyperlink on cell C5.  • Then, if you click on any link, it will take you to that certain worksheet. • Here, we click on the Australia hyperlink, and it takes us to the Australia worksheet. See the screenshot. 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")` • 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. • Do the same procedure for other cells to create a hyperlink for every worksheet.
• Finally, we will get the following result. • 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. ### 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
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
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. • As a result, it will give us the following result. See the screenshot. • 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. ### 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.
• 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. • 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")` • 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. • 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. • After that, drag the Fill Handle icon down the column. • 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. ## 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 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. 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 Advanced Excel Exercises with Solutions PDF  