How to create a table of contents in excel with hyperlinks

Using Power Query to make a list of sheets in an excel spreadsheet.
---------------------------------------------------------------------------------
#exceltips #exceltutorials #excelformulas

Microsoft Office 365, Beta Channel, Version 2101
---------------------------------------------------------------------------------
IF YOU LIKED THIS VIDEO YOU MAY ALSO LIKE:

[JorisVochten]

Thank you so much! This was really helpful!

[misterwill]

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.

[ruthramoifuila]

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

[johnhackwood]

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

[fragranceenthusiast]

This was 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! 💖

[batdelgermaa]

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

[mohanvaze]

Thank you so much for the excellent guidance. Best regards

[davidp]

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 so thank you, thank you, thank you for making one of my main jobs just so so much easier.. 😃😃😃
Kind regards
David

[DanielNjora]

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

[hm-hdok]

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?

[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

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

[lianchingyap]

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 Thanks!

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

[alexohare]

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

[lianchingyap]

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?

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

[amandak.burczyk]

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.

[kathiesandburg]

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