If like me, you create a substantial amount of worksheets in the the one workbook, for certain tasks then a good way to
# 1 help users of the spreadsheet to to navigate your workbook
# 2 give your workbook that professional edge
is to create a Table Of Contents (TOC) on the first worksheet in your Excel workbook.
There is a really quick way to do this in Excel to save you time. Lets get rolling with this great tip… in my example I have renamed my first worksheet as Table Of Contents (TOC), as this is where I am going to put my TOC that will contain the hyperlinks to my other areas of my workbook1. Save your workbook if it has not already been saved. This tip only works with a saved workbook, so if you have just opened a new workbook, and not saved it this won’t work.
- SAVE YOUR WORKBOOK.
- Select the cell you want to LINK TO.
- Point to the cell border, and right click with the mouse.
- Press the ALT key and and drag the cells to the TOC worksheet.
- Once the sheet has been activated you can then drag the cells to position you want the hyperlink to be on the TOC worksheet.
- Release the ALT key and when the pop up menu appears, select “Create Hyperlink Here”
7. The Hyperlink will appear for you with the original cell text.
There it is the quickest way to create a table of contents in Excel.
Let me know if you like this tip.
Do you use table of contents often?.
Other Excel Tip You Might Like.
1. Centre A Title Across An Excel Worksheet.
3. Change The Standard Font In Your Excel Workbooks
Want Even More Top Excel Tips?
A How To Excel At Excel Recommended Download.
Paul says
Thanks Barbara,
Works a treat after I did it right (fourth attempt) 🙂
I will find this very useful when I am creating a spreadsheet with more than 15 tabs.
Much appreciated.
Paul
Barbara says
That’s great Paul
Glad it worked for you
Regards
Barbara
Excel User says
Magical! Some other posts online give much more cumbersome way to do this.
Thank you very much.
William says
I have created an Index page for a 12 sheet workbook with hyperlinks to each sheet. However when i click the back yo index cell I get a popup that reads “Reference is Not Valid”. Any suggestions?
William says
Barbara,
Have figured it out, Quite simple really. Thanks anyway.
Harry says
Great tip thanks but I can’t use right now. I’ll be able to use this when I find a way to make the TOC (table of contents) entry follow the cells as new rows & columns are added &/or deleted. Any clues on how to do that? (Currently the TOC does not follow the cell when it’s moved.)
Barbara says
Hi Harry
You could name the cell/s as a named range, then reference the named range rather than the cell. I have used this method in the past,
Thanks and regards
Barbara
Sophie says
Dear Barbara,
Thanks for sharing this! It works really nicely. However, I cannot get the hyperlink text to update, reflecting changes to the cell where it refers to. Do you know a way to do this?
Kind regards,
Sophie
Barbara says
I believe a dynamic hyperlink should work see my post here
http://www.howtoexcelatexcel.com/blog/make-your-excel-hyperlinks-dynamic-formula-friday/