Best Microsoft Excel Bloggers

Friday, March 19, 2010

Table of Contents is not just for Word!

Table of Contents

When you think of Table of Contents, you immediately think of  Microsoft Word but it can also be useful in Excel. I think I am showing my age with this one but I do believe that Excel 4 or Excel 5 allowed you to create a Table of Contents in a workbook. Well, we are up to Excel 7 ...and soon to be 10 if you can believe it and while it is not a feature you can still have a Table of Contents - you just have to create it. It is very easy as you can use hyperlinks.

Why a TOC ?A Table of Contents allows you to easily organize and pull your data together even if it is all over the place. It can be useful for yourself or if you share files with others. It is also a very useful way to navigate around if you are doing a presentation.  Additionally, it is a great way to find files that are linked externally or for that matter hidden worksheets.

In this example, I have a sheet that contains all my input or analysis, another with charts and then a sheet with other data I want to present.

I created a new sheet called TOC and this is where I want to put all my hyperlinks.
To Insert a Hyperlink within the document, click on a cell where you want the hyperlink to display
Click Insert>Hyperlink

In the first column called Link To:
Select Place in this Document

Select the sheet you want to link to by clicking on it and then type the cell reference in. (You need to know the cell you want to link to ahead of time)
In Text to display, type in a name to display in the cell otherwise the default is that it will display the sheet name and cell reference.
Click OK.
Yes, it is that easy. In the Hyperlink dialog box, you can also link to an existing file, existing webpage or even create a new document to link to.

Below is a picture of a TOC that I created.
  Notice that I even have a link to a PowerPoint presentation.
Hyperlinking is quick and efficient.

Just a word of caution, if you hyperlink to files on your computer or network and then do a presentation elsewhere - make sure the links work. Generally, you need have all the files with you if they are not hyperlinked to the Internet.

I hid the gridlines on TOC so that it would look better when I displayed it for an online presentation
Go to the View tab and in the Show/Hide Group, remove the checkmark from gridlines.

Have a great weekend. I am so glad it is Friday!

1 comment:

  1. You can even hyperlink to a subroutine in a module, if you use the HYPERLINK worksheet fucntion.

    This example assumes the name of the routine is in cell A2 and the description in cell B2:



Ms. Excel- Resident Excel Geek