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
=HYPERLINK("#'Sales of January'!A1"," January Sales Data")
Formula Breakdown
- Here, the HYPERLINK function will create a link to go to a particular worksheet.
- Firstly, Sales of January is the name of the worksheet for which I want to create a link.
- Secondly, Hash Tag (#) will find the worksheet.
- Thirdly, Exclamatory (!) A1 represents the Cell Location of the Sheet named Sales of January.
- Fourthly, January Sales Data is the Friendly Name which means this name will be in the Content name.
- 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
=HYPERLINK("#'Sales of February'!A1"," February Sales Data")
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
=HYPERLINK("#'"&[@Name]&"'!A1")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.
Formula Breakdown
- Here, the HYPERLINK function will create a link to go to a particular worksheet.
- Firstly, Hash Tag (#) will ensure that the worksheet is in the same workbook.
- Secondly, @Name denotes the name of the worksheet for which you want to create the link.
- Thirdly, Exclamatory (!) A1 represents the Cell Location of the Sheet named Dataset.
- Fourthly, the Ampersand (&) operator will connect the name and location.
- 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
Code Breakdown
- Here, I have created a Sub Procedure named Table_of_Contents.
- Next, declare some variables Alert_data as Boolean; numb as Long; Sheet_Index as Worksheet; Sheet as Variant.
- If there is any Table of Contents in the active worksheet then the Delete command will delete that.
- Then, I have set the worksheet name as VBA where the Table of Contents will present.
- After that, I used a For Each Loop to include all the worksheets in the Table of Contents.
- 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.
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
