Create an Index Sheet with Links to All Sheets in Excel | DAXified

📑 Create an Index Sheet with Links to All Sheets in Excel

Master advanced Excel navigation techniques with DAXified

💡 Professional Tip: In Internal Audit and data analysis workflows, having quick navigation between multiple sheets can save hours of work time. This technique is particularly valuable when managing complex audit workbooks with dozens of worksheets.

Many times we need an index page in Excel — a single sheet where all other sheet names are listed with links. Instead of scrolling across tabs, you just click and jump directly to the sheet you want.

Here are two powerful ways to build this navigation system:

1Extracting All Sheet Names with GET.WORKBOOK

🔍 Why is GET.WORKBOOK “Hidden”?
GET.WORKBOOK is a legacy function from Excel 4.0 macro language (XLM). When Microsoft introduced VBA (Visual Basic for Applications) as the primary automation tool, these XLM functions were kept for backwards compatibility but hidden from the standard function library. They’re not available through the normal function dialog but still work when typed directly into formulas.

💡 Related Hidden Function: Like GET.WORKBOOK, there’s another powerful hidden function called GET.CELL that can detect cell colors and formatting in Excel – another game-changer for advanced Excel users!

Excel has an old but still powerful hidden function: GET.WORKBOOK. It returns the names of all sheets in a workbook.

1Define a Name

Go to Formulas → Name Manager → New
Set the Name to: SheetList
In Refers to, paste:

=GET.WORKBOOK(1)&T(NOW())

Adding &T(NOW()) makes the name volatile so it refreshes after recalculation.

2Spill the Sheet Names

In any blank cell, enter:

=LET(

   L, SheetList,

   N, INDEX(TEXTAFTER(L, "]"), SEQUENCE(COUNTA(L))),

   N

)

This will spill all sheet names vertically.

2Making the Sheet Names Clickable

Once you have the list of sheet names (say in column A, starting from A1), you can add hyperlinks.

In column B (next to each name), enter:

=HYPERLINK("#'" & A1# & "'!A1","Click here")

A1# = the spilled range containing sheet names
#'SheetName'!A1 = tells Excel to jump to cell A1 of that sheet
"Click here" = the text you see in the cell
Column A = sheet names, Column B = clickable “Click here” links

3VBA Macro Alternative

If you want Excel to generate both names and links for you in one step, a simple macro can do it.

⚠️ Important: Save your file as .xlsm (Macro-Enabled Workbook) before using VBA.

Sub CreateSheetIndexHere()

    Dim ws As Worksheet, r As Range, i As Long

    Set r = ActiveCell

   

    ' Headers

    r.Value = "Sheet Name"

    r.Offset(0, 1).Value = "Link"

   

    i = 0

    For Each ws In ThisWorkbook.Worksheets

        i = i + 1

        r.Offset(i, 0).Value = ws.Name

        r.Offset(i, 1).Hyperlinks.Add _

            Anchor:=r.Offset(i, 1), _

            Address:="", _

            SubAddress:="'" & ws.Name & "'!A1", _

            TextToDisplay:="Click here"

    Next ws

End Sub

Place your cursor in the cell where you want the index to start (e.g., A1)
Run CreateSheetIndexHere
Excel will list all sheet names in column A with clickable links in column B

💼 Internal Audit Application: This macro is particularly useful when creating audit evidence folders with standardized navigation across multiple workbook templates.

🔑 Key Takeaways

Formula-only way: GET.WORKBOOK + HYPERLINK() → dynamic and lightweight
Macro way: CreateSheetIndexHere → instant table of contents with clickable links
Best practice: Save as .xlsm if you want to keep the macro solution

👉 Try this once and you’ll never scroll through 20+ sheet tabs again!

Have questions about implementing this in your workflow? Feel free to reach out at k.casatish@daxified.co.in – I’m always happy to help fellow Excel enthusiasts optimize their processes!

🚀 Join Our Learning Community

Ready to master more Excel techniques? Connect with fellow learners and discover new tips daily!

🌐 Explore the Blog 🎥 Subscribe on YouTube 📱 Join WhatsApp Group

Master Excel Navigation: Create Clickable Index of All Worksheets (Formula + VBA)