List All Sheet Names in a Workbook

Excel Formulas › Lookup

All versionsNamed formulaTEXTAFTER

There’s no plain function for “list every tab,” but a small named formula using the legacy GET.WORKBOOK macro function does it — handy for a table of contents or a navigation index.

Quick formula: create a named range (Formulas → Name Manager → New) called Sheets:
=GET.WORKBOOK(1) & T(NOW())
Then list them with =TEXTAFTER(INDEX(Sheets, ROW()), "]") filled down — one tab name per row.

Functions used (tap for the full reference guide):

TEXTAFTER

How it works

The named formula returns every tab; a TEXTAFTER strips the workbook prefix.

A
1Sheet names
2Summary
3Jan
4Feb

The formula

List them down a column (after defining the Sheets name):

=TEXTAFTER(INDEX(Sheets, ROW(A1)), "]") // one tab name per row, filled down

How it works

The setup is two steps:

  1. In Name Manager → New, create a name like Sheets that refers to =GET.WORKBOOK(1)&T(NOW()). GET.WORKBOOK(1) returns the array of [Book]Sheet names; the T(NOW()) just keeps it refreshing.
  2. Save the file as macro-enabled (.xlsm) — GET.WORKBOOK is a legacy macro function and won’t persist in a plain .xlsx.
  3. On the sheet, =TEXTAFTER(INDEX(Sheets, ROW(A1)), "]") pulls the Nth tab name and strips the [Book.xlsx] prefix; fill it down for the full list.
  4. In Excel 365 you can spill the whole list at once with a LAMBDA/SEQUENCE wrapper around the same name.

No macros allowed? Power Query can list sheets (Get Data → From Workbook), or a short Office Script / VBA macro can write the names to a column. The GET.WORKBOOK named formula is the no-VBA-code route, but it does need an .xlsm file.

Try it: interactive demo

Live demo

Add or remove tabs; the named formula lists them all.

Variations

Spill the whole list (Excel 365)

Wrap the name with MAP/SEQUENCE to spill every tab at once.

Make each name a hyperlink

Combine with HYPERLINK for a clickable index:

=HYPERLINK("#'"&A2&"'!A1", A2)

Power Query route

Get Data → From Workbook lists sheets with no macros.

Pitfalls & errors

Needs a macro-enabled file. GET.WORKBOOK only persists in .xlsm; a plain .xlsx drops the named formula on save.

It’s volatile. The list refreshes constantly (hence the T(NOW())). Fine for a small index, not for huge models.

Security prompts. Macro functions can trigger trust warnings; some locked-down environments block them entirely — use Power Query there.

Practice workbook

📊
Download the free List All Sheet Names in a Workbook practice workbook
The GET.WORKBOOK named-formula approach and TEXTAFTER list (results shown), the hyperlink-index and Power Query notes, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I list all sheet names in Excel?
Create a named formula (Sheets = GET.WORKBOOK(1)&T(NOW())), save as .xlsm, then use =TEXTAFTER(INDEX(Sheets, ROW(A1)), "]") filled down. Power Query or a macro can also list sheets.
Why does my sheet-list formula disappear when I save?
GET.WORKBOOK is a legacy macro function that only persists in a macro-enabled workbook. Save the file as .xlsm to keep the named formula.
How do I list sheets without macros?
Use Power Query (Get Data > From Workbook), which lists the sheets, or a short Office Script/VBA macro that writes the names into a column.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Get the current sheet name · Build clickable links (HYPERLINK) · Build references with INDIRECT

Function references: TEXTAFTER