Skip to content
Home ยป Excel Table of Contents for Sheet Tabs with Hyperlinks | Create Automatically | Office Script Macro

Excel Table of Contents for Sheet Tabs with Hyperlinks | Create Automatically | Office Script Macro

    In this Microsoft Excel video tutorial I demonstrate how to use an Office Script to automatically create a table of contents including hyperlinks for each worksheet tab.

    Download the featured file here.

    Here is the Office Script code:

    function main(workbook: ExcelScript.Workbook) {
        //If TOC already exists, delete it
        if (workbook.getWorksheet("Table of Contents")) {
            let oldTOC = workbook.getWorksheet("Table of Contents")
        //Insert a TOC worksheet as the first worksheet in the workbook
        let tocWsheet = workbook.addWorksheet();
        tocWsheet.setName("Table of Contents");
        //Enter a title for the TOC worksheet and format it
        tocWsheet.getRange("A1").setValue("Table of Contents");
        //Enter column headings for the TOC
        let tocRange = tocWsheet.getRange("A2:B2")
        tocRange.setValues([["Sheet No.", "Sheet Name"]]);
        //Calculate the size of the TOC range (based on the number of sheets)
        let worksheets = workbook.getWorksheets();
        tocRange = tocRange.getResizedRange(worksheets.length, 0);
        //Loop through every worksheet except the TOC worksheet
        for (let tocEntry = 1; tocEntry < worksheets.length; tocEntry++) {
            //Enter the position of the worksheet
            tocRange.getCell(tocEntry, 0).setValue(tocEntry);
            //Create a hyperlink to the worksheet
            tocRange.getCell(tocEntry, 1).setHyperlink({
                textToDisplay: worksheets[tocEntry].getName(),
                documentReference: `'${worksheets[tocEntry].getName()}'!A1`
        //Loop through each worksheet and remove existing 'Back to TOC' hyperlinks
        // Loop over each worksheet
        for (let sheet of worksheets) {
            // Get the used range
            let usedRange = sheet.getUsedRange();
            if (!usedRange) {
                continue; // If no used range, skip to next sheet
            // Get the values of the used range
            let values = usedRange.getValues();
            // Go through each cell in the used range
            for (let row = 0; row < values.length; row++) {
                for (let col = 0; col < values[row].length; col++) {
                    if (values[row][col] === "Back to TOC") {
                        // Clear the cell if it matches "Back to TOC"
                        usedRange.getCell(row, col).clear(ExcelScript.ClearApplyTo.contents);
        //Loop through each worksheet and add 'Back to TOC' hyperlink
        for (let tocNav = 1; tocNav < worksheets.length; tocNav++) {
            //Find the column to the right of the last used column
            let usedrange = worksheets[tocNav].getUsedRange()
            let lastcol: number = usedrange.getLastColumn().getColumnIndex()
            //Add the hyperlink 2 columns to the right of the last used column
            worksheets[tocNav].getCell(0, lastcol + 2).setHyperlink({
                textToDisplay: "Back to TOC",
                documentReference: `'${worksheets[0].getName()}'!A1`
        //Activate the TOC worksheet
        //Autofit column B
        //Turn gridlines off