Get the Sheet Name in Excel (Formula)

by

To show the current sheet’s name in a cell, for a report title or a header, use CELL(“filename”) and take everything after the closing bracket. In Excel 365 it’s a short TEXTAFTER formula.

Excel 365TEXTAFTER + CELL
Any versionMID + FIND + CELL
NeedsThe workbook to be saved

Excel 365

=TEXTAFTER(CELL("filename",A1),"]")

Any version

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

31 is the longest a sheet name can be.

How it works

CELL("filename",A1) returns the full path, the workbook name in square brackets, then the sheet name:

CELL filename returning the path, file name and sheet name

FIND locates the ], here at position 20, and MID returns everything after it.

FIND returning the position of the closing bracket
Sheet name extracted with MID

Things to know

  • Always include a cell reference like A1. Without it, CELL returns the name of whichever sheet was last changed, so every sheet can show the same name.
  • The workbook must be saved; in a new, unsaved file CELL returns nothing.
  • To get the workbook name: =TEXTBEFORE(TEXTAFTER(CELL("filename",A1),"["),"]").
  • To list all sheet names, use the list all worksheets macro.

Related