How to Create Table of Contents in Excel (6 Suitable Ways)

Sometimes you may have lots of data in different worksheets. In that case, you can use a Table of Contents to find out any worksheet easily. So, you have to know how to create a Table of Contents in Excel. In this article, I will explain how to create a Table of Contents in Excel.

Here, I’m going to explain 6 methods of how to create a Table of Contents in Excel. For your better understanding, I will use a sample dataset. Which has 2 Columns. Those are Product and Sales. Also, which contains 5 worksheets. Those are Dataset, Sales of January, Sales of February, Sales of March, and Sales of April. The sample dataset is given below.

1. Using HYPERLINK Function to Create a Table of Contents in Excel

You can use the HYPERLINK function to create a Table of Contents in Excel. The steps are given below.

Steps:

• Firstly, you have to select a different cell B5 where you want to see the contents. Moreover, the best option is to create the Table of Contents in a new worksheet.
• Secondly, you should use the corresponding formula in the B5

• Subsequently, you must press ENTER to get the result.

• Now, you have to repeat the procedure again for the sheet named Sales of February.
• In this case, you should use the corresponding formula in the B6

In the same way, you have to write the formula individually in the cells. Finally, you will see the following result.

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

2. Applying Excel Power Query for Creating Table of Contents

You can use Power Query to create a Table of Contents in Excel. The steps are given below.

Steps:

• Firstly, you have to go to the worksheet where you want to create a Table of Contents.
• Secondly, from the Data tab >> you have to choose the Get Data Also, you can use the Excel keyboard shortcuts ALT+A to go to the Data tab.
• Thirdly, from that ribbon >> you must select From File feature >> after that, select the From Excel Workbook

At this time a window named Import Data will appear.

• From that window, you need to choose your Excel file. Here, I have chosen Create Table of Contents.
• After that, you must press Import.

At this time, a dialog box named Navigator will appear.

• Now, you have to select your Excel file from that dialog box.
• Then, Click on the Transform Data. Here, if you don’t select the Excel file, you will not be able to click on the Transform Data.

Subsequently, you will see the following Power Query Editor window.

• Firstly, you should Right-Click on the Name column.
• Secondly, from the Context Menu Bar >> you need to select Remove Other Columns.

• Now, from the Close & Load feature >> you have to select Close & Load To…

At this time, a dialog box named Import Data will appear.

• Firstly, you need to select the Existing worksheet. Here, you can choose a New worksheet also. In that case, your Table of Contents will be in a different and new worksheet.
• Secondly, choose the Cell location for the Table of Contents. Here, I have chosen B4 as my Cell location.
• Finally, you must press OK.

Finally, you will see the following Contents.

Now, to create the link follow the procedure given below.

• Firstly, you have to select a different cell C5 where you want to see the contents.
• Secondly, you should use the corresponding formula in the C5

Here, in this formula, you have to select the name of the worksheet from cell B5 (Dataset).

• Subsequently, you must press ENTER to get the result.

• Similarly, create the link for other worksheets.

Finally, you will see the following Table of Contents.

3. Using Mouse Cursor to Create Table of Contents in Excel

You can use the Mouse Cursor to create a Table of Contents in Excel. The steps are given below.

Steps:

• Firstly, you have to write down the Contents

• Now, go to the worksheet named Dataset and Right-Click on any Cell Border. Don’t release the Mouse Cursor. For this method, you have to keep holding the Cursor.
• Then, hold the ALT Also, don’t release the ALT key. For this method, you have to keep holding both the Cursor and the ALT key altogether.
• At this moment, move the Cursor to the worksheet where you keep the Contents.

• Now, bring the Cursor to the corresponding content named Dataset.
• Firstly, release the ALT key and then release the Mouse Cursor.
• Secondly, from the Context Menu Bar >> select the Create Hyperlink Here

Below, for your better understanding, I have attached a GIF.

In the same way, you have to create links for other individuals. Finally, you will see the following result.

4. Applying Keyboard Shortcuts

You can employ the Keyboard Shortcuts to create a Table of Contents in Excel. The steps are given below.

Steps:

• Firstly, you have to select a different cell B5 where you want to see the contents.
• Secondly, you need to press the CTRL+K

At this time, a dialog box named Insert Hyperlink will appear.

• Now, from the Place in This Document command >> select Dataset under the Cell Reference.
• Then, write down what you want to see as content in the Text to display Here, I have written “ Dataset “.
• Finally, press OK.

After that, you will see the following Content with a link.

• Similarly, create the link for other worksheets.

Finally, you will see the following Table of Contents.

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

5. Use of Context Menu Bar to Create Table of Contents in Excel

You can employ the Context Menu Bar to create a Table of Contents in Excel. The steps are given below.

Steps:

• Firstly, you have to select a different cell B5 where you want to see the contents.
• Secondly, from the Context Menu Bar >> you have to choose the Link feature >> then select the Insert Link

At this time, a dialog box named Insert Hyperlink will appear.

• Now, from the Place in This Document command >> select Dataset under the Cell Reference.
• Then, write down what you want to see as content in the Text to display Here, I have written “ Dataset “.
• Finally, press OK.

After that, you will see the following Content with a link.

• Similarly, create the link for other worksheets.

Finally, you will see the following Table of Contents.

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

6. Using VBA Code to Create Table of Contents

You can employ a VBA code to create a Table of Contents in Excel. The steps are given below.

Steps:

• Firstly, you have to choose the Developer tab >> then select Visual Basic.

• Now, from the Insert tab >> select Module.

• Write down the following Code in the Module.

Sub Table_of_contents()
    Dim Alert_data As Boolean
    Dim numb  As Long
    Dim Sheet_Index As Worksheet
    Dim Sheet As Variant
    Alert_data = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Table of contents").Delete
    On Error GoTo 0
    Set Sheet_Index = Worksheets("VBA")
    numb = 1
    Cells(1, 1).Value = "Table of contents"
    For Each Sheet In ThisWorkbook.Sheets
        If Sheet.Name <> "Table of contents" Then
            numb = numb + 1
            Sheet_Index.Hyperlinks.Add Cells(numb, 1), "", "'" & Sheet.Name & "'!A1", , Sheet.Name
        End If
    Next
    Application.DisplayAlerts = Alert_data
End Sub

• Now, Save the code then go back to Excel File.
• After that, go to the Developer tab >> Macros 

• Then, select the Macro name (Table_of_Contents) and click on Run.

Finally, you will see the following Table of Contents which has all the worksheets.

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

💬 Things to Remember

• When you want to make a Table of Contents for all the worksheets then you can follow either method 2 (Power Query) or method 6 (VBA).

• In case, when you need to create a Table of Contents for certain worksheets then you can follow the other methods.
• In my opinion, method 3 ( Mouse Cursor) is the simplest and fastest method to create a Table of Contents.

Download Practice Workbook

You can download the practice workbook from here:

Conclusion

I hope you found this article helpful. Here, I have explained 6 methods to create a Table of Contents in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Create Table of Contents Without VBA in Excel
• How to Create Table of Contents in Excel with Page Numbers