How to Create an Index or Table of Contents in Excel

Download Article

Plus, learn to use the INDEX function

Written by Megaera Lorenz, PhD

Last Updated: January 26, 2026 Fact Checked

Download Article

• Making the Index
|
• Creating Hyperlinks Back to the Index
|
• Using the INDEX Function
|
• Video

|Show more |Show less

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 in your Excel workbook using a script you can copy and paste in a few easy steps, and how to add helpful "back to index" buttons to each sheet to improve navigation. We’ll also explain the basics of using the INDEX function.

Making a Simple Index in Excel

• You can index your workbook with a hyperlinked table of contents by running a script in the Code Editor.
• Add linked buttons back to the Table of Contents to make large workbooks easier to navigate.
• Use the INDEX function to retrieve data from a specific location in your sheet, or to perform calculations using a specified range.

Steps

Section 1 of 3:

Making the Index

Download Article

1. 1
   Click Automate at the top of your workbook. This method allows you to run a quick script that will generate a table of contents for your workbook. Each entry in the TOC will link to the corresponding sheet in the workbook. To get started, open your workbook and click the Automate tab at the top of the screen.
2. 2
   Select New Script. You’ll see this option on the left side of the ribbon menu. A small menu will open.
   Advertisement
3. 3
   Click Create in Code Editor. The Code Editor panel will open on the right side of your workbook.

   • If you’ve never used the Code Editor before, you might need to click through a couple of informational messages.
   • You’ll see an example script in the window when you first open it.
4. 4
   Paste the script below into the Code Editor window. If there’s an example script in the Code Editor window, select it all and delete it. Paste the following code in its place:

   function main(workbook: ExcelScript.Workbook) {
     // Insert a new worksheet at the beginning of the workbook.
     let tocSheet = workbook.addWorksheet();
     tocSheet.setPosition(0);
     tocSheet.setName("Table of Contents");
     // Give the worksheet a title in the sheet.
     tocSheet.getRange("A1").setValue("Table of Contents");
     tocSheet.getRange("A1").getFormat().getFont().setBold(true);
     // Create the table of contents headers.
     let tocRange = tocSheet.getRange("A2:B2")
     tocRange.setValues([["#", "Name"]]);
     // Get the range for the table of contents entries.
     let worksheets = workbook.getWorksheets();
     tocRange = tocRange.getResizedRange(worksheets.length, 0);
     // Loop through all worksheets in the workbook, except the first one.
     for (let i = 1; i < worksheets.length; i++) {
       // Create a row for each worksheet with its index and linked name.
       tocRange.getCell(i, 0).setValue(i);
       tocRange.getCell(i, 1).setHyperlink({
         textToDisplay: worksheets[i].getName(),
         documentReference: `'${worksheets[i].getName()}'!A1`
       });
     };
     // Activate the table of contents worksheet.
     tocSheet.activate();
   }
   
5. 5
   Click Run. You’ll see this green button at the top of the Code Editor panel. Click it and wait for the script to run. When it’s done, you should see a message that says “The script ran successfully.”
6. 6
   Use the new index sheet to navigate your workbook. As soon as the script runs, a new sheet will appear at the front of your workbook called “Table of Contents.” Click a link in the Name column to go to the corresponding sheet.

   • If you want to make it easier to get back to the Table of Contents from any sheet in the workbook, you can add an Index link to the other sheets.
Advertisement

Section 2 of 3:

Creating Hyperlinks Back to the Index

Download Article

1. 
   1
   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 (or Table of Contents) sheet.
2. 
   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.
   • You can’t use spaces in the cell name, so you’ll have to use something like “Index” or “TableofContents.”
3. 
   3
   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.
4. 
   4
   Click the Insert tab. It's at the top of the screen.
5. 
   5
   Select Shapes. Depending on your version of Excel, you might see this in a section of the menu called Illustrations. The Shapes icon looks like a white circle overlapping a blue square.
6. 
   6
   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.
