If you mean worksheet tabs, Excel has no built-in worksheet formula that directly lists their names. A legacy Excel 4 macro function, GET.WORKBOOK, can return sheet names through a defined name, but it may be blocked by security settings; if you need a dependable or shareable solution, consider listing them manually or using an approved macro instead.
What “tabs” means in Excel
Excel’s worksheet tabs are the labeled tabs at the bottom of a workbook, such as Summary, Sales, or January. They represent worksheets, not browser-style tabs or the tabs in the ribbon. A request to list tabs “in a formula” usually means displaying the worksheet names in cells.
There is an important limitation: ordinary worksheet formulas can refer to cells on sheets whose names you already know, but Excel does not provide a standard function such as SHEETNAMES that discovers every worksheet in the open workbook. Functions such as SHEET and SHEETS return a sheet number or a count; they do not return a list of names. The legacy method below is therefore a workaround, not a normal formula-only feature.
Use GET.WORKBOOK through a defined name
GET.WORKBOOK is an Excel 4.0 macro function, sometimes called an XLM function. It is not entered directly into a worksheet cell in the same way as SUM or TEXT. Instead, you create a workbook-level defined name that calls it, then use a regular formula to retrieve each item from the returned list.
In Excel for Microsoft 365 on Windows, open Formulas > Name Manager > New. Set the name to SheetNames, set the scope to Workbook, and enter this in the Refers to box:
=GET.WORKBOOK(1)&T(NOW())
The 1 argument asks for worksheet names. The T(NOW()) part is often added to make the defined name volatile, so it can refresh when Excel recalculates. This is still an XLM-based technique: the presence of a formula in Name Manager does not make it an ordinary worksheet function or bypass Excel’s security controls.
Return the names in worksheet cells
Once the workbook-level name exists, use INDEX to retrieve its entries one at a time. The raw entries can include the workbook name in brackets as well as the worksheet name, so the following version removes the bracketed prefix.
With the defined name SheetNames in place and the first output cell at A2, enter this formula in A2, then fill it down:
=IFERROR(MID(INDEX(SheetNames,ROWS($A$2:A2)),FIND("]",INDEX(SheetNames,ROWS($A$2:A2)))+1,255),"")
ROWS($A$2:A2) evaluates to 1 in A2, 2 in A3, and so on, selecting the next entry each time. MID starts just after the closing bracket, leaving the sheet name. IFERROR returns a blank when the copied formula runs past the end of the list. The 255 is a character limit for the extracted name, comfortably above Excel’s 31-character worksheet-name limit.
For example, if a workbook has tabs named Overview, Sales, and 2026 Budget, the filled formulas should display those three names in order, followed by blank cells. The list reflects the workbook’s sheet order. It includes worksheets; do not assume it will also provide a useful inventory of chartsheets or every other kind of sheet object.
Steps and checks before relying on the result
- Save a separate copy of the workbook before trying a legacy XLM method, particularly if it contains important data.
- Create the defined name with workbook scope, not a single-sheet scope, so it is available from the output worksheet.
- Put the output formula in a clear column and fill it down far enough to cover the number of sheets you expect.
- Test the results against the visible tabs, including hidden worksheets if your workbook uses them.
- After adding, deleting, renaming, or reordering sheets, recalculate and confirm that the list has updated as expected.
The workbook’s calculation settings can affect when a volatile defined name refreshes. If the output appears stale, press F9 to recalculate in Excel for Windows; on Mac, use the equivalent recalculation command available for the current Excel version. A recalculation is not a guarantee that an organization’s security policy will permit XLM functions.
Security, compatibility, and maintenance limits
XLM is a legacy macro technology. Microsoft 365 security controls or an organization’s administrator may disable Excel 4.0 macros, and Excel can show a security warning or refuse to evaluate the defined name. Do not lower security settings or enable content in a file merely to make a sheet-name list work. If the workbook comes from someone else, follow your organization’s rules and verify its source.
Because GET.WORKBOOK is not a documented modern worksheet function, behavior can vary with Excel version, update channel, and security policy. A colleague who opens the file may see a warning, errors, or no refreshed list. Test the workbook in the environment where it will actually be used, and explain the dependency to anyone who receives it. On Excel for Mac or Excel for the web, availability and behavior may differ; do not assume the Windows XLM approach will work there. Excel for the web also does not support running VBA macros.
Sheet names may contain spaces, punctuation, or numbers, which is why the extraction formula treats the text after the closing bracket as the name rather than trying to split at a space. Renaming a sheet changes what the list returns after recalculation. A formula-generated list is also a display of names, not a set of clickable worksheet links; making navigation links is a separate task.
Alternatives when a formula is not suitable
If the list is short or only needed once, typing the names into a table is the simplest option and avoids macro compatibility questions. To inspect the workbook interactively, use the sheet-navigation controls near the lower-left corner of the Excel window; they help locate sheets but do not create a cell-based list.
For a repeatable workbook used by a team, a reviewed VBA procedure can enumerate worksheet names, but it requires macro-enabled files and permission to run macros. VBA code should be reviewed before use, and a backup should be made first because running a macro cannot be undone with Ctrl+Z. If the workbook is managed centrally, ask the owner or administrator which automation method is approved. There is no standard Power Query worksheet formula that automatically substitutes for GET.WORKBOOK in every workbook setup.
If you only need to know how many sheets there are, not their names, the built-in SHEETS function may be enough. With no argument, the formula =SHEETS() returns the number of sheets in the workbook. For instance, a workbook containing five sheets returns 5. That count does not identify the tabs, and it should not be confused with the position returned by SHEET.
Worksheet tabs versus a tab character
There is another possible meaning of “tabs in Excel formula”: a tab character inside cell text. That is different from listing worksheet names. Excel’s CHAR function can create a tab character using character code 9, although how it appears depends on the cell, font, and surrounding text.
With the text Hello in A1 and World in B1, enter this formula in C1 to join them with a tab character:
=A1&CHAR(9)&B1
This creates text separated by a tab character; it does not add a worksheet tab or list workbook sheets. If the intended question is about a tab keystroke inside a formula, CHAR(9) is the relevant idea. If it is about the worksheet labels at the bottom of the window, use the defined-name approach or one of the alternatives above.
Frequently Asked Questions
Can Excel list worksheet names with a normal formula?
No. Excel for Microsoft 365 does not include a standard worksheet function that spills all worksheet names into cells. The GET.WORKBOOK method uses a legacy XLM macro function in a defined name.
Does SHEETS() list the tabs?
No. =SHEETS() returns a count of sheets, not their names. The SHEET function returns a sheet’s position number, not a name list.
Why does GET.WORKBOOK show an error or warning?
It may be blocked because it is an Excel 4.0 macro function, or the defined name may have been entered with the wrong name or scope. Check your organization’s security policy rather than weakening protections, and verify the formula in Name Manager.
Will the list update when I rename a tab?
It is intended to recalculate, but refresh timing can depend on calculation settings and XLM support. Recalculate the workbook and check the output after a change; do not treat the result as reliable until you have verified it in your Excel environment.
Can I use this formula in Excel for the web?
Do not count on it. The method depends on a legacy macro function, whose support differs from ordinary worksheet formulas and may be restricted. For a workbook used in a browser, a manually maintained list or an approved compatible automation method is safer.
Related
- Unmerge Cells in Excel: Keyboard Shortcuts and Steps
- How to Unmerge Cells in Excel and Keep Data
- Unmerge Cells in Excel on iPad: Step-by-Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Drop-Down Shortcut: Open a Column MenuIn Excel for Microsoft 365 on Windows, select a cell in a filtered column and press Alt+Down Arrow…
- Long Invoice Template Excel: Layout and FormulasA long invoice template in Excel is a worksheet designed to handle many billable lines while keeping the…
- Excel Data Validation Custom Formula: Rules and ExamplesIn Excel for Microsoft 365 on Windows, select the cells to control, choose Data > Data Validation, set…
- Calculating NPV in Excel: Formula, Steps, and ExamplesTo calculate net present value in Excel, place the discount rate in B1, the initial investment in B2,…
- Python pandas Excel Functions: Read and Write FilesPython pandas handles Excel files mainly with read_excel() to load workbook data into a DataFrame and DataFrame.to_excel() to…
- Calculate Age in Excel Using Date of Birth with DATEDIFTo calculate age in Excel from a date of birth, put the birth date in A2 and enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.