Create a Table of Contents in Excel

Have you ever had an Excel file with multiple worksheets? Wouldn’t it be nice to have a table of contents on a worksheet with links to each sheet? Many of us would shout, “Yes!”.

Unfortunately, Excel cannot do that by itself. But, by creating a macro, you can help Excel create the sheet. The website Computergaga even provides the code. There is one issue with the code, but it is resolved in the comments in their blog. I am providing the corrected code in this post.

Save As Macro-Enabled

You will want to save the Excel file as a Macro-Enabled Excel file. Just go to File and click on Save As. Change the Save As Type to Excel Macro-Enabled Workbook. See Figure 1.

Excel Macro-Enabled Workbook screenshot

Figure 1

Developer Tab

Before you can start doing anything with macros, you need to go to the options and add the developer tab. Click on File, Options, and Customize Ribbon. See Figure 2.

Developer tab screenshot

Figure 2

Click in the checkbox next to Developer in the list on the right side. Then, click OK. The Developer ribbon has everything for creating a macro on it.

Add the Module Code

Open the workbook where you want to create the Table of Contents. On the Developer tab, click the Visual Basic button (on the very left). The VBA window will open. Select the Insert tab and choose Module. See Figure 3.

Visual Basic window

Figure 3

Copy and paste the following code into the module window.

Sub CreateTOC()

Dim i As Byte

Const SheetName = “Table of Contents”

With Application

.ScreenUpdating = False

.DisplayAlerts = False

End With

If Sheets(1).Name = SheetName Then

Sheets(SheetName).Delete

End If

Sheets.Add Before:=Sheets(1)

Sheets(1).Name = SheetName

Range(“B2”).Value = SheetName

With Range(“B2”).Font

.Name = “Calibri”

.Size = 14

.Underline = xlUnderlineStyleSingle

.Bold = True

End With

Range(“B4”).Select

‘Loop through each sheet and create a table of contents using each sheet name

For i = 2 To Sheets.Count

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:=”‘” & Sheets(i).Name & “‘” & “!A1”, TextToDisplay:=i – 1 & “. ” & Sheets(i).Name

ActiveCell.Offset(2, 0).Select

Next

Range(“B4:B” & ActiveCell.Row).Font.Underline = xlUnderlineStyleNone

With Application

.ScreenUpdating = True

.DisplayAlerts = True

End With

End Sub

Finish and Run the Macro

After pasting in the code, click the Save button. Then, click File and Return to Excel. On the Developer tab, click the Macros button and choose the CreateToC macro. Then click the Run button. See Figure 4.

Macro Window with Excel ToC selected

Figure 4

After the macro runs, Excel will create a worksheet titled Table of Contents that will contain a link to each of the sheets in your workbook. If you add more sheets in the future, you just need to run the macro again. The Table of Contents worksheet will be removed and an updated one will replace it. See Figure 5.

ToC screenshot

Figure 5

Image Credit: https://www.flickr.com/photos/97741188@N04/

Angela Nino - Versitas Editor

Leave a Reply

Your email address will not be published. Required fields are marked *