Table of Contents Macros

When your workbook only has a few sheets, you can easily navigate to them using the tabs at the bottom of Excel. But for workbooks that have lots of sheets, I recommend adding a Table of Contents as your first sheet.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Downloads

List-Style Table of Contents

One format for your Table of Contents is a simple list, where each item in the list is a link that pulls you over to the corresponding sheet.

List-style Table of Contents

I've created a macro so that you can add this Table of Contents to any workbook in an instant. You can copy the macro from this tutorial:

Excel Table of Contents that Automatically Updates

Gallery-Style Table of Contents

A visually appealing alternative to the list-style format is a Table of Contents Gallery. This format shows screenshots from each of your worksheets. Each of the images links to its corresponding sheet so that you can jump to the sheet you want with one click.

Gallery-style table of Contents

For instructions about how to insert this type of Table of Contents into your file, check out this tutorial:

How to Add a Table of Contents Image Gallery Sheet to Your Excel Files

Help with Macros

If you don't have much experience working with macros, check out my free 3-part video series on getting started with macros & VBA.

I hope these free tools are helpful for you and impress your boss and coworkers! If you have questions or feedback, leave a comment below.

4 comments

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

  • Hey Jon, not withstanding all the good stuff that you produce & share all the time, I felt that your last email was packed with a lot of useful stuff (ToC macro etc…). So I just wanted to say thanks. I’ve signed up to your webinar for next week, so I’ll see you then. Cheers.

  • Jon,

    You’re a rock star with Excel. The short videos that you do have helped me in my everyday work. And you explain things in a great step by step process

  • Hi Jon
    I have copied the TOC macro into a file, and this works ok, except when returning to the TOC sheet I get the message below:
    “Run time error ‘AutoFilter’ of object range failed”
    If I click on the End button I get back to th sheet; if I hit the Debug button it highlights ‘.Resize(i, 2).AutoFilter’ which means nothing to me!
    I’m a Mac user, not conversant with Macros and 83 years old, which explains a lot!
    Thank you.
    Regards
    Colin

  • Hello Jon – I keep getting a “Run time error ‘AutoFilter’ of object range failed” alert when I use the TOC macro. Is it because I’m using Excel via a Microsoft 365 subscription on a MacBook Air and not a PC?

    How do I fix this error?

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter

Free Excel Training Webinar Modern Power Tools