Suppose you want to display the sheet name in the header and footer, create a dynamic formula referencing multiple worksheets, or automate Excel tasks that involve different worksheets. For all these tasks, you must learn how to find the sheet name in Excel.

Microsoft Excel worksheets enable you to create different calculations and data analytics in the same workbook. Using worksheets, you can segregate the data analytics and visualization tasks of a specific project.

Though all the datasets, their calculations, and visualizations are in the same workbook, the Excel file still stays organized due to the fact that you can create hundreds of worksheets to segregate your data.

When the workbook becomes large and it consists of hundreds of worksheets, it can be truly challenging to remember the names of these worksheets. Here comes the sheet name feature of Excel to ease your work.

Keep reading as I show you several ways of finding sheet names in Excel using manual and automatic methods.

Reasons to Find Sheet Name in Excel

You can create dynamic worksheet name views using the sheet name feature in Excel. Here’s an example to help you understand:

Suppose, you created an index of the Excel workbook manually. However, after a few weeks, you had to change the names of a few worksheets. The index won’t update itself automatically. Anyone using the workbook referencing the outdated index will surely make navigational and programmatic errors in the workbook.

To avoid this issue, you can use sheet names in Excel to create dynamic worksheet name views. When someone changes the worksheet name, the index will update automatically.

Find below other important justifications for learning this basic Excel skill:

1. You might want to find the sheet name to automate processes, such as creating customized reports or macros that perform actions on specific worksheets.
2. If you’re setting up custom headers or footers for printing, you can use the sheet name to dynamically display the sheet name on each sheet.
3. In data validation rules or conditional formatting, you can reference the sheet name to apply specific rules or formatting based on the sheet’s name.
4. If you create a dashboard or summary sheets that consolidate data from other sheets, the sheet name can help you reference and pull data from the relevant sheets.
5. For auditing and traceability purposes, you can use the sheet name to track changes and actions performed on specific sheets.
6. When importing or exporting data to and from Excel, the sheet name can be used to specify the target sheet.

So far, you’ve found out what’s sheet name in Excel and why you must learn how to find it. Great! Let’s now explore several methods to get the sheet name below:

Add Sheet Names Using the Header & Footer Command in Excel

The Header & Footer command is the built-in user interface function that allows you to add the workbook name and worksheet name when printing or exporting Excel spreadsheets as PDFs. Find below the steps to follow:

1. Go to the Excel worksheet in which you want to add the sheet name and file name as header and footer elements.
2. Click the Insert tab and find the Header & Footer command inside the Text block.
3. Click the Header & Footer command.

4. Select the File Name and Sheet Name items in the Header & Footer Elements block.
5. These will be added in the header as the &[Tab] and &[File] codes.

6. Click anywhere on the Excel spreadsheet except the header section.
7. Now, scroll down to the footer section.
8. Click the Footer and add the items you want to add here from the Header & Footer Elements block.
9. Repeat the steps for all other worksheets, if needed.
10. Print the workbook.

The header and footer elements added this way are dynamic content. When you change the worksheet name, the changes will also be reflected in the header and footer sections.

Find Sheet Name with the CELL Formula in Excel

Suppose you need to show the current worksheet name in any cell on the active sheet. What you can quickly do is manually write down the name by copying it from the worksheet tab and pasting the name as a text string in any cell.

However, when you change the worksheet name in the future, Excel won’t be able to update the name automatically. Instead, you can use the following formula that adds a worksheet name in any cell in the active worksheet dynamically.

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))

When you change the active worksheet name, Excel will be able to update the same name in the designated cell.

Here’s how you can create a dynamic worksheet index in your Excel workbook by using the above formula:

1. Populate dynamic views of worksheet names for all the worksheets in cell A1 except for the one where you want to create the sheet index by using the above formula.

2. Now, go to the target worksheet and create a column named Worksheet Index.
3. In the first cell below the column header, copy and paste the above formula to populate the name for the target worksheet.

4. Now, use the following formula to add the rest of the worksheet names:

=Operations!A1

5. Replace Operations with the actual worksheet names on your workbook in the above formula.

Get Sheet Name in an Excel Cell Using the MID Function in Excel

If the above formula doesn’t work for you or you’re looking for an alternative, you can use the following formula:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

This function will also view the active worksheet name in the selected cell dynamically.

Create a User-Defined Function to Find Sheet Names in Excel

Since Excel doesn’t offer any dedicated function to find sheet names, you can create one yourself. Excel’s ability to accept user-defined functions or UDFs will help you here. Here’s how:

1. Press Alt + F11 to bring up the Excel VBA tool.
2. There, click on the Insert button and choose the Module option.
3. Inside the blank module, copy and paste the following script:

Function GetSheetName()
    GetSheetName = Application.Caller.Worksheet.Name
End Function

