Create a Hyperlinked Table of Contents in Excel ๐
Learn to make a master sheet with clickable links to all sheets, streamlining navigation in your Excel workbook.

Sharon Smith
44.3K views โข Jan 24, 2024

About this video
How to create a master sheet table of contents in Excel that has hyperlinks to all the sheet tabs in your workbook. Create a quick list of all existing sheets in your Excel workbook using Power Query. This loads a dynamic table that can be refreshed when new sheet tabs are added or deleted. Create a column for the hyperlinks to the sheet tabs. Using this hyperlink formula will fill down the table and your master sheet table of contents is created very quickly and easily.
Hyperlink formula used in this video (replace "Index" with your own sheet name):
=HYPERLINK("#'"&[@Index]&"'!A1",[@Index])
Follow along with the Excel sheet used in this video tutorial: https://drive.google.com/drive/folders/1tZsrkCtdUHkvFZ5vfYpoBFTPefY1F-sZ?usp=sharing
TIMESTAMPS โฐ
00:00 Create Table of Contents in Excel
00:07 Pause OneDrive Sync
01:09 Create List of Excel Sheet Tabs in Power Query
02:30 How to Format Table in Excel
02:37 Create Hyperlinks to Sheet Tabs with Formula
04:24 Create Link Back to Master Sheet Tab in Excel
05:52 Format Table of Contents in Excel
06:27 Auto Refresh List when Sheets are Added or Deleted
๐FREE COURSE: How To Create Fillable Forms in Microsoft Word - A Step-by-Step Guide
Create Fillable Forms, Surveys & Questionnaires in Microsoft Word like a Pro!
https://youtu.be/438pCPCSuG4
WATCH NEXT ๐บ Automate Report Formatting & Combine Multiple Sheets with Power Query: https://youtu.be/WvyUtNvG-H8
CHANNEL LINK ๐บ
https://www.youtube.com/@SharonSmith
Visit my Channel page on YouTube to see all my videos, playlists, community posts and more!
TEMPLATES ๐
Check out my helpful list of templates available for purchase: https://creatoriq.cc/43c51cv
Thank you for supporting my channel! ๐
FREE DOWNLOADS โฌ๏ธ
โฆ Visit https://mailchi.mp/6a0859ea0844/sharonsmithhrformulasdownload to sign-up for my e-mail list and get FREE downloads of super helpful spreadsheet formulas, dashboards and Org Chart templates for HR professionals.
CONNECT WITH ME ๐
Visit my website: https://www.sharonsmithhr.com for more information, tools and resources.
LinkedIn: https://www.linkedin.com/in/sharonsmithhr
Twitter: https://twitter.com/SharonSmithHR
Instagram: https://www.instagram.com/sharonsmithlearning/
Facebook: https://www.facebook.com/SharonSmithLearning
GEAR โ๏ธ
๐ Blue Yeti USB Microphone: https://amzn.to/2W4SbzV (Great for recording professional sounding audio for your videos!)
๐ฑ Silent Mouse: https://amzn.to/3pxpc25 (This is a really cool mouse!)
๐ฅ Screen Recording Software: https://techsmith.z6rjha.net/NZG5b
๐ Green Screen: https://amzn.to/2DnHsY2
๐ธ Camera: https://amzn.to/39KvpQA
๐ Live Stream Tool: https://amzn.to/2VFJyID (Turns your DSLR into a top notch webcam)
RESOURCES ๐
โ๏ธ JotForm: https://www.jotform.com/pricing/?utm_source=sharon-smith&utm_campaign=jf1&utm_medium=blog
๐ฅ๏ธ TechSmith Camtasia Screen Recording: https://techsmith.z6rjha.net/SharonSmithHR
๐ Wondershare PDFelement (PDF Editor): https://bit.ly/31QEkA8
๐ป PresenterMedia: https://shareasale.com/r.cfm?b=417324&u=2065055&m=43706&urllink=&afftrack=
Links included here are affiliate links. If you click on these links and make a purchase, I may earn a small commission at no additional cost to you. Thanks for supporting this channel!
SUPPORT THIS CHANNEL ๐
- Hit the "$Thanks" button on any video, or
- Donate through my PayPal link: https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=AJJ6SXERNDMYA&source=url
If you found this content helpful, please consider donating to my channel. Your donation, no matter what amount, is greatly appreciated and goes towards producing more content that enhances your productivity and elevates your skills.
You can also support my channel just by watching, liking, and sharing all my videos! Thank you so much! โค๏ธ
#exceltutorial #datamanagement #productivity #powerquery #exceltips
PLAYLISTS ๐ฅ
โค Jotform Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuO84pPbnHdTdgurmxHgZcyD
โคCreate A Letterhead in Word: https://www.youtube.com/playlist?list=PLb80efKTbWuO0RyeiYB0fEb55601yL4OT
โคCreating Fillable Forms: https://www.youtube.com/watch?v=YktZ3B-tyqs&list=PLb80efKTbWuOe1LfFMNExEabkRkB5ehcv
โคPowerPoint Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuMcFRbsZO5wkXgYjBG4meRM
โค Excel Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuNjHDGgNeuGrGwOvh4JQC90
โค Excel Tips & Tricks: https://www.youtube.com/playlist?list=PLb80efKTbWuPI7fEWtroXHvJHhx5FnVMW
โค Word Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuMotoYEXBFMlB3PtsBlFn45
Hyperlink formula used in this video (replace "Index" with your own sheet name):
=HYPERLINK("#'"&[@Index]&"'!A1",[@Index])
Follow along with the Excel sheet used in this video tutorial: https://drive.google.com/drive/folders/1tZsrkCtdUHkvFZ5vfYpoBFTPefY1F-sZ?usp=sharing
TIMESTAMPS โฐ
00:00 Create Table of Contents in Excel
00:07 Pause OneDrive Sync
01:09 Create List of Excel Sheet Tabs in Power Query
02:30 How to Format Table in Excel
02:37 Create Hyperlinks to Sheet Tabs with Formula
04:24 Create Link Back to Master Sheet Tab in Excel
05:52 Format Table of Contents in Excel
06:27 Auto Refresh List when Sheets are Added or Deleted
๐FREE COURSE: How To Create Fillable Forms in Microsoft Word - A Step-by-Step Guide
Create Fillable Forms, Surveys & Questionnaires in Microsoft Word like a Pro!
https://youtu.be/438pCPCSuG4
WATCH NEXT ๐บ Automate Report Formatting & Combine Multiple Sheets with Power Query: https://youtu.be/WvyUtNvG-H8
CHANNEL LINK ๐บ
https://www.youtube.com/@SharonSmith
Visit my Channel page on YouTube to see all my videos, playlists, community posts and more!
TEMPLATES ๐
Check out my helpful list of templates available for purchase: https://creatoriq.cc/43c51cv
Thank you for supporting my channel! ๐
FREE DOWNLOADS โฌ๏ธ
โฆ Visit https://mailchi.mp/6a0859ea0844/sharonsmithhrformulasdownload to sign-up for my e-mail list and get FREE downloads of super helpful spreadsheet formulas, dashboards and Org Chart templates for HR professionals.
CONNECT WITH ME ๐
Visit my website: https://www.sharonsmithhr.com for more information, tools and resources.
LinkedIn: https://www.linkedin.com/in/sharonsmithhr
Twitter: https://twitter.com/SharonSmithHR
Instagram: https://www.instagram.com/sharonsmithlearning/
Facebook: https://www.facebook.com/SharonSmithLearning
GEAR โ๏ธ
๐ Blue Yeti USB Microphone: https://amzn.to/2W4SbzV (Great for recording professional sounding audio for your videos!)
๐ฑ Silent Mouse: https://amzn.to/3pxpc25 (This is a really cool mouse!)
๐ฅ Screen Recording Software: https://techsmith.z6rjha.net/NZG5b
๐ Green Screen: https://amzn.to/2DnHsY2
๐ธ Camera: https://amzn.to/39KvpQA
๐ Live Stream Tool: https://amzn.to/2VFJyID (Turns your DSLR into a top notch webcam)
RESOURCES ๐
โ๏ธ JotForm: https://www.jotform.com/pricing/?utm_source=sharon-smith&utm_campaign=jf1&utm_medium=blog
๐ฅ๏ธ TechSmith Camtasia Screen Recording: https://techsmith.z6rjha.net/SharonSmithHR
๐ Wondershare PDFelement (PDF Editor): https://bit.ly/31QEkA8
๐ป PresenterMedia: https://shareasale.com/r.cfm?b=417324&u=2065055&m=43706&urllink=&afftrack=
Links included here are affiliate links. If you click on these links and make a purchase, I may earn a small commission at no additional cost to you. Thanks for supporting this channel!
SUPPORT THIS CHANNEL ๐
- Hit the "$Thanks" button on any video, or
- Donate through my PayPal link: https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=AJJ6SXERNDMYA&source=url
If you found this content helpful, please consider donating to my channel. Your donation, no matter what amount, is greatly appreciated and goes towards producing more content that enhances your productivity and elevates your skills.
You can also support my channel just by watching, liking, and sharing all my videos! Thank you so much! โค๏ธ
#exceltutorial #datamanagement #productivity #powerquery #exceltips
PLAYLISTS ๐ฅ
โค Jotform Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuO84pPbnHdTdgurmxHgZcyD
โคCreate A Letterhead in Word: https://www.youtube.com/playlist?list=PLb80efKTbWuO0RyeiYB0fEb55601yL4OT
โคCreating Fillable Forms: https://www.youtube.com/watch?v=YktZ3B-tyqs&list=PLb80efKTbWuOe1LfFMNExEabkRkB5ehcv
โคPowerPoint Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuMcFRbsZO5wkXgYjBG4meRM
โค Excel Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuNjHDGgNeuGrGwOvh4JQC90
โค Excel Tips & Tricks: https://www.youtube.com/playlist?list=PLb80efKTbWuPI7fEWtroXHvJHhx5FnVMW
โค Word Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuMotoYEXBFMlB3PtsBlFn45
Tags and Topics
Browse our collection to discover more content in these categories.
Video Information
Views
44.3K
Likes
590
Duration
8:01
Published
Jan 24, 2024
User Reviews
4.5
(8) Related Trending Topics
LIVE TRENDSRelated trending topics. Click any trend to explore more videos.
Trending Now