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.
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.
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.
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?