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.
Contents
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
3. Next, select any cell on the sheet and right click on it to open the options menu. Now, select the Hyperlink option.
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.
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.
6. With this, the content with the hyperlink will be added to the table of 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
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.
2. Now click on Insert from the toolbar at the top and select Module
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 = xAlertsEnd Sub
4. Run the code by pressing the F5 button or by clicking on the Run button.
5. Next, go to Files and select Close and Return to Microsoft Excel
6. Now you will see the table of content is created with all the hyperlinked content all at once.
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.
