Written by Allen Wyatt (last updated June 12, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
There may be instances when you are developing a worksheet and you need to reference the name that you have assigned to the worksheet. (This is the name that appears on the worksheet tab, at the bottom of the Excel window.) Unfortunately, Excel does not include any intrinsic functions to do this. You can create such a function, however, by starting with the use of the CELL worksheet function.
If you include the following in a cell, Excel returns the fully path of the workbook, along with the sheet name:
=CELL("filename")
For instance, if you entered this into a cell in the Sheet3 worksheet of the MyBook workbook, the information returned by Excel might be something like C:\My Documents\[MyBook.xls]Sheet3 (depending, of course, on the drive and directory in which the workbook is saved).
To return just the worksheet name from this value, you could use the following in your cell:
=MID(CELL("filename"),(FIND("]",CELL("filename"))+1),50)
This will work for any worksheet name up to 50 characters in length. (If you routinely use different lengths, simply change the value in the expression.)
If you would prefer to use a macro-oriented approach, you can create a full-featured macro that will do the job. The following macro, SheetStuff, will return any of three separate items:
Function SheetStuff(numWanted As Byte) As String Select Case numWanted Case 1 SheetStuff = ActiveSheet.Name Case 2 SheetStuff = ThisWorkbook.Name Case 3 SheetStuff = ThisWorkbook.FullName Case Else SheetStuff = ActiveSheet.Name End Select End Function
To use this macro function, simply put =SheetStuff(X) in a cell in your worksheet. You should replace X with either 1, 2, or 3, depending on the information you want. If you use 1, the name of the current worksheet is returned. If you use 2, then the name of the workbook is returned. Finally, 3 returns the name and full path of the workbook.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2019) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
An Excel workbook can contain quite a few different objects. Sometimes those objects can be hidden so that they are not ...
Discover MoreNeed to click on a cell and have it replaced with an "X"? Macros make it easy to do, as illustrated in this tip.
Discover MoreIf your workbook contains links, you are normally given the opportunity to update those links when you open the workbook. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments