Skip to main content
Skip to main content
Microsoft 365

Add a table of contents to your workbook – it’s easy, I promise!

Sometimes workbooks can be very large and hard to navigate. Only so many tabs fit across the bottom of the screen, and it’s hard to know how long each worksheet is. Excel doesn’t have a built-in way to add a table of contents to a workbook; however, there is a way! In this post, I’ll show you how to add a new worksheet to the beginning of your workbook called “TOC” (table of contents). This example uses Excel 2010.


On the TOC sheet, Column A lists each sheet name and includes a hyperlink shortcut to the appropriate worksheet. Column B lists the worksheet number (which sheet it is in the workbook) and the number of pages contained on that worksheet (how many printed pages it would be). You have to use VBA (Visual Basic for Applications), but don’t be scared off by the fact that you need code—it’s actually pretty simple, and I’ll walk you through it step-by-step.

Microsoft Excel

Turn data into insights.

Get Excel

First, you have to add the code to your workbook, and to do that you need the Developer tab. If you don’t usually work with code in Excel, you probably don’t see the Developer tab in the Ribbon. To show the Developer tab:

  1. Click the File tab.
  2. Under Help, click Options.
  3. Click Customize Ribbon.
  4. Under Customize the Ribbon, select the Developer check box.

Now you can create a macro:

  1. On the Developer tab, in the Code group, click Visual Basic.image
  2. In the Visual Basic Editor, on the Insert menu, click Module.
  3. In the code window of the module, type or copy the following macro code:

Option Explicit
Sub Create_TOC()
Dim wbBook As Workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet

Dim lnRow As Long
Dim lnPages As Long
Dim lnCount As Long

Set wbBook = ActiveWorkbook

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

‘If the TOC sheet already exist delete it and add a new

On Error Resume Next
With wbBook
.Worksheets.Add Before:=.Worksheets(1)
End With
On Error GoTo 0

Set wsActive = wbBook.ActiveSheet
With wsActive
.Name = “TOC”
With .Range(“A1:B1”)
.Value = VBA.Array(“Table of Contents”, “Sheet # – # of Pages”)
.Font.Bold = True
End With
End With

lnRow = 2
lnCount = 1

‘Iterate through the worksheets in the workbook and create
‘sheetnames, add hyperlink and count & write the running number
‘of pages to be printed for each sheet on the TOC sheet.
For Each wsSheet In wbBook.Worksheets
If wsSheet.Name <> wsActive.Name Then
With wsActive
.Hyperlinks.Add .Cells(lnRow, 1), “”, _
SubAddress:=”‘” & wsSheet.Name & “‘!A1”, _
lnPages = wsSheet.PageSetup.Pages().Count
.Cells(lnRow, 2).Value = “‘” & lnCount & “-” & lnPages
End With
lnRow = lnRow + 1
lnCount = lnCount + 1
End If
Next wsSheet


With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Now, save your workbook with the macro code.

  1. In the Visual Basic Editor, on the File menu, click Close and Return to Microsoft Excel.
  2. On the File menu, click Save As, and then save the file as an Excel Macro-Enabled Workbook (.xlsm).

You are almost ready to run the code. You might need to change your macro security settings to enable macros. To set the security level temporarily to enable all macros, do the following:

  1. On the Developer tab, in the Code group, click Macro Security.clip_image002
  2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.Note    To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

And now you can run the code that creates the table of contents worksheet!

  1. On the Developer tab, in the Code group, click Macros.clip_image002[1]
  2. In the Macro window, select the Create_TOC macro, and click Run.


Related posts

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…

Image for: Surface Pro X and Surface Arc Mouse inside on desk with Excel and search on screen.

Connect to your own data with more new data types in Excel

The evolution of Excel Excel is the ultimate decision-making tool. We’re amazed every day by the ways in which you, our customers, use Excel to make better decisions, leveraging the flexibility of the 2D grid and formulas to capture, analyze and collaborate on data. Up to this point, Excel has only had a couple base…