How do I get a list of all sheet names in Excel?

There's no plain function for it. The classic trick is a defined name using =GET.WORKBOOK(1) plus =INDEX(name, ROW()), which needs the file saved as .xlsm. Tellsheet's Table of Contents lists every worksheet with a clickable link and no macro.

How do I make a table of contents in Excel?

Build a list of sheet names, then wrap each in =HYPERLINK("#'Sheet'!A1", "Sheet") to make it clickable. Tellsheet's Table of Contents does this automatically, adding a hyperlinked index as the first tab.

Why does my sheet-name formula need an .xlsm file?

GET.WORKBOOK is an old macro (XLM) function, so Excel treats the workbook as macro-enabled and only saves it as .xlsm/.xlsb. An add-in avoids this — Tellsheet's Table of Contents works in any workbook, including Excel on the web.

Home › Excel Guides › List All Sheet Names in Excel

How to List All Sheet Names in Excel

A 40-tab workbook with no index is a scrolling nightmare. Excel has no function to list sheet names — here's the classic macro-name trick, and a one-click clickable table of contents.

Last updated: August 2026

Workbook		Contents tab
Jan, Feb, Mar, … tabs	→	→ Jan
		→ Feb
		→ Mar

Method — the GET.WORKBOOK macro name

Formulas > Define Name. Name it Sheets, refers to =GET.WORKBOOK(1).
Down a column: =INDEX(Sheets, ROW()) to list the tab names (it returns them as [Book]Sheet, so wrap with MID/FIND to trim).
Make each clickable: =HYPERLINK("#'"&A2&"'!A1", A2).
Save as .xlsm — the macro name forces macro-enabled format.

The 1-click way — Tellsheet

Table of Contents adds a navigation sheet as the first tab, with a clickable hyperlink to every worksheet in the workbook — jump anywhere in one click. No GET.WORKBOOK, no MID/FIND trimming, no .xlsm. Works on Windows, Mac, and Excel on the web. Pairs with Split to Sheets and Combine Sheets.

Frequently asked questions

How do I list all sheet names?

A defined name using =GET.WORKBOOK(1) with =INDEX(name, ROW()) (needs .xlsm), or Tellsheet's Table of Contents in one click with no macro.

How do I make a clickable table of contents?

Wrap each name in =HYPERLINK("#'Sheet'!A1", "Sheet"), or let Table of Contents build the hyperlinked index for you.

Why does the formula need .xlsm?

GET.WORKBOOK is an old macro function, so Excel saves the file as macro-enabled. An add-in works in any workbook, including the web.

Does it update when I add a sheet?

Re-run Table of Contents to refresh the index after adding or renaming tabs.

Related Excel guides

Build a table of contents in one click

Table of Contents adds a hyperlinked index tab linking to every sheet — no macros, right inside Excel.

Get Tellsheet free See pricing