Insert Sheet Name In Cell: Easy! 3 Methods to Return the Worksheet Name
Often, you need to insert and work with the sheet name in an Excel sheet, for example if you are working with the ‘INDIRECT’-formula. Or, if you want to dynamically change headlines depending on the sheet name. If you don’t want to type the sheet name manually – which is very unstable – there are three ways to get a sheet name
Before we start: If you just have to insert the sheet name for a small amount of worksheets, please consider doing it manually. It usually is the fastest way.
Contents
Method 1: Insert the sheet name using built-in Excel functions
The easiest way is to copy the following function and paste it into your Excel cell:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))This formula obtains the filename “=CELL(“filename”,A1)” and separates it after the character “]”. If you want to get the name of another Excel sheet, you have to change the cell reference from “A1” to any cell of the other worksheet. And depending on your version and language of Excel, you might have to translate the function names and maybe replace “,” by “;”.
The big advantages of this method is that it doesn’t require any programming in VBA or a third-party Excel add-in.
On the downside, please note the following comments:
- If you open a new file and paste this function, it won’t work before saving it.
- The cell function is volatile. That means, it always calculates no matter if you’ve changed anything. This is a disadvantage for large Excel files where the performance of calculation is crucial.
- Also, the cell function doesn’t translate to other languages. If your Excel is set to German, Spanish etc., you have to replace the “filename” part with the respective word in your language.
Method 2: Return the sheet name using VBA
The second way to get a worksheet name uses VBA. Once set-up, it might be easier to use. You have to open the developer tools and add a module. Next, you can use this code:
Function PROFEXSheetName(cell As Range) As String
'This function returns the name of a selected worksheet
'"cell" contains the link to a cell on the worksheet
Application.Volatile
'With "Application.Volatile" you can make sure, that the function will be recalculated once the worksheet is recalculated
PROFEXSheetName = cell.Worksheet.Name
'Returns the worksheet name
End FunctionNow, you can use the function “=PROFEXSheetName(A1)” in your Excel cell and the worksheet name will be shown.
As the result of both ways is (or should be…) the same, it is up to you which way to choose. If you feel comfortable using an easy macro, you probably go for the second way. If not, just paste the formula from the first way to your cell.
Method 3: Use ‘Professor Excel Tools’
Because returning the sheet name is a common problem in Excel, it’s included in the Excel add-in ‘Professor Excel Tools’. Just type =PROFEXSheetName(A1) and it’ll return the sheet name. Download the free trial below.
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
Example: Take it a step further: Insert and clean a worksheet name
I usually recommend not using space characters in worksheet names. It has some disadvantages, especially when linking to other worksheets (read more about worksheet names here).
So, I usually use underscore “_”-characters to separate words in sheet names. Let’s assume the following example: The worksheet name should at the same time be displayed in a heading. Here is how to do it, using the method one from above.
The approach: Wrap the substitute function around the sheet name function:
So, the function is:
=SUBSTITUTE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))),"_"," ")
Download example sheet and further reading
Please download the example file here. It contains examples for the methods two and three from above.
You don’t want to insert the worksheet name but rather the whole path, file name etc.? Check this article. It has everything, you need!
Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
