How to Create Table of Contents in Excel with Page Numbers

When you work on multiple worksheets in Excel, it is obvious to keep track of each of them. You will need a shortcut to access any worksheet. This is where you will need a Table of Contents in your Excel workbook. In this article, we will learn how to create a table of contents with page numbers in Excel in 3 easy ways.

How to Create Table of Contents in Excel with Page Numbers: 3 Easy Ways

Let us take an example of 3 datasets. Here, the first one shows the information on the Employee History of a company.

The second one has the Sales History for the month of June.

The last one shows the Sales History for the month of July.

Now, we will create a table of contents based on these worksheets.

1. Use Link to Create Table of Contents with Page Numbers

To create a table of contents with page numbers we can use the Link tool in Excel. Let’s see the step-by-step process below:

Step 1: Insert Page Numbers in Individual Worksheets

First, we will insert page numbers in the worksheets.

• In the beginning, go to the View tab and select Page Layout from the Workbook Views group.

• After that, you will notice 3 blocks in the upper Header section on the page.
• Similar blocks will also appear in the Footer section below.
• Now, click on any block where you want the page number.

• Next, go to the Header & Footer tab.
• Here, select Page Number under the Header & Footer Elements group.

• Afterward, you will see the &[Page] appear on the Header block.

• Finally, click any cell of the worksheet and you will see the page number.

• In addition to that, type ‘of’ after &[Page] and then go to the Header & Footer tab.
• Here, select the Number of Pages option.

• Initially, the Header block will look like this:

• Now click any cell and it will show the page number among all the pages.

• Apply the same procedure for other worksheets as well.

Step 2: Create Table of Contents with Link

Now, we will create a table of contents based on the worksheets.

• First, create a new worksheet where you want to create the table of contents.
• Then, in this worksheet, right-click on cell B4.
• Afterward, select Link from its Context Menu.

• You will get this Link command under the Insert tab.

• Next, the Insert Hyperlink window pops up.
• Here, select Place in this Document.
• After that, choose the first worksheet Employee History.

• Following, insert the page number beside the worksheet name in the Text to display box.

• Then, press OK.
• Finally, you will see that the worksheet is inserted as a table of contents.

• Follow the similar procedure for other worksheets as well.
• After some formatting in cells, you will get the final result like this:

• Now click on any of the worksheet names and it will direct you to that page.

Read More: How to Create Table of Contents in Excel with Hyperlinks

2. Make Table of Contents Using Excel GET.WORKBOOK Function

In this method, we will use the GET.WORKBOOK function in Excel to create a table of contents with page numbers.

• In the beginning, you need to name the worksheets along with the page numbers like the following image.

• Then, open a new sheet “TOC” where you want to create a table of contents with page numbers, and go to the Formulas tab.
• Here, select Define Name under the Define Names group.

• After that, a New Name window appears.
• In this window, type WorksheetNames in the Name box.

• Then, type this formula in the Refers to box.

• After that, press OK.
• Now, select the cell where you want to insert the name of the first worksheet. For example, here we selected cell B4.
• Following, insert this formula in cell B4.

• Then, press Enter.
• It will show the 1st worksheet name in your workbook.
• Apply the same formula for cells B5 and B6 where the ROW in the formula will be A3 and A4 respectively.
• The final output looks like this:

Read More: How to Create Table of Contents Without VBA in Excel

3. Apply Excel VBA Macro to Make Table of Contents with Page Numbers

In this section, we will make a table of contents with page numbers by applying VBA Macro code in Excel. Let’s follow the process below:

• First, go to the Developer tab.
• Then, select Visual Basic under the Code group.

• Following the Visual Basic window, select Module from the Insert section.

• Now, insert this code on the blank page and click on the Run Sub button or press F5 on your keyboard.

Sub CreateTableofcontents()
    Dim yAlerts As Boolean
    Dim L As Long
    Dim xSheetIndex As Worksheet
    Dim xSt As Variant
    yAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Table of contents").Delete
    On Error GoTo 0
    Set xSheetIndex = Sheets.Add(Sheets(1))
    xSheetIndex.Name = "Table of contents"
    L = 2
    Cells(2, 2).Value = "Table of contents"
    For Each xSt In ThisWorkbook.Sheets
        If xSt.Name <> "Table of contents" Then
           L = L + 2
            xSheetIndex.Hyperlinks.Add Cells(L, 2), "", "'" & xSt.Name & "'!A1", , xSt.Name
        End If
    Next
    Application.DisplayAlerts = yAlerts
End Sub

• Finally, you will get the table of contents based on the worksheets on a new  sheet.

Read More: How to Make Table of Contents Using VBA in Excel

Download Workbook

Download the sample file from here to practice by yourself.

Conclusion

I hope it was a helpful article for you on how to create a table of contents in Excel with page numbers in 3 easy ways. Let us know your feedback.

Related Articles

• How to Create Table of Contents Automatically in Excel
  
• How to Create Table of Contents for Tabs in Excel