How to Create Table of Contents Automatically in Excel

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to create a table of contents in Excel? Then, you’ve come to the right place! You can create a table of contents in Excel to navigate to the worksheet of your choice with just one click. In this article, we’ll demonstrate 4 handy ways to automatically create a table of contents in Excel.


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

Admittedly, Excel does not offer any feature to generate a table of contents. Instead, you can utilize Excel functions, VBA code, and keyboard shortcuts to obtain a table of contents. Therefore, without further delay, let’s see each method individually.
Let’s say, we have the Quarterly Sales Data shown in the B4:F14 cells. Here, the dataset shows the Location and Quarterly Sales for the year 2019.

Dataset 1

Dataset 1Likewise, the Sales Data for 2021 are shown below.
Dataset 1
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


Method-1: Utilizing Keyboard Shortcut to Create Table of Contents

Wouldn’t it be great if only there were a keyboard shortcut to create a table of contents in Excel? Well, you’re in luck because our first method describes just that. So, follow these simple steps.

📌 Steps:

  • At the very beginning, type in the name of the worksheet. In this case, the name of our worksheet is 2019 Sales Data.
  • Next, press the CTRL + K key on your keyboard.

Using Keyboard Shortcut

This brings up the Insert Hyperlink wizard.

  • Now, 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 string of text as shown in the image below.

Automatically Create Table of Contents in Excel Using Keyboard Shortcut

In a similar fashion, repeat the process for the 2020 Sales Data worksheet.

Automatically Create Table of Contents in Excel Using Keyboard Shortcut

In turn, follow the same procedure for the 2021 Sales Data worksheet.

Automatically Create Table of Contents in Excel Using Keyboard Shortcut

Finally, the results should look like the picture given below.

Automatically Create Table of Contents in Excel Using Keyboard Shortcut

Just like that, you’ve generated a table of content for your worksheets, it’s that easy!

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


Method-2: Employing ALT Key to Generate Table of Contents

Suppose you already have a heading for your table which you want to insert as the index name in the table of contents. Our next method answers this very question. So just follow along.

📌 Steps:

  • Initially, select the heading (here it is 2019 Sales Data).
  • Next, press and hold down the ALT Key and the right mouse button.

📄 Note: This method will only work if your worksheet has been already saved. So, make sure to press the CTRL + S key to save your worksheet first.

Using ALT Key

  • Now, hover the cursor at the edge of the selected B1 cell and drag it into the worksheet with the table of contents. In this case, it is the ALT Key worksheet.

Automatically Create Table of Contents in Excel Using ALT Key

This brings you to the ALT Key worksheet.

  • Following this, let go of the ALT key and drag the cursor into the desired location (B5 cell) while holding down the right mouse button.
  • In turn, 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

Consequently, the results should look like the following image below.

Automatically Create Table of Contents in Excel Using ALT Key

Lastly, 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

If you’re one of those people who enjoy using Excel formulas then our next method has you covered. Here, we’ll apply the HYPERLINK function to embed links that refer to the worksheets. So, let’s begin.

📌 Steps:

  • Firstly, go to the B5 cell and enter the expression below.

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

In this formula, the “#’2019 Sales Data’!A1” is the link_location argument and refers to the location of the 2019 Sales Data worksheet. Finally, 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

  • Secondly, 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

  • Likewise, type in the expression below to repeat the procedure for the 2021 Sales Data worksheet.

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

Automatically Create Table of Contents in Excel Using HYPERLINK Function

Subsequently, after completing all the steps the results should look like the image shown below.

Automatically Create Table of Contents in Excel Using HYPERLINK Function

Read More: How to Create Table of Contents in Excel with Hyperlinks


Method-4: Applying VBA Code to Create Automatic Table of Contents

If you often need to get the column number of matches, then you may consider the VBA code below. It’s simple & easy, just follow along.


📌 Step-01: Open Visual Basic Editor

  • Firstly, navigate to the Developer tab >> click the Visual Basic button.

Applying VBA Code

This opens the Visual Basic Editor in a new window.


📌 Step-02: Insert VBA Code

  • Secondly, go to the Insert tab >> select Module.

Automatically Create Table of Contents in Excel Applying VBA Code

For your ease of reference, you can copy the code from here and paste it into the window as shown 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:

Now, I will explain the VBA code used to generate the table of contents. In this case, the code is divided into 3 steps.

  • In the first portion, the sub-routine is given a name, here it is Excel_Table_Of_Contents().
  • Next, define the variables alerts, y, and Wrksht.
  • Then, assign Long, Boolean, and Variant data types respectively.
  • In addition, define Wrksht_Index as the variable for storing the Worksheet object.
  • In the second potion, remove any previous Table of Contents sheet using the Delete method.
  • Now, insert a new sheet with the Add method in the first position and name it “Table of contents” using the Name statement.
  • In the third portion, we declare a counter (y = 1) and use the For Loop and the If statement to obtain the names of the worksheets.
  • Finally, use the HYPERLINK function to generate clickable links embedded in the worksheet names.

Automatically Create Table of Contents in Excel Applying VBA Code


📌 Step-03: Running VBA Code

  • Now, press the F5 key on your keyboard.

This opens the Macros dialog box.

  • Following this, click the Run button.

Automatically Create Table of Contents in Excel Applying VBA Code

Eventually, the results should look like the screenshot given below.

Automatically Create Table of Contents in Excel Applying VBA Code

Read More: How to Make Table of Contents Using VBA in Excel


Navigating to Worksheets Using Status Bar

If you have lots of worksheets in Excel it may be difficult to navigate to the preferred location. However, Excel has one nifty trick up its sleeve! That is to say, you can use the Status Bar to navigate to any worksheet in a breeze. Now, allow me to demonstrate the process in the steps below.

📌 Steps:

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

Navigate to Worksheets Using Excel Status Bar

  • Next, right-click with the mouse.

In an instant, the Activate dialog box pops up which displays all the sheets.

  • Following this, choose the sheet, for example, we’ve chosen the 2021 Sales Data >> click the OK button.

Navigate to Worksheets Using Excel Status Bar

That’s it, you’ll move to the sheet that you’ve chosen.

Navigate to Worksheets Using Excel Status Bar


Practice Section

We have provided a practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

I hope this article helped you understand how to create a table of contents automatically in Excel. If you have any queries, please leave a comment below.


Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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