Table of Content Excel – Easy Ways to Create Table of Content in Excel Spreadsheet

  • Home
  • / Table of Content Excel – Easy Ways to Create Table of Content in Excel Spreadsheet

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.

add content table manually_11zon

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.

content table context menu2_11zon

  • To get the Link option, you can also open the Insert tab from the ribbon and then choose Link from the Links menu.

content table context menu3_11zon

  • 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.

content table context menu4_11zon

  • Cell B5 has now added a link.

content table context menu5_11zon

  • The same method will be used to add a link in each cell of your content table.

content table context menu6_11zon

  • Click on any tab and you will be directed to that particular sheet tab.

content table context menu7_11zon

  • 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


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.


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.


Write your comment Here