In this Article
This tutorial demonstrates how to list the sheet names of a workbook with a formula in Excel.
List Sheet Names Using Named Range and Formula
There is no built-in function in Excel that can list all the worksheets in a workbook. However, this can be achieved by using a combination of different functions.
To list the sheet names in a workbook, we will do the following:
- Create a named range “Worksheets”
- Use a formula to list out all sheet names.
Create Name Range for Sheet Names
To create a Named Range for the sheet names, in the Excel Ribbon: Formulas > Name Manager > New
Type “Worksheets” in the Name Box:
In the “Refers to” section of the dialog box, we will need to write the formula
|
1 |
=GET.WORKBOOK(1) & T(NOW())" |
This formula stores the names of all sheets (as an array in this format: “[workbook.xlsm].Overview”) in the workbook to the named range “Worksheets”.
The “GET.WORKBOOK” Function is a macro function, so your workbook has to be saved as a macro-enabled workbook (file format: .xlsm) for the sheet names to be updated each time the workbook is opened.
Note: When filling the Edit name dialog box, workbook should be selected as the scope of the name range.
Using Formula to List Sheet Names
Now we use a formula to list the sheet names. We’ll need the INDEX, MID, FIND, and ROWS Functions:
|
1 |
=INDEX(MID(Worksheets,FIND("]",Worksheets)+1,255),ROWS($B$5:B5)) |
- The formula above takes the “Worksheets” array and displays each sheet name based on its position.
- The MID and FIND Functions extract the sheet names from the array (removing the workbook name).
- Then the INDEX and ROW Functions display each value in that array.
- Here, “Overview” is the first sheet in the workbooks and “Cleaning” is the last.
For more detail on how the MID and FIND Functions work to get sheet names, see get-sheet-name link.
Alternate Method
You also have the option to create the list of sheet names within the Name Manager. Instead of
|
1 |
=GET.WORKBOOK(1) & T(NOW()) |
set your “Refers to” field to
|
1 |
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") |
Now there’s no need for MID, FIND, and ROWS in your formula. Your named range is already made up of only sheet names.
Use this simpler INDEX formula to list the sheets:
|
1 |
=INDEX(SheetName,B3) |
