Download Article
Download Article
If your Excel workbook contains numerous worksheets, you can add a table of contents that indexes all of your sheets with clickable hyperlinks. This tutorial will teach you how to make an index of sheet names with page numbers in your Excel workbook without complicated VBA scripting, and how to add helpful "back to index" buttons to each sheet to improve navigation.
Steps
-
Create an index sheet in your workbook. This sheet can be anywhere in your workbook, but you'll usually want to place the tab at the beginning like a traditional table of contents.
- To create a new sheet, click the + at the bottom of the active worksheet. Then, right-click the new tab, select Rename , and type a name for your sheet like Index or Worksheets .
- You can rearrange sheets by dragging their tabs left or right at the bottom of your workbook.
-
Type Page Number into cell A1 of your index sheet. Column A is where you'll be placing the page numbers for each sheet.Advertisement
-
Type Sheet Name into cell B1 of your index sheet. This will be the column header above your list of worksheets.
-
Type Link into cell C1 of your index sheet. This is the column header that will appear above hyperlinks to each worksheet.
-
Type SheetList into the "Name" field. This names the formula you'll be using with the INDEX function. [3] X Research source
-
Type the formula into the "Refers to" field and click OK . The formula is =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") . [4] X Research source
-
Enter page numbers in column A. This is the only part you'll have to do manually. For example, if your workbook has 20 pages, you'll type 1 into A2, 2 into A3, etc., and continue numbering down until you've entered all 20 page numbers.
- To quickly populate the page numbers, type the first two page numbers into A2 and A3, click A3 to select it, and then drag the square at A3's bottom-right corner down until you've reached the number of pages in your workbook. Then, click the small icon with a + that appears at the bottom-right corner of the column and select Fill Series .
-
Type this formula into cell B2 of your index sheet. The formula is =INDEX(SheetList,A2) . When you press Enter or Return , you'll see the name of the first sheet in your workbook.
-
Fill the rest of column B with the formula. To do this, just click B2 to select it, and then double-click the square at its bottom-right corner. This adds the name of each worksheet corresponding to the page numbers you typed into column A.
-
Type this formula into C2 of your worksheet. The formula is =HYPERLINK("#'"&B2&"'!A1","Go to Sheet") . When you press Enter or Return , you'll see a hyperlink to the first page in your index called "Go to Sheet."
-
Fill the rest of column C with the formula. To do this, click C2 to select it, and then double-click the square at its bottom-right corner. Now each sheet in your workbook has a clickable hyperlink that takes you right to that page.
-
Save your workbook in the macro-enabled format. Because you created a named range, you'll need to save your workbook in this format. [5] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source Here's how:
- Go to File > Save .
- On the pop-up message that warns you about saving a macro-free workbook, click No .
- In the "Save as type" or file format menu, select Excel Macro-Enabled Workbook (*.xlsm) and click Save .
Advertisement
-
Click your index or table of contents sheet. If you have a lot of pages in your workbook, it'll be helpful to readers to add quick "Back to Index" or "Back to Table of Contents" links to each sheet so they don't have to scroll through lots of worksheet tabs after clicking to that page. Start by opening your index sheet.
-
Name the index. To do this, just click the field directly above cell A1, type Index , and then press Enter or Return .
- Don't worry if the field already contains a cell address.
-
Click any of the sheets in your workbook. Now you'll create your back button. Once you create a back button on one sheet, you can just copy and paste it onto other sheets.
-
Click the Insert tab. It's at the top of the screen. [6] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click the Illustrations menu and select Shapes . This option will be in the upper-left area of Excel. [7] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click a shape for your button. For example, if you want to create a back-arrow icon sort of like your web browser's back button, you can click the left-pointing arrow under the "Block Arrows" header.
-
Click the location where you want to place the button. Once you click, the shape will appear. If you want, you can change the color and look using the options at the top, and/or resize the shape by dragging any of its corners. [8] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Type some text onto the shape. The text you type should be something like "Back to Index." You can double-click the shape to place the cursor and start typing right onto the actual shape
- You might need to drag the corner of the shape to resize it so the text fits.
- To place a text box on or near the shape before typing, just click the Shape Format menu at the top (while the shape is selected), click Text Box in the toolbar, and then click and drag a text box.
- You can stylize the text using the options in Text on the toolbar while the shape is selected.
-
Right-click the shape and select Link . This opens the Insert Hyperlink dialog. [9] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click the Place in This Document icon. It's in the left panel.
-
Select your index under "Defined Names" and click OK . You might have to click the + next to the column header to see the Index option. This makes the text in the shape a clickable hyperlink that takes you right to the index.
-
Copy and paste the hyperlink to other sheets. To do this, just right-click the shape and select Copy . Then, you can paste it onto any other page by right-clicking the desired location and selecting the first icon under "Paste Options" (the one that says "Use Destination Theme" when you hover the mouse over it). [10] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
Advertisement
Expert Q&A
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Video
Tips
Submit a Tip
All tip submissions are carefully reviewed before being published
Thanks for submitting a tip for review!
References
- ↑ https://www.journalofaccountancy.com/issues/2018/nov/create-automated-list-of-excel-worksheet-names.html
- ↑ https://www.journalofaccountancy.com/issues/2018/nov/create-automated-list-of-excel-worksheet-names.html
- ↑ https://www.journalofaccountancy.com/issues/2018/nov/create-automated-list-of-excel-worksheet-names.html
- ↑ https://www.journalofaccountancy.com/issues/2018/nov/create-automated-list-of-excel-worksheet-names.html
- ↑ https://support.microsoft.com/en-us/office/save-a-macro-24a026ef-3145-4bf8-a5f2-2fc7889ff74a
- ↑ https://support.microsoft.com/en-us/office/add-shapes-0e492bb4-3f91-43b5-803f-dd0998e0eb89
- ↑ https://support.microsoft.com/en-us/office/add-shapes-0e492bb4-3f91-43b5-803f-dd0998e0eb89
- ↑ https://support.microsoft.com/en-us/office/work-with-links-in-excel-7fc80d8d-68f9-482f-ab01-584c44d72b3e
- ↑ https://support.microsoft.com/en-us/office/work-with-links-in-excel-7fc80d8d-68f9-482f-ab01-584c44d72b3e
About This Article
Article Summary
X
To create a table of contents in Excel, you can use the "Defined Name" option to create a formula that indexes all sheet names on a single page. Then, you can use the INDEX function to list the sheet names, as well as the HYPERLINK function to create quick links to each sheet.
Did this summary help you?
Thanks to all authors for creating a page that has been read 113,316 times.
Advertisement
