Create Table of Contents in Excel File (Excel 2013)

There are times where your spreadsheet will expand and have several worksheets within the same spreadsheet file. Normally, you would click the worksheet tabs (or next / previous arrow buttons) to navigate between worksheets. That’s okay, but…if you have more than 15 of them, it will be rather troublesome to sort through the list of tabs in your file just to find that particular worksheet!

A good way to navigate between multiple worksheets is to create a table of contents (TOC). However, unlike Word where you can automatically create a TOC using the built-in feature, Excel doesn’t have one. You have to build one yourself using hyperlinks. Let’s look at this example. There are 17 tabs in a spreadsheet:

create_toc_excel1
 

You need to insert a new worksheet called “Table of Contents” and start building the hyperlinks that link them back to the tabs. Here’s how:

1.  Right-click the Introduction tab and select Insert from the pop-up menu.

create_toc_excel2
 

2.  In the Insert dialog box, select Worksheet and click the OK button.

create_toc_excel3
 

3.  Double-click the new worksheet tab Sheet1, rename it as Table of Contents and hit the Enter button.

create_toc_excel4
 

4.  Give your worksheet a proper heading. For example, in cell B2, type “Table of Contents” and format it as bold with 16pt font size.

create_toc_excel5
 

5.  Let’s start building the first hyperlink to the Introduction tab. Right-click cell B4 and select Hyperlink from the pop-up menu.

create_toc_excel6
 

6.  A new Insert Hyperlink dialog box appears. Click the Place in This Document button, then select Introduction from the list.

create_toc_excel7
 

7.  Delete !A1 from Text to display text box. Then delete A1 from Type the cell reference text box. Click the OK button.

create_toc_excel8
 

8.  You have successfully created the first hyperlink. To test, click the Introduction hyperlink. It should jump straight to the Introduction worksheet.

create_toc_excel9
 

Repeat the same steps, i.e. Step 5 to 7, to continue building the hyperlinks to the remaining worksheets.

9.  Note that when you try to create the second hyperlink onwards, the apostrophe symbol ‘’ appears, surrounding the worksheet name. Delete those too (besides deleting the !A1 and A1).

create_toc_excel10
 

10.  Your final TOC will look like this:

create_toc_excel11

Now that you have completed building the TOC in the first worksheet tab, you should also create a link back to the TOC for each of the worksheet. For example, in the Introduction worksheet, type “<<Back” in cell P2 and right-click the cell. Select Hyperlink from the pop-up menu.

create_toc_excel12
 

11.  A new Insert Hyperlink dialog box appears. Click the Place in This Document button, then select Table of Contents from the list. Delete A1 from Type the cell reference text box. Click the OK button.

create_toc_excel13
 

12.  You have successfully created a link back to the main TOC. To test, click the <<BACK hyperlink. It should jump straight to the Table of Contents worksheet.

create_toc_excel14
 

But wait! If you have like a gazillion worksheets in the file, the above method may not seem ideal. You need to create a macro to help you automate the tasks. These two references below are excellent sources to teach you how to do so:

http://blogs.office.com/2011/01/13/add-a-table-of-contents-to-your-workbook-its-easy-i-promise/

http://sniptools.com/vault/auto-generate-a-table-of-contents-in-excel

 

About perrinator

I wear many hats - blogger, writer, freelancer, gamer, traveler, baker, white-collar slave - I strive to offer valuable insights by sharing real-life examples of work-related challenges and how to address them. So, join me on this journey of learning and growth together!

View all posts by perrinator →

Leave a Reply

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