Create a table of contents for Excel spreadsheets in a workbook

You can create a table of contents for spreadsheets in an Excel workbook either manually or using a macro. This helps you keep track of things.

Create a table of contents for spreadsheets in an Excel workbook – manual method

Excel files with many spreadsheets can quickly become confusing. A worksheet set up as a table of contents can help. You don’t need to be able to program to do this.

• Open the document in Excel that you want to add a table of contents to. Place the mouse pointer over the first worksheet in the worksheet list. Then right-click once.

• Select “Insert” and click on “Worksheet” in the “Insert” dialog box. It is located on the “General” tab. Confirm your selection with OK. Excel has now inserted a new, empty worksheet named “Table1” into the worksheet list for you.

• Right-click on the name of the new sheet and select “Rename”. You can now overwrite the existing name. Type “Table of Contents”.

• In cell A1, write a heading, for example, Sales 2024. In cell A2, enter the names of the individual spreadsheets in cell A2 (see Figure 1 below).

• Once you have entered all the names, click in cell A2 to insert the link to the spreadsheet. First click on the “Insert” tab in the menu above.

• In the “Links” submenu (third from the right), left-click the Link icon. This opens the “Insert Link” dialog.

• In the dialog window, select the source of the link on the left. In our example, this is “Current Document” (see Figure 2 below). Click the symbol once with the left mouse button.

• In “Enter the cell reference:”, specify where the cursor should jump to when you later access the worksheet via the link. By default, Excel always targets the first cell in the first column, i.e. A1.

• From the list, select the worksheet you want to switch to when you click the mouse later. In our example: “02.07”. Confirm the selection with OK.

• Now click in cell A3 and repeat steps 6 – 9 for all worksheets in your Excel workbook. After you have inserted all the links, simply open the desired worksheet with a single click of the mouse.

Create a table of contents using an Excel macro

If you find manually creating the table of contents too laborious, you can also write a macro for it. It automatically creates a list of all worksheets and inserts hyperlinks to jump directly to the respective sheets. To do this, use the VBA developer tools.

• If the “Developer Tools” tab is not displayed in your open workbook, activate it first. To do this, click on the “File” tab, select “More…” at the bottom of the command column on the left and then “Options”. Under “Customize Ribbon”, check the box in front of “Developer Tools” and confirm with OK.

• You can now access the “Developer Tools” tab. Click on Visual Basic (first icon on the left). This opens the Visual Basic Editor.

• Paste the VBA code shown at the bottom of the image into the module. Then press Alt + Q to close VBA and return to Excel.

• The macro is named “CreateTableOfContents”. You can access it by clicking on the “Macros” icon in “Developer”. Highlight the macro name and click on “Run”.

• Now open any other worksheet you want from the new “Table of Contents” worksheet with a click of the mouse.