Excel Formula to List All Sheet Names in a Workbook

Formulas & functions · Updated November 19, 2026

Excel does not have a standard worksheet function that directly returns every tab name. In Excel for Microsoft 365 on Windows, you can use the older GET.WORKBOOK macro function in a workbook-level defined name, then extract the names with a cell formula: define SheetNames as =GET.WORKBOOK(1)&T(NOW()), and enter =IFERROR(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS($A$2:A2)),"") in A2 and fill it down. The setup requires desktop Excel and may be blocked by macro security; details and alternatives follow.

Set up the workbook-level name

GET.WORKBOOK is an Excel 4.0 macro function, not a regular worksheet function such as SUM or COUNTA. Excel generally requires it to be used through a defined name rather than typed directly into a cell. The name gives the worksheet formula a reference to the array of sheet identifiers returned by the macro function.

  1. Open the workbook in the Excel desktop app and choose Formulas > Name Manager > New.
  2. Enter SheetNames as the name. Set its scope to Workbook, so it can be used from any worksheet in that workbook.
  3. In Refers to, enter =GET.WORKBOOK(1)&T(NOW()), then select OK and close Name Manager.
  4. Save the workbook in a macro-enabled format, such as .xlsm, if Excel prompts you to preserve macro-related content.

The 1 argument asks GET.WORKBOOK for sheet names. The NOW portion is a common way to make the defined name volatile, so it is recalculated when Excel recalculates formulas. It does not mean the list is guaranteed to refresh at the instant a sheet is added or renamed; refresh behavior is covered below.

Extract the names into cells

For the formula below, assume the output list starts in A2 and that column A is available. Put this formula in A2, then copy it down as many rows as the workbook could have sheets:

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

GET.WORKBOOK commonly returns entries with the workbook name in square brackets before the tab name, such as [Budget.xlsx]Overview. FIND locates the closing bracket, and MID takes the characters that follow it, leaving Overview. INDEX returns the first item in A2, the second item in A3, and so on. ROWS($A$2:A2) evaluates to 1 in the first row and increases as the formula is filled down. When there are no more names to return, IFERROR displays a blank instead of an error.

For example, if a workbook has tabs named Overview, Data, and Chart, the filled formulas return those three names in order, followed by blank cells. The order reflects the workbook’s sheet-tab order, not alphabetical order. The formula reads names; it does not create clickable links or change the tabs themselves.

Use a spill formula in Microsoft 365

If you have set up the same workbook-level name and use Microsoft 365 or Excel 2024, you can try a dynamic-array version instead of copying a formula down. With the output area below the formula cell empty, enter this in A2:

=TEXTAFTER(SheetNames,"]")

TEXTAFTER returns the text after the closing bracket, and Excel’s dynamic-array behavior can spill the returned names into cells below A2. This is shorter, but the copied INDEX formula is a useful fallback if spill results are unavailable, blocked, or awkward in a particular workbook. If Excel displays a spill error, clear cells in the intended output area and check for merged cells that may prevent the result from expanding.

What the list includes—and what it does not

This is a workbook-level inventory, not a list of only the tabs currently visible to the user. It can include hidden sheets, which is useful for auditing a workbook but may surprise someone expecting only visible tabs. The GET.WORKBOOK result can also include other sheet types, such as chart sheets, so do not assume every returned name belongs to a standard worksheet. The formula does not unhide sheets or grant access to protected content.

GET.WORKBOOK refers to the workbook in which the defined name is evaluated. Keep the name and the output formula in that workbook rather than relying on a copied formula in another file. If you need only visible worksheet tabs, or need to distinguish worksheets from chart sheets, this technique may need additional filtering or a different tool.

Refresh the list after workbook changes

Adding, deleting, moving, or renaming a sheet does not always cause a regular worksheet calculation to refresh this list immediately. The T(NOW()) portion encourages recalculation, but Excel recalculates formulas in response to calculation events; it is not a dedicated sheet-change notification. If the output appears out of date, use Formulas > Calculate Now. On Windows, Ctrl+Alt+F9 forces a full recalculation of all formulas in open workbooks.

