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.


Download Workbook

Download the sample file from here to practice by yourself.


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

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

Table of Contents in Excel with Page Numbers

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.

Use Link to Create Table of Contents with Page Numbers

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

Use Link to Create Table of Contents with Page Numbers

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

Use Link to Create Table of Contents with Page Numbers

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

Use Link to Create Table of Contents with Page Numbers

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

Use Link to Create Table of Contents with Page Numbers

  • Initially, the Header block will look like this:

Use Link to Create Table of Contents with Page Numbers

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

Use Link to Create Table of Contents with Page Numbers

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

Use Link to Create Table of Contents with Page Numbers

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

Use Link to Create Table of Contents with Page Numbers

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

Use Link to Create Table of Contents with Page Numbers

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

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

Table of Contents in Excel with Page Numbers

  • 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 (5 Ways)


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

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

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

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

  • Now, insert this code on the blank page.
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
<img class="aligncenter size-full wp-image-200353" src="https://www.exceldemy.com/wp-content/uploads/2022/08/Table-of-Contents-in-Excel-with-Page-Numbers-3-4.png" alt="" width="952" height="503" />
  • After that, click on the Run Sub button or press F5 on your keyboard.

  • Finally, you will get the table of content based on the worksheets.
  • After some formatting the output looks like this:

Note: You need to name the worksheets along with the page numbers like the following image. Otherwise, the VBA code will only show the name.

Read More: How to Make Table of Contents Using VBA in Excel (2 Examples)


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

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

  • In the beginning, go to the Formulas tab.
  • Here, select Define Name under the Define Names group.

Make Table of Contents Using Excel GET.WORKBOOK Function 

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

Make Table of Contents Using Excel GET.WORKBOOK Function 

  • Then, type this formula in the Refers to box.
=GET.WORKBOOK(1)&REPT(NOW(),)

Make Table of Contents Using Excel GET.WORKBOOK Function 

  • 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.
=IF(ROW(A2)>SHEETS(),REPT(NOW(),),SUBSTITUTE(HYPERLINK("#'"&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(TabNames,ROW(A2))," ",CHAR(255)),"]",REPT(" ",32)),32))&"'!A1",TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(TabNames,ROW(A2))," ",CHAR(255)),"]",REPT(" ",32)),32))),CHAR(255)," "))

Make Table of Contents Using Excel GET.WORKBOOK Function 

The IF function applied here is referenced from Professor Excel.

  • Then, press Enter.
  • It will show the 1st worksheet name in your workbook.
  • Apply the same formula for cell 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


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. Follow ExcelDemy for more Excel articles.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo