How to list all sheet names in Excel

1Just look: the Activate dialog

Right-click the sheet scroll arrows at the bottom left of the window, to the left of the first tab. Excel lists every visible sheet in a dialog; double-click one to go there. Nothing is written to the workbook. Hidden sheets are left out, and you cannot copy the list.

2Names in cells: a GET.WORKBOOK formula

GET.WORKBOOK is an old Excel 4.0 macro function that still works inside a defined name. It returns the workbook’s sheet names.

1. Go to Formulas › Name Manager › New.
2. Name: SheetNames. Refers to:

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

   The T(NOW()) part adds nothing to the text; it just makes the name recalculate so the list stays current.
3. In Microsoft 365 or Excel 2021 and later, one formula spills the whole list:

   =TEXTAFTER(TRANSPOSE(SheetNames), "]")

   TEXTAFTER needs Microsoft 365 or Excel 2024. In Excel 2021, use =MID(TRANSPOSE(SheetNames), FIND("]", TRANSPOSE(SheetNames)) + 1, 255).
4. In older versions, put this in a cell and fill down until it goes blank:

   =IFERROR(MID(INDEX(SheetNames, ROWS($1:1)), FIND("]", INDEX(SheetNames, ROWS($1:1))) + 1, 255), "")

Each raw entry looks like [Budget.xlsx]Summary, which is why the formulas cut everything up to the ]. Because it is a macro function, the workbook has to be saved as .xlsm or .xlsb to keep working, and strict macro security settings can stop it calculating.

3Names in cells without macros: Power Query

Power Query can read a workbook’s structure, including whether each sheet is hidden, and it needs no macros. It reads the file as last saved, so save first.

1. Data › Get Data › From File › From Excel Workbook, and pick the workbook itself.
2. In the Navigator, select any sheet and choose Transform Data.
3. In Applied Steps on the right, delete every step after Source. You now see one row per sheet, table and defined name.
4. Filter the Kind column to Sheet, then keep just the Name and Hidden columns (right-click › Remove Other Columns).
5. Close & Load. Use Data › Refresh All after adding or renaming sheets.

4A linked index sheet: a VBA macro

This macro adds a sheet called Sheet index at the front of the active workbook, listing every sheet with its type and visibility — including very hidden ones — and making each visible worksheet’s name a link.

1. Press Alt+F11, then Insert › Module, and paste:

Sub BuildSheetIndex()
    Dim book As Workbook, indexSheet As Worksheet, sh As Object, r As Long
    Set book = ActiveWorkbook

    For Each sh In book.Sheets
        If sh.Name = "Sheet index" Then
            MsgBox "Rename or delete the existing 'Sheet index' sheet first."
            Exit Sub
        End If
    Next sh

    Set indexSheet = book.Worksheets.Add(Before:=book.Sheets(1))
    indexSheet.Name = "Sheet index"
    indexSheet.Range("A1:C1").Value = Array("Sheet", "Type", "Visibility")

    r = 1
    For Each sh In book.Sheets
        If Not sh Is indexSheet Then
            r = r + 1
            indexSheet.Cells(r, 1).Value = sh.Name
            indexSheet.Cells(r, 2).Value = TypeName(sh)
            Select Case sh.Visible
                Case xlSheetVisible: indexSheet.Cells(r, 3).Value = "Visible"
                Case xlSheetHidden: indexSheet.Cells(r, 3).Value = "Hidden"
                Case xlSheetVeryHidden: indexSheet.Cells(r, 3).Value = "Very hidden"
            End Select
            If TypeName(sh) = "Worksheet" And sh.Visible = xlSheetVisible Then
                indexSheet.Hyperlinks.Add Anchor:=indexSheet.Cells(r, 1), Address:="", _
                    SubAddress:="'" & Replace(sh.Name, "'", "''") & "'!A1", _
                    TextToDisplay:=sh.Name
            End If
        End If
    Next sh

    indexSheet.Columns("A:C").AutoFit
End Sub

2. Click back into Excel on the workbook you want indexed, press Alt+F8, pick BuildSheetIndex and Run.

To keep the index but not the macro, save as .xlsx and answer Yes when Excel says the VBA project will be removed. Run it again after the workbook changes — delete the old index first.