How To Make Table Of Contents In Excel Sheet

As an Amazon Associate I earn from qualifying purchases.

In this tutorial, we will teach you how you can make a table of contents in the Excel sheets. Without a proper index, it is very hard to manage and navigate a large Excel workbook. However, Excel does not have a built-in feature to create a table of content. But, with the help of the methods shown in this article you can create an index in your Excel worksheet with ease.

Make Table Of Contents In Excel Sheet

To create an index in your worksheet you can make use of hyperlinks. By using the hyperlink you can make it easy to navigate to a particular sheet/content in the workbook by just clicking on its link. And here’s how you can do that.

Method 1: Create A Table Of Contents In Excel Using Hyperlinks

1. Launch Excel on your computer and open the worksheet in which you want to create a table of content. Now, to create an index you will have to insert a new blank page to your already created worksheet. For inserting a new sheet press shift+F11, as you do that a new page will be added to your worksheet.

2. Now you can change the name of the sheet by clicking on it and then select Rename  option from the menu

Rename the sheet

3. Next, select any cell on the sheet and right click on it to open the options menu. Now, select the Hyperlink option.

Add hyperlink to the cells

4. As you do that a new tab will open, on this tab select the option Place in This Document from the left side panel. Now select the content you want to add to the index. The selected content will highlight and the name of the content will be shown on the Text to display box at the top of the tab.

select the content for hyperlinking

5. Now, remove the cell range( A1 in this case) from the content name on the Text to display box and click then on OK.

Create A Table Of Contents In Excel Using Hyperlinks

6. With this, the content with the hyperlink will be added to the table of content

hyperlinked content

7. Now you can follow steps 3, 4 and 5 to add remaining content to the table. Once created the hyperlink for all the content your index shall look something like this

table of content in excel

Now anyone can easily navigate to all the content on your every worksheet by just clicking on the links. But, this method is not feasible for a very large worksheet because here you have to add content to the table one by one which takes a lot of time. However, there is a second method that can help you create an index for a large worksheet within seconds.

ALSO READ: Convert Picture of Table Into Excel

Method 2: Create Index In Excel Worksheet Using VBA Code

VBA stands for Visual Basic for Application. Generally, coders use the VBA editor to create tools for Excel. However, you need not worry if you don’t know how to code because we are going to provide the code. You just have to copy and paste it the editor and you are good to go.

1. Open the Excel worksheet and press  Alt + F11 to enter the VBA editor.

Vba editor

2. Now click on Insert from the toolbar at the top and select Module

insert module to vba editor

3. Now copy and paste the following code in the module

Sub CreateTableofcontents()
'updateby Extendoffice 20180413
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Table of contents").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Table of contents"
    I = 1
    Cells(1, 1).Value = "Table of contents"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Table of contents" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), """'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

 

4. Run the code by pressing the F5 button or by clicking on the Run button.

run the code

5. Next, go to Files and select Close and Return to Microsoft Excel

exit vba editor

6. Now you will see the table of content is created with all the hyperlinked content all at once.

create index in excel using vba codes

Now you can simply click on any of the hyperlinked content to explore it.

Final Thoughts

So that’s how you create a table of content list on Excel for easy navigation of a large worksheet. If you work on Excel then you should definitely learn this skill because it will keep you ahead of all. Also, if you liked our tutorial then do share this article with others as well.

Amazon and the Amazon logo are trademarks of Amazon.com, Inc, or its affiliates.

LEAVE A REPLY

Please enter your comment!
Please enter your name here