4. Click the Save button.
5. On the warning message that pops up, press No.

6. Choose the XLSM file in the Save as type drop-down menu of the Save As dialog that pops up.
7. Now, close the Excel VBA Editor.

When working in the same workbook in the future, in any cell, just type this formula and Excel will populate the worksheet name instantly:

=GetSheetName()

The formula syntax depends on the function name you use in the Excel VBA script.

Find Sheet Names in Excel Using Excel VBA

The best way to find sheet names the way you want using various types of Excel VBA scripting. Find below three different scripts and scenarios that you should know:

Display Sheet Names in a Message Box Using VBA

Suppose you want to create a VBA macro button and place it on the Excel worksheets to view sheet names when needed. You can do so by following the instructions mentioned below.

Create the DisplaySheetName VBA macro by using the following script:

Sub DisplaySheetName()
    Dim sheetName As String
    sheetName = ActiveSheet.Name
    MsgBox "The current sheet name is: " & sheetName
End Sub

You can follow the steps mentioned in the previous section to learn how to create a VBA macro in Excel.

Once you’ve successfully created the macro, follow these steps:

1. Go to the Developer tab on the Excel ribbon menu.
2. Click the Insert command inside the Controls block and choose Form Controls > Button.
3. The mouse cursor will turn into a crosshair so you can draw a button.
4. Draw the button on a cell of your choice.

5. Once you’re done, the Assign Macro dialog will pop up.
6. There, choose the DisplaySheetName and click OK.

That’s it. You can now copy and paste this VBA macro button on other worksheets. Whenevre you or another user need to know the current name of the active worksheet, just click the button.

Fetch All Sheet Names in an Excel Workbook Using VBA

You can use the following Excel VBA script to generate a worksheet index in your workbook. Write the CreateSheetIndex macro in your workbook and execute it.

Sub CreateSheetIndex()
    Dim ws As Worksheet
    Dim sheetIndex As Worksheet
    Dim cell As Range
    Dim lastRow As Long

    ' Set the target worksheet (SheetIndex)
    On Error Resume Next
    Set sheetIndex = ThisWorkbook.Sheets("SheetIndex")
    On Error GoTo 0

    ' Check if the target worksheet exists, create it if not
    If sheetIndex Is Nothing Then
        Set sheetIndex = ThisWorkbook.Sheets.Add(, ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        sheetIndex.Name = "SheetIndex"
    End If

    ' Clear existing content in the worksheet
    sheetIndex.Cells.Clear

    ' Add the header "List of Worksheets"
    sheetIndex.Cells(1, 1).Value = "List of Worksheets"
    lastRow = 2

    ' Loop through each worksheet and add its name to the list
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "SheetIndex" Then
            Set cell = sheetIndex.Cells(lastRow, 1)
            cell.Value = ws.Name
            lastRow = lastRow + 1
        End If
    Next ws
End Sub

The code will add a new worksheet named SheetIndex where you’ll find all the spreadsheets of the Excel file.

The worksheet index will update automatically if you change the sheet names. You just need to run the macro each time you open the workbook to fetch the latest worksheet names.

Find Sheet Names in Excel Using Office Scripts

While Excel VBA is only available on Excel desktop apps, Office Scripts is available on both Excel for Microsoft 365 desktop and Excel for the web apps.

Also, you can use Office Scripts to further automate your tasks on Microsoft Power Automate.

Therefore, when you need to create an automated way to get sheet names in Excel, you must try out Office Scripts as well. Find below the script and the steps to implement the script:

1. Go to the Automate tab.
2. Click on the New Script button inside the Scripting Tools command block.
3. Now, copy and paste the following Office Scripts code inside the Code Editor pane:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range A1 on selectedSheet
	selectedSheet.getRange("A1").setFormulaLocal("=RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-SEARCH(\"]\",CELL(\"filename\",A1)))");
}

4. Click the Save script button to save it.
5. Click on the Run button to execute the script.
6. You’ll see the sheet name in A1.

Note: Microsoft has limited the access to Office Scripts feature only to Microsoft 365 subscribers who own Microsoft 365 Business Standard or higher plans. If you don’t see the Automate tab, you aren’t eligible to use this feature.

Conclusions

I’ve mentioned 6 different methods to find sheet names in Excel with minimum effort. You can also remember the steps of these methods easily.

You can use the Header & Footer command, the CELL function, and the MID function if you occasionally need to get sheet names. These are the easiest ones you can begin with.

If you like to create automatic programs in Excel using coding, you can choose two VBA-based methods, one is the user-defined function and another using Excel VBA subroutines.

Finally, if you need to use advanced automation in Excel using other Microsoft tools like Microsoft Power Automate, you can use the Office Scripts-based method to get sheet names.

Did this article help?

Do you have a tip you want to share?

Let me know by writing few lines in the comment box below.