Where is the worksheet name in Excel?

This blog post looks at using an Excel formula to display the sheet name in a cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet name.

For the formula we will be using the CELL, MID and FIND functions. Let’s begin by looking at the CELL function.

The CELL function is a fantastic, and relatively unknown, function in Excel. Its purpose is to return information about a cell such as its column, cell colour, whether it is in a protected state or the filename. We need to use the function to return the filename of a cell. Because our goal is to return the sheet name, it does not matter what cell we use. Any cell on the sheet will work. In the example below, cell B1 has been used.

=CELL("filename",B1)

The function above will return the full filename of the cell such as;

C:\Users\Trainee1\Desktop\[return-sheet-name.xlsx]London

Returning Just the Sheet Name to a Cell

Now that we have the filename, we need to extract just the sheet name. The MID and FIND functions will be used for this.

The FIND function is used to return the position of the closing “]” + 1. The “]” indicates the end of the workbook reference, so the following characters are those for the sheet name.

The MID function then extracts up to the next 32 characters. This is an excessive number but because sheet names cannot have more than 31 characters it ensures the full name is returned whatever it may be.

In Excel there isn’t any one function to get the sheet name directly. But you can get a sheet name using VBA, or you can use the CELL, FIND, and MID functions:

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

Let’s go through the above formula.

CELL Function

We can use the CELL Function to return the file path, name, and sheet by inputting “filename”.

=CELL("filename")

To get the current worksheet’s name, you can use the function with or without the optional reference argument, referring to any cell on the current tab.

=CELL("filename",A1)

You can also get information about any other worksheet by referring to a cell on that sheet.

=CELL("filename",AnotherSheet!A1)

FIND Function

As shown above, the CELL Function returns the file path, name, and worksheet. We don’t need the path or workbook name, so we need to isolate the sheet name.

First use the FIND Function to determine the position of the character immediately before the sheet name (which is always “]”).

=FIND("]",B3)

MID Function

Since we have the last character position before the worksheet name, we can now use the MID Function to extract the characters after that position.  Our sheet name starts at the position found above + 1, but we don’t know how long the sheet name is. However, we do know the maximum length of a worksheet name (31), and we can use that in the MID Function:

If you can't see the worksheet tabs at the bottom of your Excel workbook, browse the table below to find the potential cause and solution.

Note: The image in this article are from Excel 2016. Your view might be slightly different if you have a different version, but the functionality is the same (unless otherwise noted).

Cause

Solution

The window sizing is keeping the tabs hidden.

• If you restore multiple windows in Excel, ensure that the windows are not overlapping. Perhaps the top of an Excel window is covering the worksheet tabs of another window.

  

• The status bar has been moved all the way up to the Formula Bar.

  

• Tabs can also disappear if your computer screen resolution is higher than that of the person who last saved the workbook.

Try maximizing the window to reveal the tabs. Simply double-click the window title bar.

If you still don't see the tabs, click View > Arrange All > Tiled > OK.
 

The Show sheet tabs setting is turned off.

First ensure that the Show sheet tabs is enabled. To do this,

• For all other Excel versions, click File > Options > Advanced—in under Display options for this workbook—and then ensure that there is a check in the Show sheet tabs box.

  

The horizontal scroll bar obscures the tabs.

Hover the mouse pointer at the edge of the scrollbar until you see the double-headed arrow (see the figure). Click-and-drag the arrow to the right, until you see the complete tab name and any other tabs.

The worksheet itself is hidden.

To unhide a worksheet, right-click on any visible tab and then click Unhide. In the Unhide dialog box, click the sheet you want to unhide and then click OK.

Where is the Excel sheet name?

Where does the worksheet name appear?