How to Create a Table of Contents in Excel 2016

Published: 28 November 2016
on channel: Excel, Word and PowerPoint Tutorials from Howtech

41,956

140

In this HowTech written tutorial, we’re going to show you how to create a table of contents in Excel 2016.
Don't forget to check out our main channel / howtech for more free how-to videos!
Join us on Facebook
/ howtechtv
Our App
http://bit.ly/how-tech

The easiest way to do this especially if you only have a few worksheets is to select a cell and hit Ctrl+K to create a hyperlink to a selected worksheet.

The next method uses VBA to automatically create the hyperlinks to all the worksheets. Go to Options.

Click Customize Ribbon and check the Developer box.

Under the Developer tab, click Visual Basic.

Click Insert then click Module.

Copy and paste the following code:
Option Explicit
Sub Create_ToC()
Dim wbBook As Workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet
Dim lnRow As Long
Set wbBook = ActiveWorkbook
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

On Error Resume Next
With wbBook
.Worksheets("ToC").Delete
.Worksheets.Add Before:=.Worksheets(1)
End With
On Error GoTo 0
Set wsActive = wbBook.ActiveSheet
With wsActive
.Name = "ToC"
With .Range("A1")
.Value = VBA.Array("Table of Contents")
.Font.Bold = True
End With
End With
lnRow = 2

For Each wsSheet In wbBook.Worksheets
If wsSheet.Name - wsActive.Name Then
wsSheet.Activate
With wsActive
.Hyperlinks.Add .Cells(lnRow, 1), "", _
SubAddress:="'" & wsSheet.Name & "'!A1", _
TextToDisplay:=wsSheet.Name

End With
lnRow = lnRow + 1
End If
Next wsSheet
wsActive.Activate
wsActive.Columns("A:B").EntireColumn.AutoFit
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

To use Visual Basic Macros, you will need to save the file as an Excel Macro Enabled Workbook.

Click Macros under the Developer tab.

Select the Create_ToC Macro and Run it. If your original worksheet is named differently, you may need to change all instances in the VBA script of ToC.

A table of contents of all the worksheets should be created.

This is a HowTech tutorial, visit our website and watch our videos for more.

Watch video How to Create a Table of Contents in Excel 2016 online, duration hours minute second in high quality that is uploaded to the channel Excel, Word and PowerPoint Tutorials from Howtech 28 November 2016. Share the link to the video on social media so that your subscribers and friends will also watch this video. This video clip has been viewed 41,956 times and liked it 140 visitors.