How to Get Sheet Name in Excel (Formula)

In Excel, there is no direct function to get the sheet name of the active sheet. Now the solution to this problem is to create a formula using multiple functions or to use a custom function created using the VBA.

In this tutorial, you will learn both methods with examples.

Use a Formula to Get the Worksheet Name

To create a formula to get the worksheet name we need to use CELLS, FIND, and MID function. Following is the function where you can get the sheet name.

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

You enter the above formula in any of the cells in the worksheet for which you want to have the sheet name. Now let’s understand this formula, and to understand this we need to break it up into four parts.

In the first part, we have a CELL function that returns the address of the workbook along with the current sheet’s name.

And, following is the address that we got from the cell function. Here you can see you have the sheet name at the end of the address, and you need to get the sheet name from it.

Now in the second part, we have the FIND function that uses the cell function to get the address and find the position of the character that you have exactly one position ahead of the sheet name.

And once you get the position number of “]”, you need to add 1 into it to get the position of the first character of the sheet name.

Now in the third part, you have the LEN and CELL functions to count of the characters in the entire path.

Now at this point, we have the address path, the position of the first character of the sheet name, and the count characters that we have in the address path.

And in the fourth part, by using the MID function you have got the sheet name in the result.

Create a User-Defined Function to Get Sheet Name

Getting a sheet name by a UDF is the easiest way. You don’t need to create a complex formula, but a simple code like the following.

Function mySheetName()
mySheetName = ActiveSheet.Name
End Function

Now let’s learn how we can use this code to extract the current worksheet’s name in a cell. Use the following steps:

1. First, go to the Developer tab and click on Visual Basic.
   
2. Now in the Visual Basic Editor, go to the Insert option and click on the Module to Insert a Module.
   
3. After that, go to the code window and paste the above code there.
   
4. In the end, close the visual basic editor and come back to the worksheet.

Now, select any of the cells in the worksheet for which you want to get the name and enter the following function there.

You can learn more about creating a custom function from this tutorial.