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

Method 1 – Using Context Menu to Create Table of Contents for Tabs in Excel

Steps

Write down all the spreadsheet tabs where you want to add links.

Right-click on cell B5.
Open the Context Menu.
Select the Link option.

Another way you can get the Link option.
Go to the Insert tab on the ribbon.
Select Link from the Links group.

Open the Insert Hyperlink dialog box.
Select Place in This Document from the Link to section.
Set any cell reference.
Select the place in this document. As we want to create a hyperlink of the United States worksheet, so, select the United States.
Click on OK.

It will create a hyperlink on cell B5.

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

If you click on any tabs, it will take you to that certain spreadsheet tab.

Click the Australia tab, which takes us to the Australia spreadsheet tab. See the screenshot.

Method 2 – Applying Excel VBA Code to Create Table of Contents for Tabs

Steps

Go to the Developer tab on the ribbon.
Select Visual Basic from the Code group.

Open up the Visual Basic option.
Go to the Insert tab there.
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

Close the visual basic window.
Go to the Developer tab again.
Select the Macros option from the Code group.

The Macro dialog box will appear.
Select the Table_of_Contents option from the Macro name section.
Click on Run.

It will give you the following result. See the screenshot.

If you select any tab, it will take you to that worksheet.

Select the Finland tab, it will take us to the Finland spreadsheet tab. See the screenshot.

Method 3 – Using HYPERLINK Function

Steps

Select cell B5.
Write down the following formula.

=HYPERLINK("#'United States'!A1","USA")

Press Enter to apply the formula.

Select cell B6.
Write down the following formula.

=HYPERLINK("#'France '!A1","France")

Press Enter to apply the formula.

Do the same procedure for other cells to create a table of contents for tabs.
Get the following result.

Select any tab, it will take it to that spreadsheet tab.

Select the France tab, it will take us to the France spreadsheet tab. See the screenshot.

Method 4 – Use of Power Query to Create Table of Contents for Tabs in Excel

Steps

Go to the Data tab on the ribbon.
Select Get Data drop-down option from the Get & Transform Data.
Select From File option.
Select From Excel Workbook.

Select your preferred Excel file and click on Import.

The Navigator dialog box will appear.
Select the Table of Contents option.
Click on Transform Data.

It will open up the Power Query window.

Right-click on the Name title and select Remove Other Columns.

All other columns are removed.
Click on the Close & Load drop-down option.
Select Close & Load To.

The Import Data dialog box will appear.
Select the place where you want to put your data and also set the cell.
Click on OK.

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

Create a new column where you want to put your tabs link.

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 tab, it will take you to that particular worksheet.
We click on the USA tab. It takes us to the United States spreadsheet tab.

Method 5 – Use of Buttons to Create Table of Contents for Tabs

Steps

Go to the Developer tab on the ribbon.
Select the Insert drop-down option from the Controls group.

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

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.
Select the New option.

It will open the Visual Basic window where you must 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 must replace ‘United States’ with your preferred tab name. All other codes will remain unchanged.

Close the window.
Go to the Developer tab on the ribbon.
Select Macros from the Code group.

The Macro dialog box will appear.
Select Button1_Click from the Macro name section.
Click on Run.

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

Set our button name as ‘USA’.
You can set your preferred name.
Click on the Name of the button.
It will take you to that certain tab.

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.
Get the required table of contents for tabs. See the screenshot.

Method 6 – Applying Combined Formula to Create Table of Contents

We utilize the Name Manager where we will define the name. After that, we will use a combined formula to 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

Go to the Formula tab in the ribbon.
Select Define Name from the Defined Names group.

It will open the New Name dialog box.
In the Name section, put TabNames as the name.
Write down the following formula in the Refers to section.

=GET.WORKBOOK(1)&REPT(NOW(),)

Click on OK.

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.