Excel has no built-in worksheet formula that directly returns every sheet name. In Excel for Microsoft 365 on Windows, you can list them with the Excel 4.0 macro function GET.WORKBOOK, saved as a workbook-level defined name, then use a cell formula to display the names.
The formula method at a glance
The technique has two parts: define a name that retrieves the workbook’s sheet list, then refer to that name in a worksheet formula. The setup is more involved than entering one formula in a cell, but it can produce a list that updates when sheets are added or removed and the workbook recalculates.
No worksheet cell layout is required for the defined name; it is a workbook-level name that retrieves sheet information. In Formulas > Name Manager, choose New, enter SheetNames as the name, set Scope to Workbook, and enter the following in Refers to:
=GET.WORKBOOK(1)&T(NOW())
For the worksheet formula, assume the list should start in A2. Enter this in A2 and copy it down:
=IFERROR(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS($A$2:A2)),"")
The name returned by GET.WORKBOOK commonly includes a workbook label in brackets before the sheet name. The formula finds the closing bracket and keeps the text after it. INDEX returns the first name in A2, the second in A3, and so on; when the copied formula runs out of names, IFERROR displays a blank. For example, if the workbook has tabs named Summary, Data, and Notes, the first three results are Summary, Data, and Notes.
Set up the defined name in Excel
Use a workbook-level name so the formula can refer to it from any sheet in the file. In Excel for Microsoft 365 on Windows:
- Open Formulas > Name Manager, then select New.
- Type
SheetNamesin the Name box and chooseWorkbookfor Scope. - Enter the defined-name formula in Refers to, then select OK and close Name Manager.
- Put the worksheet formula in the cell where the list should begin, then fill it down far enough to allow for the expected number of sheets.
The text in the Name box is an identifier, not a sheet tab name. Avoid spaces in it; if you choose a different name, use that same name in the worksheet formula. A workbook-scoped name is generally easier to maintain than a sheet-scoped one because it can be used from different tabs without qualifying it with a worksheet name.
On Excel for Mac, the ribbon and dialogs may be arranged differently, but Name Manager or Define Name is the relevant feature. Availability of Excel 4.0 macro functions can depend on the Mac version and security policy, so the Windows desktop steps should not be assumed to work unchanged. Excel for the web does not provide a way to create or run this macro-function method; set it up in desktop Excel instead.
Use a spilling formula in Microsoft 365
If you have Excel for Microsoft 365 and want one formula to fill a list automatically, use the same workbook-level SheetNames name. With the first result intended for A2 and the cells below it empty, enter this in A2:
=TEXTAFTER(SheetNames,"]")
TEXTAFTER removes the workbook label through the closing bracket, and dynamic arrays spill the returned names into cells below the formula. This avoids copying a formula down and leaving extra blank formulas. If the spill area contains data, Excel displays a #SPILL! error; clear the obstructing cells or move the formula. TEXTAFTER requires Microsoft 365 or Excel 2024, so use the copied-down INDEX version in older releases.
The dynamic formula still depends on GET.WORKBOOK. Microsoft 365 adds convenient spill behavior, but it does not turn the macro function into a standard worksheet function or remove its security limitations.
How the method works and what it returns
GET.WORKBOOK is an older Excel 4.0 macro function, sometimes called an XLM macro function. It can retrieve workbook sheet information from a defined name, even though it is not ordinarily entered directly as a worksheet formula. The argument 1 requests the sheet-name list. The defined name makes that result available to the cell formula.
The extra T(NOW()) in the defined name is a recalculation aid used in this technique. NOW() is volatile, meaning it recalculates when Excel recalculates the workbook; T contributes empty text, so it does not add a date to the returned names. It is not a guarantee of instant updating at the exact moment a tab is added. The workbook may need a recalculation before the list reflects a change.
The returned list represents sheet objects in the workbook, not just visible worksheet tabs. Hidden sheets may be included, and a workbook that contains chart sheets may return those sheet names too. The extraction formula removes the bracketed workbook prefix; it does not filter hidden sheets or distinguish sheet types. If the task is specifically to list only visible worksheets, this method may need additional filtering or a different solution.
Refresh behavior and workbook changes
After adding, deleting, or renaming a sheet, check whether the output has updated. If it has not, recalculate the workbook with F9 on Windows or Fn+F9 on many Mac keyboards; Mac keyboard behavior depends on the system’s function-key settings. Excel’s Formulas > Calculate Now command is another way to recalculate. A full calculation can also be requested with Ctrl+Alt+F9 on Windows; the Mac equivalent varies by keyboard and Excel version.
With the copied-down formula, keep enough filled rows for future sheets. Extra rows show blanks after the last name, while too few rows truncate the list. The spilling version expands automatically, but only if its destination cells are clear. If you need a permanent snapshot rather than a live list, copy the results and use Paste Special > Values; those names will no longer update when the workbook changes.
Saving and reopening can also matter. Excel may apply different security rules to macro functions depending on where a workbook came from and how the organization manages macros. If the list stops updating after reopening, check whether Excel has displayed a security notice or whether your administrator blocks XLM functions. Do not weaken macro protections just to make a sheet-name list work.
Common problems and fixes
#NAME?appears: Check that the defined name exists, is spelledSheetNamesin both places, and has workbook scope. Also check whether Excel is blocking or does not support the XLM macro function in that environment.- The result includes the workbook name: Make sure the cell formula extracts text after the closing bracket. If your defined name is different, update the cell formula to reference the correct name.
- Names are missing or stale: Recalculate, confirm the name’s Refers to entry is intact, and ensure the formula has been filled down far enough. Recalculation may refresh the list, but security restrictions can prevent the method from working.
#SPILL!appears: This applies to theTEXTAFTERversion. Clear cells in the intended output range, including cells that look empty but contain formulas or spaces.- The formula shows a blank: In the copied-down version, blanks are expected after the last sheet name because
IFERRORhides the out-of-range result. Move the formula higher or fill it down farther if names are missing.
Sheet names cannot contain certain characters, including square brackets, so the bracket-based extraction is suitable for ordinary Excel sheet names. If a formula is being copied between workbooks, remember that the defined name belongs to its workbook; copying only the cell formula does not necessarily copy the name definition.
Limitations and safer alternatives
The main drawback is the reliance on an Excel 4.0 macro function. It is not a normal function documented for direct use in worksheet cells, and security settings or organizational policy may block it. That makes this method useful for workbooks where the defined name is permitted, but less suitable when a file must be portable across Excel for the web, locked-down computers, or unrelated spreadsheet applications.
For a one-time list, typing or copying sheet names manually may be the simplest option. For a repeatable process under strict macro policies, a managed script or a small VBA macro can be considered by someone authorized to use it. VBA macros also have security implications, require an appropriate macro-enabled file when saved with code, and cannot be undone with Ctrl+Z after they change a workbook. Save a copy before running any macro. Power Query is useful for importing and transforming data, but it is not a simple built-in worksheet formula for enumerating the current workbook’s tabs.
Before choosing an approach, consider how often the workbook changes, who needs to open it, and whether hidden or chart sheets should appear. A quick manual list may be most reliable for a stable workbook; a defined name can save time for a frequently changing desktop workbook, provided its security requirements are acceptable.
Frequently Asked Questions
Is there a normal Excel formula that lists every sheet name?
No. Excel does not provide a standard worksheet function whose direct purpose is to return all sheet names. The formula approach above relies on GET.WORKBOOK in a defined name, an Excel 4.0 macro function.
Can I enter GET.WORKBOOK(1) directly in a cell?
Usually not as an ordinary worksheet formula. Set it up as the Refers to expression for a defined name, then use that name from the worksheet formula.
Will the list update when I add a tab?
It can update after recalculation, but it may not change immediately when a sheet is added or renamed. Recalculate the workbook and check that the XLM function is allowed.
Does the formula include hidden sheets?
The method retrieves workbook sheet information rather than filtering for visible tabs, so hidden sheets can be included. Chart sheets may also appear; it is not a visible-worksheet-only filter.
Does this work in Excel for the web?
No, not as a method to create and run the XLM-based defined name. Use desktop Excel for this technique, or choose a manual or organization-approved alternative.
Can the results be sorted alphabetically?
The basic method returns names in the workbook’s sheet order. If you need alphabetical order, sort a copied list manually or use a separate formula approach compatible with your Excel version; sorting is not part of the sheet-name extraction formula itself.
Related
- Excel Data Validation List: Create a Yes/No Dropdown
- Excel Data Validation Simple List: Create and Manage It
- Excel Data Validation List: Can It Show Two Columns?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Add Dates in a Weekly Sequence AutomaticallyTo add dates in weekly sequence in Excel, enter a starting date in A2, then put =A2+7 in…
- Vlookup with multiple criteria?Learn how to use VLOOKUP with multiple criteria in Excel, build a reliable helper key, avoid common errors,…
- How to Multiply Different Cells in Excel With a FormulaTo multiply different cells in Excel, select an empty cell, type a formula that starts with an equals…
- Excel Last Day of Month: Formula and ExamplesTo return the last day of the month for a date in Excel, use =EOMONTH(A2,0), where A2 contains…
- VLOOKUP in Excel for Multiple Columns: Methods and TipsYes. In Excel for Microsoft 365, VLOOKUP can return values from multiple columns either by copying one formula…
- Remove Spaces After Text in Excel Cells with TRIMTo remove only ordinary spaces at the end of text while preserving spaces inside words, put this formula…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.