In some cases, you may have dozens of named sheets in your workbook. However, if you need the list of sheet names, you can utilize the formulas. In this article, I’ll discuss 5 conventional methods which are mainly formulas and also **VBA** code to find the sheet name list in Excel.

If you need to get the sheet name only instead of the list you can visit the **Excel Sheet Name** article. Here, we’ll show the process of retrieving the list of sheet names in a single column.

**Read More: How to Get Excel Sheet Name (2 Methods)**

**Table of Contents**hide

## Download Practice Workbook

## How to Get Sheet Name List in Excel

In the following figure, you see that we have 12 sheets which are mainly prices in months along a year. Also, the names of sheets are like **Price_Jan**, **Price_Feb**, and so on.

Unfortunately, there is no dedicated function to get the list of sheet names in Excel. So we have to use the combination of several functions or the 2-step process of using **Name Manager** & formula.

Let’s dive into the methods.

### 1. Using the Combination of INDEX & FIND Function to Get Sheet Name List

If you want to find the list of sheet names using several functions, you may use the formula containing the **INDEX**, **LEFT**, **MID**, and **FIND** functions.

Follow the steps below.

**Steps:**

⏩ Click on the **Formulas** tab > **Name Manager** option from the **Defined Names** ribbon.

⏩ Then you’ll see a dialog box namely **Name Manager**

⏩ Press on the **New** option.

⏩ Insert the **Name **(here the name is **Worksheets**) and the below formula in the **Refers to** section.

`=GET.WORKBOOK(1) & T(NOW())`

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

⏩ After that enter the following formula in the **B5 **cell where you want to get the sheet names.

`=INDEX(MID(Worksheets,FIND("]",Worksheets)+1,255),ROWS($B$5:B5))`

⏩ Finally, if you press **Enter **and use **Fill Handle Tool** for the below cells, you’ll get the list of sheet names like the following.

### 2. Utilizing INDEX Function along with REPLACE Function (Automated List)

Again, you can insert the below formula in the **Refers to **section and the **Name** as **SheetNames** after clicking the **New **option from the **Name Manager** dialog box.

`=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")`

Now, insert the formula where you want to get the list.

`=INDEX(SheetNames,B5)`

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

### 3. Applying TRANSPOSE Function to Find Sheet Name List

Furthermore, you can apply the **TRANSPOSE **function which returns a horizontal cell range as a vertical cell range, or vice versa.

Before doing that make sure the **Name** is **GetSheetNames **and insert the below formula.

`=GET.WORKBOOK(1)`

Then insert the following formula.

`=TRANSPOSE(GetSheetNames)`

Eventually, you’ll get the following output.

### 4. Using the LOOKUP Function to Get the Sheet Name List

Before using the **LOOKUP function**, create a new name where the **Name **may be **Sheets **and the formula in the **Refers to** section.

`=GET.WORKBOOK(1)&T(NOW())`

Note: The same macro-enable formula is used in the first method (Name: **Worksheets**), you can also utilze that if you want.

Then insert the formula.

`=LOOKUP("xxxxx",CHOOSE({1,2},"",INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROWS(B$1:B1))))`

If you press **Enter **and use the **FIl Handle Tool**, you’ll get the following output.

### 5. Creating a Dynamic List of Sheet Names Using SUBSTITUTE Function

Moreover, you may create a dynamic list of sheet names using the **SUBSTITUTE** function.

Fix the name as **Dynamic_List_of_SheetNames **and put the below formula.

`=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")`

Afterward, insert the following formula.

`=INDEX(Dynamic_List_of_SheetNames,B5)`

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

### 6. Sheet Name List Using VBA Code

Now, let’s see how you can apply the **VBA **code to get the list of sheet names in excel.

**Step 1: **

Firstly, open a module by clicking **Developer**>**Visual** **Basic**.

Secondly, go to **Insert**>**Module**.

**Step 2: **

Then, copy the following code in your 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
```

**Step 3:**

Finally, run the code.

**Notes:**

Be careful about the following facts while 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.

After running the code, the output will be as follows.

**Read More: ****How to Search Sheet Name with VBA in Excel (3 Examples)**

## Things to Remember

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

II. Be careful about the **#BLOCKED error** which may be found instead of the output if the workbook is unable to update.

## Conclusion

In the above article, I tried to cover the methods to get the list of sheet names. Obviously, I believe these methods might be beneficial for you. Anyway, if you have any queries and suggestions, please don’t forget to share them in the following comments section.

## Related Articles

**How to Search Sheet Name in Excel Workbook (2 Methods)****Apply Sheet Name Code in Footer in Excel (3 Ways)****How to Select Sheet by Variable Name with VBA in Excel (2 Ways)****Use Sheet Name Code in Excel (4 Applications)****How to Rename Sheet in Excel (6 Easy and Quick Methods)****Rename Sheet with VBA in Excel (Both Single and Multiple Sheets)**