How to List Sheet Name in Excel (5 Methods + VBA)

In this article, we’ll discuss various ways to derive a list of sheet names in Excel. Unfortunately, there is no dedicated function to fetch a list of sheet names in Excel, but we can use a combination of several functions, the 2-step process of using Name Manager & formula, or VBA code to retrieve a list of sheet names into a single column.

In the following figure, we have 12 sheets which are mainly prices in months during a year. The names of the sheets are Price_Jan, Price_Feb, and so on.

Let’s fetch a list of the sheet names.

Method 1 – Using the Combination of INDEX & FIND Functions

We can use a formula containing the INDEX, LEFT, MID, and FIND functions.

Steps:

• Click on the Formulas tab > Select the Name Manager option from the Defined Names ribbon.
• In the Name Manager box that opens, click the New button.

• Insert the Name (here, Worksheets).
• Insert the below formula in the Refers to section:

Note:
GET.WORKBOOK is a macro-enabled function that stores the sheet names in the workbook.

• Enter the following formula in cell B5:

• Press Enter and use the Fill Handle tool to fill the cells below.

The list of sheet names is returned.

Method 2 – Using the INDEX and REPLACE Functions (Automated List)

This time, after clicking the New option from the Name Manager dialog box, insert the Name as SheetNames, and the below formula in the Refers to section:

Insert the following formula in cell C5:

B5 is the starting cell of the serial number (S/N).

Method 3 – Using the TRANSPOSE Function

The TRANSPOSE function returns a horizontal cell range as a vertical cell range, or vice versa.

Steps:

• Ensure the Name is GetSheetNames.
• Enter the below formula:

• Insert the following formula:

The following output is returned:

Method 4 – Using the LOOKUP Function

Before using the LOOKUP function, create a new name where the Name is Sheets and the formula in the Refers to section is:

Note:
The same macro-enabled formula is used as in the first method (Name: Worksheets).

Insert the formula:

• Press Enter and use the Fill Handle to return the following output:

Method 5 – Dynamic List of Sheet Names Using SUBSTITUTE Function

We can create a dynamic list of sheet names using the SUBSTITUTE function.

Steps:

• Set the name as Dynamic_List_of_SheetNames and enter the below formula:

• Insert the following formula in cell C5:

B5 is the starting cell of the serial number (S/N).

Method 6 – Using VBA Code

Last but definitely not least, we can apply VBA code to get the list of sheet names.

Steps:

• Open a module by clicking Developer > Visual Basic.

• Go to Insert > Module.

• Copy and paste the following code into the module:

Sub GetListOfAllSheets()
   Dim w As Worksheet
   Dim i As Integer
   i = 5
   Sheets("Price_Jun").Range("B:B").Clear
   For Each w In Worksheets
       Sheets("Price_Jun").Cells(i, 2) = w.Name
       i = i + 1
   Next w
End Sub

• Run the code.

The output will be as follows:

Notes:
Take care of the following when using the VBA code:

• Worksheet name: Here, the worksheet name is Price_Jun.
• Cells(i, 2) means the cell location of row i (here, i=5) and column 2.

Things to Remember 

I. As GET.WORKBOOK is a macro-enabled function, save the Excel file in .xlsm format (designated extension format for macro-enabled Excel file).

II. A #BLOCKED error may be returned instead of the output if the workbook is unable to update.

Download Practice Workbook

Related Articles

• How to Get Excel Sheet Name
• How to Insert Excel Sheet Name from Cell Value
• How to Rename Sheet in Excel
• How to Use Sheet Name Code in Excel
• How to Search by Sheet Name in Excel Workbook

<< Go Back to Excel Sheet Name | Excel Worksheets | Learn Excel