How to get sheet name in Excel

Working in different sheets within the same workbook is quite common in Excel. Often we rename these worksheets as per our requirements. But what if we want to fetch the name of a worksheet we are working on? Is it feasible? Indeed, it is! All you have to do is to refer to the sheet name, and cell address and excel will fetch the cell data of that respective sheet for you.

Now the question arises of how to get the sheet name in Excel. Though there is no direct formula to get the sheet name, as we know, we can customise any formula. In this tutorial, we will discover the different methods to fetch the active worksheet name in Excel.

Formula to fetch the active Worksheet Name

There are some essential day-to-day tasks in Excel for which Microsoft has not introduced any built-in function, and one such job is to get the sheet name. The first solution to this problem is to customise our own formula using multiple functions.

To create a formula to fetch the worksheet name, we will merge three Excel functions, i.e., CELLS, FIND, and MID.

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

You can enter the above formula in any Excel worksheet for which you want to fetch the sheet name. To understand the concept of the above formula below given is the step-by-step implementation of the formula that you can use in the worksheet to get the sheet name:

Step 1: Specify CELL function

Start the formula with the '=' sign. Use the built-in CELL formula to return the complete path, workbook name and active worksheet name.

This function will ask for two parameters. In the first parameter specify the info type that you want to return (in our case its "Filename"), and in the second parameter mention the cell address.

Formula use: =CELL ("filename", A1)

The above formula will return the following output.

NOTE: Instead of the above cell reference A1, you can refer to different cell address as well within the first worksheet. It will also return the same worksheet name.

Step 2: Merge above function with FIND

As you can see in the above result, the sheet name is after ']' bracket. Therefore, we will use the FIND function to look for the position of the right bracket.

We will start the formula with equals to followed by the FIND.
In its parameter, firstly we will specify the value want to look for i.e., "]", we will incorporate the CELL function to return filename within which we want to find the position of the character.
Next, we will add 1 to the returned position to get the starting position of the first character of the sheet name.

Formula Used: = FIND("]",CELL("filename"))+1

Step 3: Extract a substring using MID function

The last step is to worksheet name. To incorporate this, we will use the MID function, starting from the middle of the string.

We will again start the formula using = equals followed by the MID function.
It will ask for three parameters, and in the first parameter, it will ask to specify the text from where we are supposed to extract the substring. So here, we will utilize the formula of STEP-1.
In the second parameter, it will ask the position from where it has to start the searching. In this, we will utilize the formula of STEP-2.
In the third parameter, we will specify the position to which we want to fetch the string. We will put 131 (as that could be the maximum length of a worksheet name).

Formula Used: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,131)

Explanation: The above formula will start extract a substring at position 104 and will fetch all the characters till position 131.

The above formula will return the following output.

Step 4. To fetch the name of another worksheet

To get the name of the second worksheet, simply refer to any cell on the second worksheet.

Formula Used: =MID(CELL("filename",Sheet3!A1),FIND("]",CELL("filename",Sheet3!A1))+1,131)

User Defined Formula using VBA

Creating a user-defined formula is the simplest thing to do in Excel. Many of you might find the above formula a bit complex, and sometimes, it might land up with errors or wrong output. So writing the formula is not the only option you have. You don't need to enter a complex formula but instead can do it with a simple user-defined code using Excel VBA.

Now let's cover the various steps to create a user-defined formula and how we can implement this formula in our Excel worksheet to fetch the active worksheet's name in a cell.

To understand the concept of the above formula below given is the step-by-step implementation of the formula that you can use in the worksheet to get the sheet name:

The First step is to enable the Developer tab in Excel. You can either use the shortcut 'Alt+ T' or from the Excel ribbon tab go to the Developer tab -> Visual Basic option.

You can opt any of the option and it will open the Visual Basic Editor Window.
Next to start with the code we can to insert a module in the Visual Basic Editor. Therefore, from header click on the Insert option and from the dialog window click on the Module.
You will have the following window. Next, we have to write a code. Copy and paste the following code in the module window.

Code:

Function mysheetname()
Mysheetname = ActiveSheet.Name
End Function

The above code will successfully create a function using which you can get the name of your current worksheet.
After that close the VBA window and return back to the active worksheet for which you want to fetch the name.
Select any of the cells in your worksheet and unlike Excel inbuilt functions type the user-defined function.

Formula Used: =mysheetname()

Once done press the enter button. Excel will return the following output.

Within three lines of code, we covered the entire formula. Wasn't that much simpler and more accessible than the above mid formula? If you don't want to be involved in complex formulas, you can also use VBA to quickly solve any Excel problem; all you need is a little coding knowledge and a handful of practice.