How to create a table of contents in excel with hyperlinks

Using Power Query to make a list of sheets in an excel spreadsheet.
=HYPERLINK("#'"&[@Name]&"'!A1",[@Name])
---------------------------------------------------------------------------------
#exceltips #exceltutorials #excelformulas
SUBSCRIBE: goo.gl/c46YPs
Microsoft Office 365, Beta Channel, Version 2101
---------------------------------------------------------------------------------
IF YOU LIKED THIS VIDEO YOU MAY ALSO LIKE:
- How to make hyperlinks to multiple files in excel • How to make hyperlinks...

[@misterwill3625]

I was looking up hyperlink index in Word to see if it existed and I found this video! Excellent! I've wanted to create a table of contents in excel for years, I didn't know how to do it, and I didn't think the feature existed. Thank you so much for this video! Very clear. I'm anxious to try it.

[@JorisVochten]

Thank you so much! This was really helpful!

[@fragranceenthusiast5790]

This was f......... fantastic! I have an Excel sheet with lots of tabs for chess opening notes (ECO code classifications go from A00 to E99) on various openings for both white and black, and within each opening there are a number of sub variations. I will be able to navigate my workbooks MUCH, MUCH more easily now! A huge thank you Karina! :0)

[@quietkayoss]

This was an EXCEL-ENT tutorial!! Thank you for creating this video, it gave me a basic start to creating a table of contents. I have questions which is what made this a very good learning experience! 💖

[@maryannmiami]

Very useful! Thank you!

[@batdelgermaa]

Very helpful. Thank you. Especially for the Hyperlink function.

[@ruthramoifuila9703]

@Karina. This tutorial was clear, understandable and easy to follow. I appreciate this tutorial. My 2023 workbook finally has a TOC with links. Many thanks to you.

[@davidp1282]

Hi Karina, This was a brilliant example of how well a short training video can be, clear, simple, even for me ;-) and concise... the bit I loved the best was you described what sooo sooo many others seem to fail time again to do and that is the thought process as you are working through (what has until now eluded me) and that was the step by step explanation of each piece of the syntax in the hyperlink and what it does. I'm comfortable working with PQ but for the life of me it never occurred to me to use it in this manner, fantastically brilliant. I tell you this really put a smile on my face, if only you could see.😀 this is a poor imitation but you get the point. I've been labouring over different ways to do this for ages and I make a lot of 'Excel Workbooks' so this tidbit is invaluable. So using PQ to generate the list of sheets was a great step but the explanation of the hyperlink was the icing on the cake. I've seen it before but "no one" bothers to explain what the significance of each part of the hyperlink formula meant. The bit I've strangely never found answer to was what the # symbol did in this context, maybe I just didn't search hard enough..... so thank you, thank you, thank you for making one of my main jobs just so so much easier.. 😃😃😃 Kind regards David

[@davidp1282]

Some additional follow up observations and clarification. The clarification is that I am working on Windows 10 using MS Office 365 (on a Macbook Pro via Parallels) not that I believe the later should be of any concern but annoyingly... I cannot get it to work... how bizarre. So upon a little further testing it seems like working on a Mac even running Windows 10 via Parallels yields some 'oddities' especially when it comes to file paths etc.... who knew, not me certainly, well you alway learn something new everyday if you just look ;-) I tried a simple experiment, I used the normal, right click create "HYPERLINK" (excel 2016, my normal excel at work) but on MS Office 365 it is "LINK" When I compared the link path it was look at I was surprised, it was pointing to file:///\\Mac\Home\Documents\Workbook_Template.xlsm oooohh that's nasty. So I'm guessing somewhere in the internal workings of using the usual =HYPERLINK("#'"&[@Name]&"'!A1",[@Name] sort of gets messed up... I'll have to wait until tomorrow and test it at work to see the difference but I've no doubt that it's a good solution. Hope the insight my help anyone else who tries something similar.

