How to Create a Table of Contents with Hyperlinks in Excel
HTML-код
- Опубликовано: 9 июн 2024
- 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: drive.google.com/drive/folder...
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
COURSE 🎒
Create Fillable Forms, Surveys & Questionnaires in Microsoft Word like a Pro: • Creating Fillable Form...
WATCH NEXT 📺 Automate Report Formatting & Combine Multiple Sheets with Power Query: • Automate Data Clean-up...
CHANNEL LINK 📺
/ @sharonsmith
Visit my Channel page on RUclips to see all my videos, playlists, community posts and more!
TEMPLATES 📄
Check out my helpful list of templates available for purchase: creatoriq.cc/43c51cv
Thank you for supporting my channel! 🌟
FREE DOWNLOADS ⬇️
✦ Visit mailchi.mp/6a0859ea0844/sharo... 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: www.sharonsmithhr.com for more information, tools and resources.
LinkedIn: / sharonsmithhr
Twitter: / sharonsmithhr
Instagram: / sharonsmithlearning
Facebook: / sharonsmithlearning
GEAR ⚙️
🎙 Blue Yeti USB Microphone: amzn.to/2W4SbzV (Great for recording professional sounding audio for your videos!)
🖱 Silent Mouse: amzn.to/3pxpc25 (This is a really cool mouse!)
🎥 Screen Recording Software: techsmith.z6rjha.net/NZG5b
📗 Green Screen: amzn.to/2DnHsY2
📸 Camera: amzn.to/39KvpQA
🔌 Live Stream Tool: amzn.to/2VFJyID (Turns your DSLR into a top notch webcam)
RESOURCES 📚
✏️ JotForm: www.jotform.com/pricing/?utm_...
🖥️ TechSmith Camtasia Screen Recording: techsmith.z6rjha.net/SharonSm...
📑 Wondershare PDFelement (PDF Editor): bit.ly/31QEkA8
💻 PresenterMedia: shareasale.com/r.cfm?b=417324...
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: www.paypal.com/cgi-bin/webscr...
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: • Jotform Tutorials
➤Create A Letterhead in Word: • Create A Letterhead in...
➤Creating Fillable Forms: • How To Create A Fillab...
➤PowerPoint Tutorials: • PowerPoint Tutorials
➤ Excel Tutorials: • Microsoft Excel Tutorials
➤ Excel Tips & Tricks: • Excel Tips & Tricks
➤ Word Tutorials: • Microsoft Word Tutorials
If you want to follow along with this video, you can access the worksheet I created in this video as an example for free. No need to share your email to access this file: drive.google.com/drive/folders/1tZsrkCtdUHkvFZ5vfYpoBFTPefY1F-sZ?usp=sharing Thanks for watching!
I always download your videos to improve my work.
That is great to hear! Thanks for supporting my Channel!
I absolutely love your videos🙏🏽
I'm so glad you find my videos helpful! Thanks for watching!
Hello Sharon! Your Excel videos are very helpful! I use Excel to manage my invoices that I send to clients. I keep a summary page where I put the job name, date, and total amount charged from each invoice. Is there an easy way to pull this data from all my sheets? I work in the same document and create a new tab for each invoice. As of now I'm doing it all manually and it's a bit of a pain.
Nice
Thanks so much for watching!
Perhaps it's my relative newness to digging into these sort of topics in Excel or my lack of understanding how Onedrive works, but can you say a little more as to why one has to pause OneDrive auto-synch and turn off auto save to perform this function? Thanks...
Hello can you tell me how i can make a table of contents for only 1 tab in exel? So the table needs to refer to the start of a chapter on a row. Depending on adding new information the location of the row can/will change regularly.
Hello how are you Sharron good evening I just want to tell u today I hv achieved 10 years long service award.
Congratulations! I'm so happy for you. 🙌 Thanks for watching my videos!