Summary

To get the name of the current worksheet (i.e. current tab) you can use a formula based on the CELL function. CELL retrieves the workbook name and sheet, and the MID and FIND functions are used to extract just the sheet name. In the example shown, the formula in E5 is:

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

Generic formula

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

Explanation 

The CELL function is used to get the full file name and path:

CELL("filename",A1)

The result looks like this:

path[workbook.xlsm]sheetname

CELL returns this result to the MID function as the text argument. The sheet name begins just after the left bracket, so the starting position is calculated with FIND:

FIND("]",CELL("filename",A1))+1

The number of characters to extract is hardcoded as 255. In the Excel UI, you can't name a worksheet longer than 31 characters, but the file format itself permits worksheet names up to 255 characters, so this ensures the entire name is retrieved.

Alternative with RIGHT

You can also use the RIGHT function to extract the sheet name, instead of MID:

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

This requires more function calls, but works just as well.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.