change language:

In Excel it can be useful to display the worksheet name in a cell of that worksheet. For example, to use this name in the first row of a worksheet as the title of that worksheet. By default, Excel has no function to put the name of a worksheet in a cell, but with a smart combination of three functions this is possible. To display the worksheet name in a worksheet worksheet cell, the following function can be used:

=MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1),1)+1,99)

This function gives an error message if the file has not yet been saved and therefore does not have a file name.

Short explanation

The function CELL("filename",A1) displays the full path with the file name including the worksheet name. The name of the file is shown in square brackets, after which the worksheet name follows immediately.

The SEARCH function determines the position of the right square bracket, then the MID fiunction displays the text from this position plus 1 to the end. In fact, the text is displayed with a maximum length of 99, but because a worksheet name in Excel can only contain a maximum of 31 characters, that is more than enough.

A cell reference must be added to the CELL function (A1 in this case, but it may be another cell) to ensure that the sheet name of the current worksheet is displayed. If no cell reference is included, the sheet name of the last modified cell will be displayed, which can be a cell on another worksheet.

If you don't have an English Excel version, the 'filename' option cannot be chosen from the drop-down list while entering the CELL function. In Dutch, for example, this option is called 'bestandsnaam'. However, although the 'filename' option cannot be chosen in this version, this value can be entered. The 'filename' option works in any language version, while the local values such as 'bestandsnaam' only work in the local language version.

Questions / suggestions

Hopefully this article helped you to display the worksheet name in a cell. If you have any questions about this topic or suggestions for improvement, please post a comment below.

arrow_up