[@johnhackwood1568]

Wow, great, simple idea and use of Power Query Karina!

[@_MidnightMarauder_]

Outstanding! Thank you!! 👏🏾👏🏾👏🏾

[@utubeAgape]

Awesome, thank you!

[@mohanvaze1]

Thank you so much for the excellent guidance. Best regards

[@DanielNjora]

Thank you so much for this. Good job and keep it up.

[@Dr.00Z]

thank you! Great help

[@joshuamanampiu6489]

Fantastic!

[@hm-hd2ok]

is there a way i could use cell reference to call data from cell C3 from all sheets next to the hyperlinks created in the TOC?

[@user-uc3vw4dq8o]

Fantastics

[@lianchingyap5023]

Hi Karina, one more question, in the table of contents, can I know if we can base on the name of the tab to equal to certain column in other tabs? For example: in the table of contents of your excel file, I want the insert 1 more column at the right to have the value showing at D100 of each tab. Instead of manually equal it individually, can I know if we have any formula to equal it on the spot, please? Please let me know if I cannot express myself well..... Thanks!

[@lianchingyap5023]

Hi Karina, first of all, thanks for this helpful video! Can I know when we wish to insert more tabs in this file, will this table of contents updated automatically too? For example, I need to create about 10 new tabs per month in this Excel file. How can the table of contents include the new 10 tabs in the list, please?

[@BabarJunaid]

Hi, I made the table of contents as you taught. It shows the hyperlink but not taking me to the specific sheet when I click on any name. please guide

[@sergiopereira886]

🔝🔝🔝

[@amandak.burczyk1527]

This video is so perfectly made! The info is succinct, yet clear; the cadence is spot on, and the length is perfect. However, I did run into some issues, I could not do this while in the workbook. I had to close the workbook, open a blank one, then run the Power Query. I also do not have "Remove other columns" I assume I am running a different version of Excel. I attempted using this formula and it only copied the cell within the sheet I was in.

[@bazgotbanned]

Thanks for this. It is possible to make the Contents list "dynamic" so that when you add a new sheet, it will be automatically added to the Contents sheet?

[@TheSquirrelMommy]

I am wondering if, when adding a new tab, it will appear in the existing table of contents, or if this process needs to be repeated whenever a new tab is added?

[@SirLukis]

I try doing this but i get the message "The Process Cannot Access The File Used by Another Process". Could that be due to the file existing in a cloud service? I followed the steps exactly, so if the cloud isn't the issue im not sure what would be. help!

[@kathiesandburg8339]

I got this message also.

[@bazgotbanned]

@@kathiesandburg8339 Did you find a workaround?

[@carlmazziotti221]

Same issue here. Frustrating

[@prairieln]

Can anyone advise how to enable "Get Data" button under Data? I can't find it in my Macbook Pro. I'm using Office Home & Student 2021. Thank you.

[@alexohare1610]

how do you update the data/table, having addedd new worksheets to the workbook, without having to start the process again?

[@kathiesandburg8339]

Hi Karina Can not get the hyperlink to work, keep getting an error.

[@trishamarierobin8620]

Thankyou for this, however i encountered a problem saying it is being used in another process can you help me fix this please

[@javedhossain2961]

Hi Karina. Thanks for sharing this. Is this process automated? if I change the tables, will the table of contents update automatically?

[@RinkOuTime]

But what happens if you add new sheets or delete them. Does it auto change?

[@Dressesndeadlifts8290]

it does not. I just tested it. Might have to re-run the query or make a larger source selection. I am looking for answers too.

[@DanielNjora]

I was almost asking the same.

[@user-yb1zo4gb5e]

What happens if another sheet is added, how to auto update the table of contents?

[@ziruntube]

This is quite useful! But does anyone know how to keep the table of content active when you export the complete document to PDF?

[@gauripawarmagar2043]

ONE CAN MAKE THE TOC IN A BREEZE...THANKS DEAR:-)