Table of Contents for Long Excel Sheet

iconhyperlink 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

tableofcontentssheet01

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

tableofcontentssheet02

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.

tableofcontentssheet03

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.

tableofcontentssheet04

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

tableofcontentssheet05

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

tableofcontentssheet07

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

tableofcontentssheet06

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

tableofcontentssheet08

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.

tableofcontentssheet09

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

___________

You may also like...

5 Responses

  1. Adam Hartman says:

    Always, ALWAYS include a “Back to Table of Contents” button (similar to this post, but linked back to the TOC sheet) that stays at the top of each linked page. People aren’t always used to “surfing” excel workbooks, and the “Back” button helps them to get back to familiar territory.

  2. Excellent guidance–thanks for sharing! (And I’m still trying to convince people to use navigation in Word docs. ;-) )

  3. Kevin says:

    If you save as a “.pdf” does the hyperlinks follow. The answer is no. Yet if you created the same situation in Powerpoint the answer would be yes.

    Microsoft is quirky that way.

  4. drew says:

    How do you get around this if the location in the sheet will change. Because the data i am feeding into the sheet can vary in size, the location for each section can vary as well? Thanks

  5. Eric says:

    Hi, excellent post!!
    I noticed that the hyperlink function build the link only once, at the entry of the formula.
    For example you enter in a1 a URL and use a1 in hyperlink function, you press enter. Now you change the value of a1 the hyperlink of the cell where the hyperlink function is, keeps the old value of a1 :-(
    Do you have the same issue?
    I try to press f9 to force the calculation, no fix.
    I search internet, never found a note on this limitation.
    Any input will be welcome :-)
    Eric

Leave a Reply to Jimmy May @aspiringgeek Cancel reply

Your email address will not be published. Required fields are marked *