How to Create Table of Contents Automatically in Excel: 4 Creative Methods

Method 1 – Utilizing Keyboard Shortcut to Create Table of Contents

Steps:

  • Type in the name of the worksheet. In this case, the name of our worksheet is 2019 Sales Data.
  • Press the CTRL + K key on your keyboard.

Using Keyboard Shortcut

This brings up the Insert Hyperlink wizard.

  • Click the Place in This Document option >> then choose the worksheet name (2019 Sales Data) >> click the OK button.

Automatically Create Table of Contents in Excel Using Keyboard Shortcut

This inserts a clickable link into the text string as shown in the image below.

Automatically Create Table of Contents in Excel Using Keyboard Shortcut

Repeat the process for the 2020 Sales Data worksheet.

Automatically Create Table of Contents in Excel Using Keyboard Shortcut

Follow the same procedure for the 2021 Sales Data worksheet.

Automatically Create Table of Contents in Excel Using Keyboard Shortcut

The results should look like the picture given below.

Automatically Create Table of Contents in Excel Using Keyboard Shortcut

You generated a table of contents for your worksheets.


Method 2 – Employing ALT Key to Generate Table of Contents

Steps:

  • Select the heading (here it is 2019 Sales Data).
  • Press and hold down the ALT Key and the right mouse button.

Note: This method will only work if your worksheet has been saved. Press the CTRL + S key to save your worksheet first.

Using ALT Key

  • Hover the cursor at the edge of the selected B1 cell and drag it into the worksheet with the table of contents. It is the ALT Key worksheet.

Automatically Create Table of Contents in Excel Using ALT Key

This brings you to the ALT Key worksheet.

  • Release the ALT key and drag the cursor to the desired location (B5 cell) while holding the right mouse button.
  • Let go of the right mouse button >> a list of options appears; choose the Create Hyperlink Here option.

Automatically Create Table of Contents in Excel Using ALT Key

The results should look like the following image below.

Automatically Create Table of Contents in Excel Using ALT Key

Repeat the same procedure for the other two worksheets as depicted below.

Automatically Create Table of Contents in Excel Using ALT Key


Method 3 – Using HYPERLINK Function to Create Table of Contents

Steps:

  • Go to the B5 cell and enter the expression below.

=HYPERLINK("#'2019 Sales Data'!A1","2019 Sales Data")

The “#’2019 Sales Data’!A1” is the link_location argument and refers to the location of the 2019 Sales Data worksheet. The “2019 Sales Data” is the optional friendly_name argument, which indicates the text string displayed as the link. The Pound (#) sign tells the function that the worksheet is in the same workbook.

Using HYPERLINK Function

  • Follow the same process for the 2020 Sales Data worksheet and insert the formula given below.

=HYPERLINK("#'2020 Sales Data'!A1","2020 Sales Data")

Automatically Create Table of Contents in Excel Using HYPERLINK Function

  • Type in the expression below to repeat the 2021 Sales Data worksheet procedure.

=HYPERLINK("#'2021 Sales Data'!A1","2021 Sales Data")

Automatically Create Table of Contents in Excel Using HYPERLINK Function

After completing all the steps, the results should look like the image below.

Automatically Create Table of Contents in Excel Using HYPERLINK Function


Method 4 – Applying VBA Code to Create Automatic Table of Contents

Step 1: Open Visual Basic Editor

  • Navigate to the Developer tab >> click the Visual Basic button.

Applying VBA Code

This opens the Visual Basic Editor in a new window.


Step 2: Insert VBA Code

  • Go to the Insert tab >> Select Module.

Automatically Create Table of Contents in Excel Applying VBA Code

Copy the code from here and paste it into the window below.

Sub Excel_Table_Of_Contents()

    Dim alerts As Boolean
    Dim y  As Long
    Dim Wrksht_Index As Worksheet
    Dim Wrksht As Variant
    
    alerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("TOC").Delete
    On Error GoTo 0
    Set Wrksht_Index = Sheets.Add(Sheets(1))
    Wrksht_Index.Name = "TOC"
    y = 1
    Cells(1, 1).Value = "TOC"
    For Each Wrksht In ThisWorkbook.Sheets
        If Wrksht.Name <> "TOC" Then
            y = y + 1
            Wrksht_Index.Hyperlinks.Add Cells(y, 1), "", "'" & Wrksht.Name & "'!A1", , Wrksht.Name
        End If
    Next
    Application.DisplayAlerts = alerts
End Sub

Automatically Create Table of Contents in Excel Applying VBA Code

Code Breakdown:

The VBA code is used to generate the table of contents. The code is divided into 3 steps.

  • The sub-routine is given a name; it is Excel_Table_Of_Contents().
  • Define the variables alerts, y, and Wrksht.
  • Assign Long, Boolean, and Variant data types.
  • Define Wrksht_Index as the variable for storing the Worksheet object.
  • Remove any previous Table of Contents sheet using the Delete method.
  • Insert a new sheet with the Add method in the first position and name it “Table of contents” using the Name statement.
  • We declare a counter (y = 1) and use the For Loop and the If statement to obtain the names of the worksheets.
  • Use the HYPERLINK function to generate clickable links embedded in the worksheet names.

Automatically Create Table of Contents in Excel Applying VBA Code


Step 3: Running VBA Code

  • Press the F5 key on your keyboard.

This opens the Macros dialog box.

  • Click the Run button.

Automatically Create Table of Contents in Excel Applying VBA Code

The results should look like the screenshot given below.

Automatically Create Table of Contents in Excel Applying VBA Code


Navigating to Worksheets Using Status Bar

Steps:

  • Move your cursor to the bottom-left corner of your worksheet, as shown in the picture below.
  • Hover the cursor, and you’ll see a Right click to See all sheets message.

Navigate to Worksheets Using Excel Status Bar

  • Right-click with the mouse.

The Activate dialog box pops up, which displays all the sheets.

  • Choose the sheet; we’ve chosen the 2021 Sales Data >> click OK.

Navigate to Worksheets Using Excel Status Bar

Move to the sheet that you’ve chosen.

Navigate to Worksheets Using Excel Status Bar

 


Download Practice Workbook

You can download the practice workbook from the link below.


Related Articles

<< Go Back To Table of Contents in Excel | Hyperlink in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

2 Comments
  1. I created a Table of Contents with the sheet names but under the sheet names, I want a list of titles on the linked sheet that I can click to go directly to that spot on the sheet. I know I can do this manually, but I want to set it up so that if I insert or delete rows, the links will update to the new location. I hope that makes sense.

    • Hello Julie Parker,
      Thank you for your question. I have looked into this matter, and so far, I haven’t been able to find a solution to your particular query. In the meantime, I have prepared an excel template with a table of contents that you may download from the link below. That said, I will let you know when I find a solution. I hope this was helpful.

      Table of Contents.xlsx

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo