How to make Excel Formula Sheet Name in Excel

Some of the most compelling reasons to use Excel involve multiple tables that share information and interact with each other for this we make more sheets in excel to navigate easily. The standard names Excel assigns to new worksheetsSheet1, Sheet2, Sheet3, and so on aren't very helpful for identifying what they contain. And they become even less helpful if you start adding new worksheets since the new sheet numbers don't necessarily indicate the position of the sheets, just the order in which you created them. Excel doesn’t expect you to stick with these auto-generated names. Instead, you can rename them by right-clicking the worksheet tab and selecting Rename or just double-click the sheet name. Either way, Excel highlights the worksheet tab, and you can type a new name directly onto the tab.

Get Excel Formula Sheet Name

We can use the CELL Function to return the file path, name, and sheet by inputting 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.

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

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)

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

CELL (filename, A1)

path[workbook.xlsm] sheet name

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

Alternative for Excel Formula Sheet Name 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.

Get full workbook name and path

The CELL function can return various information about a worksheet. CELL can get things like address and filename, as well as information about the formatting used in the cell. The type of information to be returned is specified by the info type...

Excel formula Sheet Name: Get workbook name and path without sheet

Get workbook name and path without sheet

In this example, the goal is to get the workbook name and path without the sheet name included. The formula in E5 is: = SUBSTITUTE (LEFT (CELL (filename, A1), FIND (], CELL (filename, A1)) - 1), ...

Excel formula: Get workbook name only

In this example, the goal is to get the workbook name only without the path or sheet name included. The formula in E5 is: = MID (CELL (filename, A1), FIND ([, CELL (filename, A1)) + 1, FIND (] ...

Excel formula: Worksheet name exists

The ISREF function returns TRUE for a valid worksheet reference and FALSE is not. In this case, we want to find out of a particular sheet exists in a workbook, so we construct a full reference by concatenating the sheet names in column B with an...

The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info type. CELL can get things like address and filename, as well as detailed info about the formatting used.

The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID(apple,2,3) returns ppl.

The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.

Did you learn about how to make Excel Formula Sheet Name in Excel Spreadsheets? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.