If Excel is set to manual calculation, formulas may remain unchanged until you calculate. Check Formulas > Calculation Options and choose Automatic if that suits the workbook. The menu can also be used to recalculate without changing the workbook’s calculation mode. On Mac, use the Formulas tab’s calculation command if available in your Excel build; keyboard shortcuts differ from Windows.

  • After renaming a tab, recalculate and check whether the displayed name changes.
  • After adding a tab, fill the copied formula down far enough to show the additional result.
  • After deleting a tab, recalculate; any extra output rows should become blank once the list updates.

Security and compatibility considerations

Because GET.WORKBOOK is an Excel 4.0 macro function, an organization’s security policy or Excel’s Trust Center settings may block it. Do not lower workbook or macro security broadly just to make a sheet list work. If Excel shows a security warning, follow your organization’s guidance or ask an administrator whether this workbook feature is permitted. A file downloaded from the internet may also open in Protected View, which can restrict features until the file is reviewed and trusted.

Use the desktop version of Excel for this method. Excel for the web does not provide the same support for running legacy macro functionality, so this defined-name approach may not calculate there as it does in desktop Excel. Mac behavior and security controls can vary by Excel version and organization policy, too. Test the workbook in the actual Excel app and environment where it will be used rather than assuming the formula will work identically everywhere.

Saving as .xlsm is a practical way to preserve macro-enabled workbook content, but it does not make blocked XLM functionality safe or automatically enable it. If the file must remain a plain .xlsx, verify that the defined name is retained and works in the target Excel versions; do not treat a successful save as proof that every recipient can calculate the result.

Alternatives when the formula is unsuitable

For a one-time list, copying the tab names manually may be safer than enabling a legacy macro feature. For a repeatable process in a managed Microsoft 365 environment, Power Query or an approved Office automation workflow may be a better fit, but these options involve more setup and may not refresh the current workbook’s sheet inventory in the same way. A VBA macro can also write sheet names to cells, but it requires permission to run macros and can change workbook contents.

Choose based on how the list will be used:

  • Occasional, simple inventory: use manual entry or the defined-name formula if XLM functions are allowed.
  • Automatic recalculation in desktop Excel: test the formula in the target workbook, including after adding and renaming tabs.
  • Strict security or web-only use: avoid relying on GET.WORKBOOK and use a permitted manual or organizational automation process.
  • Need visible worksheets only: confirm whether the results include hidden or non-worksheet tabs before distributing the list.

There is no built-in formula-only method that avoids these compatibility considerations while reliably enumerating every tab in every Excel environment. The method’s main advantage is that it returns sheet names in a worksheet list; its main trade-off is reliance on a legacy macro function.

Common problems

  • #NAME? in the output: check that the workbook-level name is spelled exactly SheetNames, and that its Refers to entry begins with an equals sign and contains valid parentheses.
  • The list stops early: fill the formula farther down. The copied formula cannot display names in rows where it has not been entered.
  • The list is stale: recalculate the workbook and confirm that calculation is not set to manual.
  • The names include unexpected tabs: hidden sheets or chart sheets may be part of the workbook-level result.
  • The formula works on one computer but not another: compare Excel versions, desktop versus web use, and macro security policies.

Also check that the formula is not being entered as text. If a cell displays the formula characters rather than a result, set its number format to General and re-enter the formula. In a copied list, make sure the output cells are not merged and that the spill area is clear if using TEXTAFTER.

Frequently Asked Questions

Does Excel have a built-in formula to list all tab names?
No ordinary worksheet function directly returns all sheet names. The approach above uses the legacy GET.WORKBOOK macro function in a defined name, then a worksheet formula to display each result.

Can I list hidden sheet names too?
Yes, this workbook-level approach can return hidden sheet names as well as visible ones. Treat the output as an inventory, not as a visibility filter.

Why does the formula show a workbook name before the tab name?
GET.WORKBOOK may return an entry such as [Budget.xlsx]Overview. The MID and FIND portion of the formula removes the prefix and displays the text after the closing bracket.

Will the list update automatically when I add a tab?
Not necessarily at the moment you add it. Recalculate the workbook, and make sure the copied formula extends far enough to include another row. The volatile name encourages recalculation but is not a guaranteed sheet-change event.

Can I use this formula in Excel for the web?
Do not rely on it there. GET.WORKBOOK is a legacy macro function, and web Excel does not offer the same support as desktop Excel for this functionality. Check the workbook in the version where the list must be maintained.

Can the formula return only visible worksheet tabs?
Not with the basic formula as shown. It can include hidden sheets and other sheet types, so filtering to visible worksheets requires a different method and additional checks.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.