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.
Likewise, the Sales Data for 2021 are shown below.
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.
- 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.
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.
This inserts a clickable link into the string of text as shown in the image below.
In a similar fashion, repeat the process for the 2020 Sales Data worksheet.
In turn, follow the same procedure for the 2021 Sales Data worksheet.
Finally, the results should look like the picture given below.
Just like that, you’ve generated a table of content for your worksheets, it’s that easy!
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.
- 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.
- 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.
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.
Consequently, the results should look like the following image below.
Lastly, repeat the same procedure for the other two worksheets as depicted below.
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.
- 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.
- 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")
- Likewise, type in the expression below to repeat the procedure for the 2021 Sales Data worksheet.
=HYPERLINK("#'2021 Sales Data'!A1","2021 Sales Data")
Subsequently, after completing all the steps the results should look like the image shown below.
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.
This opens the Visual Basic Editor in a new window.
📌 Step-02: Insert VBA Code
- Secondly, go to the Insert tab >> select Module.
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
⚡ 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.
📌 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.
Eventually, the results should look like the screenshot given below.
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.
- 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.
- 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.
That’s it, you’ll move to the sheet that you’ve chosen.
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.
Download Practice Workbook
You can download the practice workbook from the link below.
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.