How to Create Table of Contents for Tabs in Excel (6 Methods)

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 for tabs using VBA code and hyperlinks in Excel. I think you find this article informative and get some valuable insides.


Download Practice Workbook

Download the practice workbook below.


6 Suitable Methods to Create Table of Contents for Tabs in Excel

To create a table of contents for tabs, we have found six different ways through which you can easily do the work. In this article, we would like to utilize several Excel commands, functions, and more importantly, a VBA code to create a table of contents for tabs. Before doing anything, we need to create some spreadsheet tabs.

After that, we would like to utilize the Excel functions and VBA code to create the required table of contents for tabs.


1. Utilizing Context Menu

Our first method is really easy to use. Here, we will write down each spreadsheet tab name and add 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 spreadsheet tabs where you want to add links.

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

Excel Table of Contents for Tabs

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

  • 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 create a hyperlink of the United States worksheet, so, select the United States.
  • Finally, click on OK.

Excel Table of Contents for Tabs

  • It will create a hyperlink on cell B5.

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

Excel Table of Contents for Tabs

  • Then, if you click on any tabs, it will take us to that certain spreadsheet tab.

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

Excel Table of Contents for Tabs


2. Embedding VBA Code

You can utilize VBA code to create a table of contents for tabs. 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 for tabs. 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 option.
  • 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.
Sub table_of_contents_for_tab()
 Dim xAlerts As Boolean
    Dim I  As Long
    Dim sheet_index As Worksheet
    Dim sheet_v As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Table of contents").Delete
    On Error GoTo 0
    Set sheet_index = Sheets.Add(Sheets(1))
    sheet_index.Name = "Table of contents"
    I = 1
    Cells(1, 1).Value = "Tabs"
    For Each sheet_v In ThisWorkbook.Sheets
        If sheet_v.Name <> "Table of contents" Then
            I = I + 1
            sheet_index.Hyperlinks.Add Cells(I, 1), "", "'" & sheet_v.Name & "'!A1", , sheet_v.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
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.

Excel Table of Contents for Tabs

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

  • Here, we select the Finland tab, it will take us to the Finland spreadsheet tab. See the screenshot.

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


3. Using HYPERLINK Function

In this method, we will utilize the HYPERLINK function. By using the HYPERLINK function, we create a table of contents for tabs. After that, if you click on the tab, it will take you to that certain spreadsheet tab. To understand this method, follow the steps carefully.

Steps

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

Excel Table of Contents for Tabs

  • After that, press Enter to apply the formula.

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

Excel Table of Contents for Tabs

  • Then, press Enter to apply the formula.

  • Do the same procedure for other cells to create a table of contents for tabs.
  • Finally, we will get the following result.

Excel Table of Contents for Tabs

  • Then, if you select any tab, it will take it to that spreadsheet tab.

  • Here, we select the France tab, it will take us to the France spreadsheet tab. See the screenshot.

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


4. Use of Power Query

Our fourth 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.
  • 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 option.
  • 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.

Excel Table of Contents for Tabs

  • 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 want 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 want to put your tabs link.

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

Excel Table of Contents for Tabs

  • Press Enter to apply the formula.

Excel Table of Contents for Tabs

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

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


5. Utilizing Buttons

Another way we can create a table of contents for tabs is by using the Buttons. In this method, we create a button and then link it to the desired spreadsheet tab. After that, if we click on the button, it will take us to that tab. To understand the method, follow the steps properly.

Steps

  • First, go to the Developer tab on the ribbon.
  • Then, select the Insert drop-down option from the Controls group.

  • Select the Button(Form Control) from the Insert drop-down option.

Excel Table of Contents for Tabs

  • As a result, it will convert the mouse cursor into a plus (+) icon.
  • Drag the plus icon to give the shape of the button.

  • It will open the Assign Macro dialog box.
  • Then, select the New option.

  • It will open the Visual Basic window where you need to put your VBA for this button.
  • This code will create a link to a certain spreadsheet tab.
  • Write down the following code.
Sub Button1_Click()
ThisWorkbook.Sheets("United States").Activate
End Sub
Note: To create a link to a certain spreadsheet tab, you need to replace ‘United States’ with your preferred tab name. All other codes will remain unchanged.
  • Then, close the window.
  • After that, go to the Developer tab on the ribbon.
  • Then, select Macros from the Code group.

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

Excel Table of Contents for Tabs

  • It will take us to that certain tab.
  • Then, right-click on the button.
  • Select Edit Text from the Context Menu.

Excel Table of Contents for Tabs

  • Here, we set our button name as ‘USA’.
  • You can set your preferred name.
  • Now, click on the Name of the button.
  • It will take you to that certain tab.

Excel Table of Contents for Tabs

  • Here, we create a link with the spreadsheet tab named ‘United States’. So, it will take us to that tab.

  • Follow the same procedure to create other buttons for all required tabs.
  • Finally, we get the required table of contents for tabs. See the screenshot.

Excel Table of Contents for Tabs


6. 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 for tabs. 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 B5.
  • 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.

Excel Table of Contents for Tabs

  • Then, if you click on any tab, it will take you to that spreadsheet tab.

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

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


Conclusion

To create an Excel table of contents for tabs, we have shown six different methods through which you can create a better version of it. To create this, we utilize several Excel functions and VBA code. All of these methods are fairly effective and user-friendly. In this article, we have shown how to utilize buttons to create a table of contents. I think we covered all possible areas regarding the table of contents. If you have further 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

ExcelDemy
Logo