How to Create an Index in Excel

Last Updated: May 10, 2024 Fact Checked

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.

Part 1

Part 1 of 2:

Making the Index

Download Article

1
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.
Column A is where you'll be placing the page numbers for each sheet.

Advertisement
This will be the column header above your list of worksheets.
This is the column header that will appear above hyperlinks to each worksheet.
It's at the top of Excel. ^{[1]
X
Research source}
It's on the "Defined Names" tab at the top of Excel. ^{[2]
X
Research source}
This names the formula you'll be using with the INDEX function. ^{[3]
X
Research source}
The formula is =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") . ^{[4]
X
Research source}
9
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 .
The formula is =INDEX(SheetList,A2) . When you press Enter or Return , you'll see the name of the first sheet in your workbook.
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.
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."
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.
14
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 .

Part 2

Part 2 of 2:

Creating Hyperlinks Back to the Index

Download Article

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.
2
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.
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.
It's at the top of the screen. ^{[6]
X
Trustworthy Source
Microsoft Support
Technical support and product information from Microsoft.
Go to source}
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}
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.
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}
8
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.
This opens the Insert Hyperlink dialog. ^{[9]
X
Trustworthy Source
Microsoft Support
Technical support and product information from Microsoft.
Go to source}
It's in the left panel.
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.
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}