Excel has no built-in worksheet formula that directly returns every tab name. In Microsoft 365 for Windows, you can list them with the legacy GET.WORKBOOK function stored in a defined name, then use TEXTAFTER in a worksheet cell to display the names as a spilling list.
Why a Setup Step Is Needed
Worksheet formulas can calculate from cell values, but they do not ordinarily expose workbook structure such as the names of every sheet. Functions such as SHEET and SHEETS can return a sheet’s position or the number of sheets; neither returns a list of names. That is why a formula such as =SHEETS() cannot answer “What are all my tab names?”
GET.WORKBOOK is an older Excel 4.0 macro function, also called an XLM macro function. It can return sheet information, but Excel does not allow you to enter it as an ordinary worksheet formula. The workaround is to place it in a workbook-level defined name and refer to that name from a cell. The cell formula then processes the returned list.
This method is useful when a workbook’s tab names change and you want a list that can be refreshed without typing every name. It is not a standard, modern worksheet-only solution: it depends on a legacy feature, which may be restricted by Excel’s security settings or by an organization’s policies.
Create the Workbook-Level Name
In Excel for Microsoft 365 on Windows, open Formulas > Name Manager, choose New, and create a name called SheetNames. Set Scope to Workbook, so the name can be used from any worksheet. In Refers to, enter the following definition. It assumes the current workbook is the workbook whose tabs you want to list:
=GET.WORKBOOK(1)&T(NOW())
Choose OK, then close Name Manager. The T(NOW()) part is a common way to make the defined name volatile, encouraging Excel to recalculate it when the workbook recalculates. It does not add a date or other visible text to the result: T returns an empty string for the numeric result of NOW.
The GET.WORKBOOK call is deliberately in the name definition, not in a worksheet cell. Typing it directly into a cell will not work as an ordinary formula. If Excel blocks or removes the definition, do not weaken security settings on a work computer without checking with your administrator; use one of the alternatives below instead.
Display the Names in a Worksheet
With the name set up, enter this formula in an empty cell, such as A1. It assumes SheetNames is the workbook-level name created above and that the cells below A1 are clear for the results:
=TEXTAFTER(SheetNames,"]")
In Microsoft 365, the result should spill down into one cell per returned name. For example, if the workbook has tabs called Read Me, January, and Summary, the displayed list is those three names, one per row. The actual output reflects the workbook’s current tabs, rather than a manually maintained list.
TEXTAFTER removes the workbook-name prefix by returning the text after the closing square bracket. The defined name’s entries commonly have a form like [Budget.xlsx]January; the part after ] is the tab name. This is why the formula is more useful than simply displaying the raw result from GET.WORKBOOK.
If your Excel installation uses a different list separator because of regional settings, Excel may require semicolons in formulas instead of commas. The formulas shown here use the comma separators specified for standard English-language Excel. The formula also requires Microsoft 365 or Excel 2021 or later for TEXTAFTER; the legacy defined-name technique is a separate compatibility consideration.
What the Results Include—and What They Do Not
The list is generated from workbook sheet information, not from a range of cell data. It is therefore different from making a table of names in cells and using FILTER or UNIQUE to clean that table. If a tab is renamed, the returned text can change after recalculation. It does not create hyperlinks, copy sheet contents, or reveal the location of a sheet’s data.
Remember that Excel distinguishes worksheets from other sheet types, such as chart sheets. The output of a legacy sheet-listing function may reflect sheet objects beyond the ordinary grid worksheets, depending on the function’s behavior and workbook. If you require a list containing only worksheet tabs, check the output against the workbook’s sheet types before relying on it for a report or automated process.
Hidden tabs are another reason to verify the results. A tab may be hidden from the normal sheet-tab bar yet still exist in the workbook. Do not treat a name list as a security feature: hiding a sheet does not protect sensitive data. Use workbook permissions and appropriate data controls when confidentiality matters.
Refresh, Recalculation, and Spill Issues
The volatile expression in the defined name is intended to prompt updates, but a result may not change immediately in every situation. Try recalculating the workbook with F9 on Windows. On Mac, use Fn+F9 where the function keys control system features, or use Excel’s calculation command. If the workbook is set to manual calculation, check Formulas > Calculation Options and consider whether automatic calculation is appropriate for your file.
- A spill error appears: Clear the cells below the formula and remove merged cells from the output area. A dynamic-array formula needs room to return its list.
- The name is not recognized: Reopen Name Manager and confirm that the name is exactly
SheetNames, has workbook scope, and its definition begins with=GET.WORKBOOK(1). - The list is stale: Recalculate with F9, then check whether the defined name is still present and whether Excel or an administrator has restricted XLM functions.
- The output contains a prefix: Confirm that the cell formula uses
TEXTAFTERwith the closing bracket as the delimiter, and that the name definition returns the expected workbook-qualified entries.
On Excel for the web, workbook features that rely on legacy macro functions may not calculate or behave like they do in desktop Excel. If the list is important, open the file in the supported desktop version and verify the results there. Excel for Mac has Name Manager and dynamic-array functions in current releases, but security behavior and available legacy-function support can differ; do not assume the Windows setup will work identically on every Mac installation.
Alternatives When the Formula Is Blocked
If you only need the names once, the simplest alternative is to read the tabs at the bottom of the workbook and type the names into a list. That is easy to understand but becomes outdated when tabs are renamed, added, or deleted. For a frequently updated inventory, a small VBA macro can write the sheet names into cells. VBA is not a worksheet formula, and it requires permission to run macros.
The example below writes the names of all sheet objects in the active workbook into column A, starting at A1 on the active sheet. It clears that many cells in column A first, so choose a blank output column and activate the workbook you intend to process. Save a copy before running it: macros cannot be undone with Ctrl+Z.
Sub ListSheetNames()
' Writes each sheet name in the active workbook to column A.
Dim sh As Object
Dim outputCell As Range
Dim i As Long
Set outputCell = ActiveSheet.Range("A1")
outputCell.Resize(ActiveWorkbook.Sheets.Count, 1).ClearContents
i = 0
For Each sh In ActiveWorkbook.Sheets
i = i + 1
outputCell.Offset(i - 1, 0).Value = sh.Name
Next sh
End Sub
To add it, press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, paste the code into the module, and run the macro while the intended workbook is active. On Mac, open the Visual Basic Editor from the Tools > Macro menu or the Developer tab if available; shortcuts can vary with keyboard settings. Save the workbook as a macro-enabled file if you need to retain VBA code. If you cannot use macros, the manual list is safer and more portable than changing security controls to enable an older macro function.
Choosing the Right Method
Use the defined-name formula when you need a live-looking list in a desktop workbook, are comfortable with the legacy XLM dependency, and your Excel security settings allow it. Use manual entry for a one-off inventory or a workbook that must work consistently in browser-based Excel. Use VBA when you need repeatable output and your environment permits macros, particularly if you want to adapt the process to write names somewhere specific.
For a workbook that will be shared widely, test the approach in the recipient’s version of Excel before building other formulas or reports around it. A name that works on one desktop may be blocked on another computer, and a spilled formula can be disrupted if someone fills the output cells. If sheet names feed a process that matters, document the method and check the output after structural changes.
Frequently Asked Questions
Can I get all tab names with one normal Excel formula?
No. Excel does not provide a standard worksheet function that directly returns all tab names. The method above combines the legacy GET.WORKBOOK function in a defined name with a normal cell formula to display the result.
Why does GET.WORKBOOK not work when I type it into a cell?GET.WORKBOOK is an Excel 4.0 macro function, not an ordinary worksheet function. Put it in a defined name through Name Manager, then refer to that name from a worksheet cell.
Will the list update when I rename or add a tab?
It is designed to recalculate from the workbook’s sheet information, and the T(NOW()) part encourages recalculation. If the displayed list does not update, press F9 on Windows and check that the defined name still exists and is allowed by Excel’s security settings.
Can I list only visible worksheet tabs?
The basic method is not a reliable visibility filter. A hidden sheet still exists in the workbook, and the returned list may include sheet types or visibility states you did not intend. Verify the results, or use a tailored VBA procedure if the requirement is specifically “visible worksheets only.”
Does the method work in Google Sheets?
No. Google Sheets does not support Excel’s Name Manager or the GET.WORKBOOK macro function. Use a Google Apps Script or enter the names manually if you need a tab inventory in Sheets.
Is a list of tab names a security check?
No. It only displays names; it does not secure, encrypt, or restrict access to sheet contents. Hidden sheets can still be exposed, so protect sensitive information with appropriate access controls rather than relying on a hidden tab.
Related
- Excel Flip Data Upside Down: Reverse Rows in Excel
- Excel Find Table Names: Where to Look and How
- Excel Find Shortcut Key: Search Cells Fast
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MIN Function Examples: Find the Smallest ValueExcel’s MIN function returns the smallest number in a set of values. For example, with scores in B2:B8,…
- How to Find the Minimum in Excel Using the MIN FunctionTo find the smallest number in a range, use Excel’s MIN function. If your values are in cells…
- Sort an Excel Column Highest to Lowest: Quick StepsTo sort numbers in Excel from highest to lowest, select a cell in the column and choose Data…
- Excel SUMIF: Sum Values When a Checkbox Is CheckedTo add only the values whose checkboxes are checked, put each checkbox’s TRUE/FALSE value in one column and…
- Excel Formula to Join Two Text CellsTo join two text cells in Excel, enter =A2&B2 in another cell, replacing A2 and B2 with the…
- Excel Filter Not Showing All Items? Find the CauseIf an Excel filter is not showing all items, first clear filters on every column, then check that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.