Workbooks that contain large datasets make it quite difficult to organize data for navigation. You keep on scrolling and just see tabs expanding wide and you have no idea how many items or products are there in each sheet.
A table of contents in Excel can help you in this situation, but there is no built-in feature that can directly help you in making content tables for ease. Well, we have come up with a few useful methods to make content tables because larger sheets tend to become uncontrollable.
Let’s get started:
How to Create a Table of Content Excel Manually
In this method, you will follow simple steps without being involved in lengthy procedures.
- Enter each sheet’s name and insert links to the cells. Once this step is done, now lets’ move on to the next part:
- Right-click on the worksheet name and make a new sheet. Choose Insert Sheet and type a valid name there.
- Enter the first sheet name in cell B4.
- Put the link to the cell. For this, right-click on the cell and choose the Hyperlink option.
- Choose This document option and click on the sheet name you need to make the list for.
- Cell A1 would be preferable for reference.
- Now, you need to repeat steps 2 and 3 until each sheet has a table of contents.
How Table of Contents in Excel is Added with Context Menu
In this method, you will be using the context menu for adding a link. Below are the steps to follow:
- Type the sheet tab name and insert a link. By clicking on the link you will be directed to that particular sheet.
- Once you are done with typing all the sheet’s tab names, right-click on cell B5.
- The Context Menu will open up.
- Choose the Link option.
- To get the Link option, you can also open the Insert tab from the ribbon and then choose Link from the Links menu.
- The Insert Hyperlink box will be there on the screen.
- Choose Place in This Document from the Link to section.
- Now, set cell reference.
- Choose the place where you need to add a hyperlink.
- Click OK.
- Cell B5 has now added a link.
- The same method will be used to add a link in each cell of your content table.
- Click on any tab and you will be directed to that particular sheet tab.
- If you click on the Australia tab, it will take you to the Australia sheet.
How to Make a Table of Content with VBA Code
Below you will find the VBA code that can help you in making a table of content Excel with links. For this, follow the steps:
Open the Microsoft Visual Basic for Applications window by pressing the ALT + F11 key.
Choose the Insert option and then click on Module. Now, copy the below given VBA code in the Code window.
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 = xAlerts
End Sub
Press the F5 key and choose the Run button to activate the code.
You will see a table of content Excel in front of all sheets with their names. Click on the sheet name to control the sheet activity.
Summary
You can have a Table of content Excel by using any of the above methods as per your choice. Each method is fairly effective and user-friendly that’s why you don’t need to be worried about it.
