How to create Table of Contents in Excel

The Problem

How often have you received complex Excel files from others which have many worksheets, some color coded (but you don’t know the meaning of the color), some having similar names …lots of data, calculations, pivots, charts … and then you face a simple problem. Where do I start? Which areas am I supposed to view? The first sheet which opens is the sheet and the area of the sheet which was open at the time of last file save. Which does not necessarily mean that is the area of primary interest. It is quite confusing.

The Risk

It is not just confusing. It is dangerous. The sender may have intended you to look at a particular sheet / range but in reality, you end up looking at some other area – and take some decision. This is a communication gap – which is difficult to bridge. This can lead to misinterpretation, erroneous decision making and chaos.

The solution: Create a Table of Contents for complex Excel workbooks

The problem: Excel does not provide a built-in feature to create a TOC. But that does not constrain us. As we saw in the last article, we can use a Hyperlink within a document to create a set of links. By default, Excel allows you to create a hyperlink to existing sheets. In practice, sheet based navigation may not be enough – because, the same worksheet may have multiple areas of interest. Therefore, Excel allows us to create other named items which can be used as hyperlink targets. You can create Range Names.

How to define range names?

Select any range in a worksheet. Go to the name box, type a name and press ENTER. No spaces allowed in the name. Now these names appear in Hyperlink Dialog. You can also create and edit names using Formulas tab – Name Manager.

Creating a TOC in Excel

Here are the steps:

1. Rename each sheet – never keep the defaults to Sheet1, Sheet2 and so on
2. Delete empty sheets
3. Identify ranges which are important for creating the TOC and assign names to them.
4. Create a new sheet called Contents
5. Create the TOC by typing relevant text
6. For each TOC item, insert a hyperlink either to a sheet or a defined name
7. Just before saving and closing the file keep the content sheet active
8. Close the file

Now everyone can use the TOC we just created to browse the file in an informed manner.

Creating a “Back” link

In order to complete the navigation, it is a good idea to have a BACK link at each of the hyperlinks. This is easy. Type Back in a cell just above the named range and insert a Hyperlink to the Contents sheet. Copy this cell to all hyperlink targets.

Remember to update the TOC

Whenever you add or delete more data or sheets, consider if you need to edit the Contents sheet the reflect the changes.