How to Add a Table of Contents to Excel (And Why You Should)

Want to organize all the worksheets in your Excel workbook? Try creating a table of contents. It makes it easy to find specific sheets, especially if your Excel file contains hundreds of them. Unfortunately, Excel doesn’t have a one-click feature for creating a table of contents, but there is a way!




Why You Should Add a Table of Contents to Excel

What would you do if you had hundreds of sheets in an Excel workbook and needed to find a specific one for updating or modifying data? Searching manually would take too much time. But, if you create a table of contents, you can easily navigate through the workbook and quickly find the sheet you need.

As an SEO content writer, I sometimes work with and manage large Excel files containing keyword data. With a table of contents, I can easily jump to the exact Excel sheet where the required information is stored, saving a lot of time and effort. It also eliminates the need to scroll through countless sheets and tabs.

A table of contents helps you maintain a structured layout by organizing related worksheets and sections logically, improving the overall user experience. It also makes it easier for your team members to find specific sections for input and review. Additionally, you can minimize errors by reducing the chances of accidentally modifying unrelated data.


I’ll be using
Microsoft Excel 365
for this demonstration. My workbook already contains four worksheets: Team A, Team B, Team C, and Team D.

Manually Add Table of Contents to Excel

To create a table of contents manually, first decide where you want to place it. It’s recommended to create a new worksheet for the table of contents to make it easier to locate and manage.

To create a new sheet, right-click on any existing worksheet name and click on “Insert,” then select “Worksheet.” Alternatively, you can press Shift+Alt+F1.

Inserting new Worksheet from the Excel Insert window.


Next, select the cell where you intend to add the hyperlink, such as B5 (or any cell you prefer).

After selecting the cell, go to the Insert tab, click on the “Link” drop-down item, and select the “Insert Link” option to display the Insert Hyperlink dialog box. You can also access it using the Ctrl+K shortcut.

Inserting link using the 'Insert Link' option of 'Link' group in the 'Insert' tab.

Navigate to the Place In This Document section, select your preferred sheet, and then type the text you want to display for the hyperlink. After doing this, press “OK” to insert the link.

Creating hyperlink of Workbook sheets in the 'Insert Hyperlink' context menu in Excel.


Repeat the process for the other sheets.

That’s it! Now you have clickable links that will take you directly to the corresponding sheets when clicked.

Displaying Table of Contents with hyperlink in Excel worksheet.

Another way to manually add a table of contents in Excel is by using the Hyperlink Function. In this method, you need to type all the names of your sheets and add hyperlink formulas to each one individually.

To get started, choose the cell where you want the TOC to appear and enter the following formula:

=HYPERLINK("#'WorkSheetName'!A1", "FriendlyName")


Here, “WorkSheetName” is the name of the worksheet for which you want to create a link. The “#” symbol identifies the worksheet, and the exclamation mark “!A1” represents the cell location on the targeted worksheet. Lastly, “FriendlyName” variable represents the name that will be displayed in the table of contents.

Applying 'HyperLink Function' on each of the sheet name in Excel.

Repeat this process for the other sheets using the same formula.

Automatically Build Table of Content

You can automatically create a table of contents using Excel’s Power Query tool. With this tool, you can list hundreds of sheets on a specific sheet with just a few clicks and create hyperlinks that will take you directly to each respective sheet when clicked.


To get started, go to the Data tab in Excel. Click on “Get Data,” then select “From File” and hit the “From Excel Workbook” option.

Getting data from Excel Workbook file.

A dialog box will appear where you can select the workbook you’re currently working in. Once you’ve selected it, click “Import.”

Importing Excel Workbook file.

Instead of choosing a specific table or sheet from the list that appears, select the name of the workbook itself. Then, click on “Transform Data.”


Transforming the data of the entire Workbook.

You will now see a list of all the sheets, tables, and defined names within the workbook. Since we only want the sheet names, apply a filter to show only the sheets from the “Kind” option.

Applying filter to show only the sheets kind Workbook.

Next, right-click on the “Name” column (which contains the sheet names) and select “Remove Other Columns.” This step leaves you with just one column that lists all the names of the sheet.


Removing all other columns except the sheet name column.

You can also rename your header to any preferred name. After making these changes, click on the “Close & Load To” option.

Load the specific columns names to Excel Workbook.

Select “Existing Worksheet” and enter the cell where you want the list to start (e.g., cell A1 or B5).

Importing date to existing Worksheet.


You’ll now have a collection of all the sheet names in your workbook.

The last step is to create hyperlinks for the sheet names using the hyperlink formula. When you create a hyperlink for the first sheet and press Enter, all the sheet columns will automatically update with their hyperlinks. If not, you can simply drag the fill handle to apply the formula to all rows in your table of contents.

You can create hyperlink using the following formula:

=HYPERLINK("#'"&[@WorkSheetName]&"'!A1", [@FriendlyName])

Applying Hyperlink Formula to each Workbook sheet in the power query method.

Now, if you click on any of the hyperlinks, it will take you straight to the corresponding sheet in your workbook.


Auto Refresh Sheet

One of the great benefits of using Power Query is that you can easily update your table of contents whenever you add or remove sheets from your workbook.

For example, I’ve added a new sheet to my workbook and saved it with the name “Team E.” Now I want this sheet to appear in the table of contents with its hyperlink.

To update the outline, simply go back to the master sheet and double-click on the “Table of Contents” Excel query that is displayed to the right of your workbook.

Opening the table of contents query from the 'Queries & Connections' tab.

In the opened menu, click on “Refresh Preview” to update your Table of Contents.


Refreshing the table of contents by clicking the 'Refresh Preview'.

However, when you update it, any table or defined name recently added will also be included in the updated Table of Contents. To filter it, navigate to the “Filtered Rows” option, click on the “Kind” dropdown, and select only the “Sheet.”

Filtering unnecessary sheets from the table of contents.

That’s it! Power Query will automatically update the Table of Contents and include the newly added sheet.


Displaying updated table after adding new sheet.

Use VBA Code Script

If your workbook is large, you can also use a VBA macro to automate the process by iterating through all sheets, creating a list entry for each, and inserting a hyperlink.

To add the VBA code, you need the Developer tab. If you’ve not accessed it before, it is not visible in the Ribbon. However, you can activate it by going to File > Options > Customize Ribbon and turning on the “Developer” option.

Turning on the developer option from the ribbon customization option.

Next, head over to the Developer tab and select the “Visual Basic” option to open the VBA editor, or simply use the Alt+F11 shortcut.


Opening the Visual Basic editor.

To insert a new module, click Insert > Module.

Inserting new module.

Finally, paste the VBA code provided by Dennis Wallentin into the editor window and click “Run” or press F5 to execute the code.

Pasting and running the VBC code in editor.


And that’s it! You’ve created a table of contents worksheet for your Excel workbook.

Displaying table of contents.

If your workbook has many sheets, it can be helpful to add a hyperlink on each sheet that returns you to the master TOC page.

To begin, open the sheet where you want to add the return link and choose the cell where you need the link to display. Next, go to Insert > Link > Place in This Document. Select the master sheet and type “Table of Contents Page” as the display text.

Linking back to table of contents page.


You’ve now created a link that, when clicked, returns you to your main Table of Content page. You can easily copy this link and paste it on all other sheets.


Whether you’re dealing with a few sheets or a large workbook, these provided methods will help you create a table of contents efficiently.

Leave a Comment

url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url url