A table of contents is a total game-changer when working with large files – it keeps everything organized and easy to navigate. Unfortunately, unlike Word, Microsoft Excel doesn’t have a simple “Table of Contents” button that adds this handy feature and updates it automatically. No, you’ll have to roll up your sleeves and create a dynamic table of contents yourself. This table will automatically update and contain clickable links, allowing you to add and remove sheets – as well as jump between them – with ease. This guide has all the info you need to create a dynamic table of contents in Excel.
How to Create a Dynamic Table of Contents in Excel
Technically, there are three ways to create a dynamic table of contents (TOC) in Excel. However, only one of them guarantees a fully automated TOC, and that’s Visual Basic for Applications or VBA for short – Microsoft’s native programming language. The other two – traditional formulas and Power Query – will give you a semi-dynamic table of contents in Excel – one that either doesn’t include clickable links or doesn’t update automatically. Since we’re after a fully dynamic Excel table of contents, we’ll use VBA.
If you aren’t particularly VBA-savvy; don’t worry – you just need to follow a few steps. But first – let’s create our table of contents.
Step 1: Click on the “Insert Worksheet” button next to your sheets at the bottom.
Step 2: Name the sheet “Table of Contents.”
Step 3: Drag the sheet to the first position for better navigation.
Step 4: Enter the names of your sheets in Column A of the “Table of Contents” sheet.
And voilà – you’ve got your table of contents. You can play with the aesthetics of this TOC later – now, we need to make it dynamic. To do so, we’ll need the help of the VBA Editor – a built-in Excel tool that lets you write and run custom codes.
Step 1: Press “Alt + F11” to open the VBA Editor.
Step 2: Go to the “Insert” tab at the top.
Step 3: Select “Module” from the dropdown menu.
Step 4: Copy and paste the following VBA code:
Sub CreateTOC()
??? Dim ws As Worksheet
??? Dim toc As Worksheet
??? Dim i As Integer
??? ‘ Check if TOC sheet already exists, delete if it does
??? On Error Resume Next
??? Set toc = ThisWorkbook.Sheets(“Table of Contents”)
??? On Error GoTo 0
??? If Not toc Is Nothing Then Application.DisplayAlerts = False: toc.Delete: Application.DisplayAlerts = True
??? ‘ Create new TOC sheet
??? Set toc = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
??? toc.Name = “Table of Contents”
??? ‘ Set up TOC header
??? toc.Cells(1, 1).Value = “Table of Contents”
??? toc.Cells(1, 1).Font.Bold = True
??? toc.Cells(1, 1).Font.Size = 14
??? ‘ Loop through all sheets and add hyperlinks
??? i = 2
??? For Each ws In ThisWorkbook.Sheets
??????? If ws.Name <> “Table of Contents” Then
??????????? toc.Hyperlinks.Add Anchor:=toc.Cells(i, 1), _
??????????????? Address:=””, _
??????????????? SubAddress:=”‘” & ws.Name & “‘!A1”, _
??????????????? TextToDisplay:=ws.Name
??????????? i = i + 1
??????? End If
??? Next ws
??? ‘ Adjust column width
??? toc.Columns(“A”).AutoFit
?End Sub
Step 5: Hit “F5” to run the code.
Step 6: Exit the VBA Editor.
You’ll notice your Excel table of contents is now clickable.
To automatically update your table of contents after changes, you just need to repeat Steps 1 to 6. This will add any new sheets to the list or remove the ones you deleted.
The above is the detailed content of How to Create a Dynamic Table of Contents in Excel. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Windows 11 slow boot time fix
Jul 04, 2025 am 02:04 AM
The problem of slow booting can be solved by the following methods: 1. Check and disable unnecessary booting programs; 2. Turn off the quick boot function; 3. Update the driver and check disk health; 4. Adjust the number of processor cores (only for advanced users). For Windows 11 systems, first, the default self-start software such as QQ and WeChat are disabled through the task manager to improve the startup speed; if you use dual systems or old hardware, you can enter the power option to turn off the quick boot function; second, use the device manager to update the driver and run the chkdsk command to fix disk errors, and it is recommended to replace the mechanical hard disk with SSD; for multi-core CPU users, the kernel parameters can be adjusted through bcdedit and msconfig to optimize the startup efficiency. Most cases can be corrected by basic investigation
How to Change Font Color on Desktop Icons (Windows 11)
Jul 07, 2025 pm 12:07 PM
If you're having trouble reading your desktop icons' text or simply want to personalize your desktop look, you may be looking for a way to change the font color on desktop icons in Windows 11. Unfortunately, Windows 11 doesn't offer an easy built-in
Fixed Windows 11 Google Chrome not opening
Jul 08, 2025 pm 02:36 PM
Fixed Windows 11 Google Chrome not opening Google Chrome is the most popular browser right now, but even it sometimes requires help to open on Windows. Then follow the on-screen instructions to complete the process. After completing the above steps, launch Google Chrome again to see if it works properly now. 5. Delete Chrome User Profile If you are still having problems, it may be time to delete Chrome User Profile. This will delete all your personal information, so be sure to back up all relevant data. Typically, you delete the Chrome user profile through the browser itself. But given that you can't open it, here's another way: Turn on Windo
How to fix second monitor not detected in Windows?
Jul 12, 2025 am 02:27 AM
When Windows cannot detect a second monitor, first check whether the physical connection is normal, including power supply, cable plug-in and interface compatibility, and try to replace the cable or adapter; secondly, update or reinstall the graphics card driver through the Device Manager, and roll back the driver version if necessary; then manually click "Detection" in the display settings to identify the monitor to confirm whether it is correctly identified by the system; finally check whether the monitor input source is switched to the corresponding interface, and confirm whether the graphics card output port connected to the cable is correct. Following the above steps to check in turn, most dual-screen recognition problems can usually be solved.
Fixed the failure to upload files in Windows Google Chrome
Jul 08, 2025 pm 02:33 PM
Have problems uploading files in Google Chrome? This may be annoying, right? Whether you are attaching documents to emails, sharing images on social media, or submitting important files for work or school, a smooth file upload process is crucial. So, it can be frustrating if your file uploads continue to fail in Chrome on Windows PC. If you're not ready to give up your favorite browser, here are some tips for fixes that can't upload files on Windows Google Chrome 1. Start with Universal Repair Before we learn about any advanced troubleshooting tips, it's best to try some of the basic solutions mentioned below. Troubleshooting Internet connection issues: Internet connection
Want to Build an Everyday Work Desktop? Get a Mini PC Instead
Jul 08, 2025 am 06:03 AM
Mini PCs have undergone
How to clear the print queue in Windows?
Jul 11, 2025 am 02:19 AM
When encountering the problem of printing task stuck, clearing the print queue and restarting the PrintSpooler service is an effective solution. First, open the "Device and Printer" interface to find the corresponding printer, right-click the task and select "Cancel" to clear a single task, or click "Cancel all documents" to clear the queue at one time; if the queue is inaccessible, press Win R to enter services.msc to open the service list, find "PrintSpooler" and stop it before starting the service. If necessary, you can manually delete the residual files under the C:\Windows\System32\spool\PRINTERS path to completely solve the problem.