7. 
   7
   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.

   • For some kinds of shapes, like rectangles, you’ll need to click and drag your cursor to make the shape appear.
8. 
   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.
9. 
   9
   Right-click the shape and select Link or Hyperlink. This opens the Insert Hyperlink dialog.

   • If you’re using a Mac with no right mouse button, Ctrl-click the shape to open the context menu.
10. 
    10
    Click the Place in This Document icon. It's in the left panel.

    • In Excel for Mac, click the This Document tab near the top of the Insert Hyperlink dialog.
11. 
    11
    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.
12. 
    12
    Copy and paste the hyperlink to other sheets. To do this, just right-click the shape (or Ctrl-click on a Mac) 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).
Advertisement

Section 3 of 3:

Using the INDEX Function

Download Article

1. 1
   Use INDEX to retrieve the value of an element at a specific location. Excel’s INDEX function allows you to retrieve a cell reference within a specific range or array of cells. There are a variety of uses for this function, including:

   • Finding an item at a particular location in a list (e.g., the 9th item in column 2).
   • Getting all the values in a row or column.
   • Working in combination with other formulas, such as SUM, AVERAGE, MIN, or MAX. For instance, you can use the INDEX formula to retrieve all the values in a column, then use AVERAGE to calculate the average value.
   • Check out our article on how to use functions and formulas in Excel.
2. 2
   Get a value from an array or range with the INDEX array form. With this version of the formula, you can highlight a value within a specific range of cells. First, define your array or range. Then, specify a row and column number. This formula will either retrieve the value at the intersection of the row and column number, or (if you set the row and column numbers to “0”) the array of values for the entire specified column or row.

   • This version of the INDEX formula uses the syntax INDEX(array, row_num, [column_num]).
   • For the “array” argument, enter a range of cells or an array constant. If your array consists of only one row or column, the row number and column number arguments are optional.
   • For the “row_num” argument, enter the number of the row in the array from which you want to return a value.
   • For the “column_num” argument, enter the number of the column in the array from which you want to return a value.
   • If the row and column numbers you select aren’t within your chosen array, you’ll get a #REF! error.
   • For example, entering the formula =INDEX(A4:B5,2,2) will return the value of the cell at the intersection of the second row and the second column in the range from A4 to B5.
3. 3
   Use the INDEX reference formula to highlight a value from one of multiple ranges. If you have separate or non-consecutive ranges of data (“areas”) and you want to find a value at a specific location within one of those areas, use the reference version of the INDEX formula. This will return the value at the intersection of the specified row and column in the specified area.

   • The correct syntax for this form is INDEX(reference, row_num, [column_num], [area_num]).
   • The “reference” argument must contain one or more cell ranges. If they’re non-consecutive, separate the ranges with parentheses.
   • The “row number” argument specifies the row within your designated reference range(s) with the data that you want to highlight.
   • “Column number” is optional. This argument specifies the column within your reference range(s) from which you want to return a value.
   • “Area number” is also optional. It specifies the area within the worksheet from which you want to return a value.
     • All your areas need to be within the same worksheet. If they’re in separate sheets, you’ll get a “#VALUE!” error.
   • For example, =INDEX((A2:C4, A5:C15), 3, 3, 2) would retrieve the value from the intersection of the third row and the third column of the second area (A5:C15).
4. 4
   Combine INDEX with other functions to make calculations. INDEX makes it easy to perform a variety of calculations using the data specified in the formula. This includes things like sums and averages. For example:

   • The formula =SUM(INDEX(A4:C12, 0, 2, 2)) would retrieve the sum of the data in the second column of the second area of the range A4:C12. In other words, it would retrieve the sum of the data in C4:C12.
   • Similarly, you could replace “SUM” in the formula above with “AVERAGE” to get the average of the data in the same specified location.
Advertisement

Expert Q&A

Search

Add New Question

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

Name

Please provide your name and last initial

Submit

Thanks for submitting a tip for review!