Table of Contents for Long Excel Sheet

From http://blog.contextures.com/

Table of Contents for Long Excel Sheet:

In the comments for my post on creating a table of contents in Excel, Eden asked:

“Can I create a content page within one worksheet? I have one worksheet and it is very long.”

Good idea! A short table of contents at the top of a worksheet would make it easy to find specific sections on a long worksheet. Here are the steps for setting that up.

Create the Headings List

The first step is to copy all the headings to the top of the worksheet, where they’ll be used for the table of contents.

1. Insert blank rows at the top of your worksheet, to make room for the table of contents.


2. Click on the first heading, to select it.


3. Press the Ctrl key, and click on each of the remaining headings, to select all of them.


4. Click the Copy command on the Excel Ribbon


5. Right-click in an empty cell at the top of the worksheet, and under Paste Options, click Paste as Values

That creates a list of headings at the top of the sheet.

Name the Heading Cells

Next, follow these steps, to create a named range for each heading cell.

1. In the body of the worksheet, select the cell with the first heading.


2. To name that cell, click in the name box, to the left of the Formula Bar.


3. Type a short one-word name for that range, and press Enter.


4. Repeat these steps, to name each of the remaining headings.

In this example, the first heading cell is named Income.

Add Links in the Table of Contents

The final step is to link the headings to the table of contents, by following these steps.

1. At the top of the worksheet, select the cell with the first heading that you’re going to link.


2. On the Excel Ribbon’s Insert tab, click Hyperlink


3. Under Link To, click on Place in This Document


4. Under Defined Names, click on that heading name – Income in this example.


5. (Optional) Click Screen Tip, and type the heading name, then click OK


6. Click OK, to close the Insert Hyperlink dialog box.

Now you have a list at the top of the worksheet that’s linked to each of the sections below.

Link to the Links

To make navigation even easier, you could put a link near each of the subheadings, to take you back to the table of contents.

• First, name a cell at the top of the worksheet. In the screen shot below, the main heading cell is named TopSummary

• Next, add a shape to the worksheet, to the left of the first heading cell. I added a small arrow, and set its properties to not Print, and Move, but don’t Size with cell.

• With the shape selected, insert a Hyperlink to the named range at the top of the sheet.

Copy the shape, and paste to the left of the remaining headings.

Test the Navigation

Now your navigation system is ready to test.

1. Click on a link in the table of contents to quickly go to a section in the report.


2. Click on an arrow, to return to the top of the worksheet.

Did it work? Do you have any other ideas for navigating through a long worksheet? Please let me know in the comments!

___________