Note: The other languages of the website are Google-translated. Back to English

How to reference tab name in cell in Excel?

For referencing the current sheet tab name in a cell in Excel, you can get it done with a formula or User Define Function. This tutorial will guide you through as follows.

Reference the current sheet tab name in cell with formula
Reference the current sheet tab name in cell with User Define Function
Easily reference the current sheet tab name in cell with Kutools for Excel

Reference the current sheet tab name in cell with formula

Please do as follow to reference the active sheet tab name in a specific cell in Excel.

1. Select a blank cell, copy and paste the formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) into the Formula Bar, and the press the Enter key. See screenshot:

Now the sheet tab name is referenced in the cell.

Easily insert tab name in a specific cell, header or footer in worksheet:

The Kutools for Excel's Insert Workbook Information utility helps easily insert active tab name into a specific cell. Besides, you can reference workbook name, workbook path, user name etc. into a cell, worksheet's header or footer as you need. Click for details.
Download Kutools for Excel Now! (30-day free trail)

Reference the current sheet tab name in cell with User Define Function

Besides the above method, you can reference the sheet tab name in a cell with User Define Function.

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. See screenshot:

3. Copy and paste the below code into the Code window. And then press Alt + Q keys to close the Microsoft Visual Basic for Applications window.

VBA code: reference tab name

Function TabName()
  TabName = ActiveSheet.Name
End Function

4. Go to the cell which you want to reference the current sheet tab name, please enter =TabName() and then press the Enter key. Then the current sheet tab name will be display in the cell.

Reference the current sheet tab name in cell with Kutools for Excel

With the Insert Workbook Information utility of Kutools for Excel, you can easily reference the sheet tab name in any cell you want. Please do as follows.

1. Click Kutools Plus > Workbook > Insert Workbook Information. See screenshot:

2. In the Insert Workbook Information dialog box, select Worksheet name in the Information section, and in the Insert at section, select the Range option, and then select a blank cell for locating the sheet name, and finally click the OK button.

You can see the current sheet name is referenced into the selected cell. See screenshot:

  If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

Demo: Easily reference the current sheet tab name in cell with Kutools for Excel

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Download the free trial now!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (24)
Rated 0.5 out of 5 · 1 ratings
Vikas
Vikas
This comment was minimized by the moderator on the site
Thank you very much. :-)
Reply
0
0
David Polacheck
David Polacheck     Vikas
This comment was minimized by the moderator on the site
Thanks all!
Reply
0
0
John
John
This comment was minimized by the moderator on the site
Using the VBA macro, if I change the tab name the value in the cell does not get updated. Am I doing something wrong?
Reply
0
0
crystal
crystal     John
This comment was minimized by the moderator on the site
Dear John,
The formula can't update automatically. You need to refresh the formula manually after changing the tab name.
Sorry about that.
Reply
Report
0
0
Morgan
Morgan     crystal
This comment was minimized by the moderator on the site
Refresh all formulas using the replace tool. Highlight everything, Find "=" (no quotes), Replace with "=" (no quotes). Nothing actually changes but every formula is reloaded.
Reply
0
0
Jeffrey
Jeffrey     Morgan
This comment was minimized by the moderator on the site
Ctrl-Alt-Shift-F9 refreshes all formulas and calculates all cells, including cells not marked as needing to be calculated.
Reply
0
0
INSTINE
INSTINE     John
This comment was minimized by the moderator on the site
Dear John,
for best example let me tell you one thing.
if you want to change your code will be like this.

Function John()
John = ActiveSheet.Name
End Function
Reply
0
0
Alister Hood
Alister Hood     John
This comment was minimized by the moderator on the site
Function TabName()
'Need to make it volatile otherwise it is never recalculated unless we do Ctrl-Shift-F9
Application.Volatile
TabName = ActiveSheet.Name
End Function
Reply
0
0
Alister Hood
Alister Hood     John
This comment was minimized by the moderator on the site
Of course nothing's ever simple with Microsoft products is it?
My solution actually breaks it, because it means everywhere you use the function it gets the name of the sheet where the last change was made, not the name of the sheet where the function is used.
Reply
0
0
bryan
bryan
This comment was minimized by the moderator on the site
Hi, using the =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) formula in desktop excel works, but not in online excel.. can you explain why that is?it returns #value error in the cell.

is there a workaround for this? Thanks!
Reply
Report
0
0
Bob
Bob     bryan
This comment was minimized by the moderator on the site
You are a champ! Nothing like a fix that one can copy and paste and have it work. Thank you.
Reply
0
0
LuisV
LuisV
This comment was minimized by the moderator on the site
FYI, you need to have more than one sheet in your workbook for this to work. Otherwise, the formula will display the filename but no sheet name.
Reply
0
0
crystal
crystal     LuisV
This comment was minimized by the moderator on the site
Good Day,
Thank you for your comment. I have tested the formula in a workbook that has only one sheet, but everything was running smoothly. The formula only displays the sheet name no matter how many worksheets existing in a workbook.
Reply
Report
0
0
Alister Hood
Alister Hood
This comment was minimized by the moderator on the site
Sorry, the comment line is wrong - should be Ctrl-Alt-Shift-F9
Reply
0
0
Kurt
Kurt
This comment was minimized by the moderator on the site
Excellent, thanks! Per: Reference the current sheet tab name in cell with formula
Reply
0
0
vanessa
vanessa
This comment was minimized by the moderator on the site
hello, i am having trouble referencing a sheet name into a cell with online excel
Reply
0
0
vanessa
vanessa
This comment was minimized by the moderator on the site
Hi, I used the above suggestions but the first formula to reference the tab sheet name will not work on "excel" online. The second works, but the "Tabname" keeps showing the same tab name for all tabs on the workbook.
Reply
0
0
crystal
crystal     vanessa
This comment was minimized by the moderator on the site
Hi,
Sorry we didn't test in excel online. Can't help you with that yet. Thank you for your comment.
Reply
Report
0
0
Ailec
Ailec
This comment was minimized by the moderator on the site
What I really want is to name some tabs using with the names on some cells from another sheet (same document of course). How can I do that?
Reply
0
0
Scott
Scott
This comment was minimized by the moderator on the site
If you're using Excel Online. I found a solution for this issue.

You need to use 2 cells to make it work. As long as you have a cell that has the reference of a tab in its name, you can use FORMULATEXT() to turn that cells formula into a string and then extract the name that way. I can't imagine a scenario where you will want to reference another sheet and you wont already have a cell referencing that sheet for information on it, but you can always just make a blank/hidden (however you want to do it) cell that is just referencing the other sheet to set this formula up. In the below example, it is referencing cell B1 and cell B1 just contains =Sheet2!A1 as a reference to the sheet I want to name. If you use a name for a sheet with a space in it (example: Sheet 1) it would show up as (='Sheet 1'A1). I used an IF function and an ISERROR function to search if the string contains a space or not and two MID functions to extract the name of the sheet from both scenarios. If you update the name of the referenced sheet, cell B1 will change its formula and subsequently change the string and output the correct name each time.

=IF(ISERROR(FIND(" ",FORMULATEXT(B1))),MID(FORMULATEXT(B1),2,FIND("!",FORMULATEXT(B1))-2),MID(FORMULATEXT(B1),3,FIND("!",FORMULATEXT(B1))-4))
Reply
0
0
Scott
Scott
This comment was minimized by the moderator on the site
If you're using Excel Online. I found a solution for this issue.

You need to use 2 cells to make it work. As long as you have a cell that has the reference of a tab in its name, you can use FORMULATEXT() to turn that cells formula into a string and then extract the name that way. I can't imagine a scenario where you will want to reference another sheet and you wont already have a cell referencing that sheet for information on it, but you can always just make a blank/hidden (however you want to do it) cell that is just referencing the other sheet to set this formula up. In the below example, it is referencing cell B1 and cell B1 just contains =Sheet2!A1 as a reference to the sheet I want to name. If you use a name for a sheet with a space in it (example: Sheet 1) it would show up as (='Sheet 1'A1). I used an IF function and an ISERROR function to search if the string contains a space or not and two MID functions to extract the name of the sheet from both scenarios. If you update the name of the referenced sheet, cell B1 will change its formula and subsequently change the string and output the correct name each time.

=IF(ISERROR(FIND(" ",FORMULATEXT(B1))),MID(FORMULATEXT(B1),2,FIND("!",FORMULATEXT(B1))-2),MID(FORMULATEXT(B1),3,FIND("!",FORMULATEXT(B1))-4))
Reply
Report
0
0
petercat
petercat
This comment was minimized by the moderator on the site
The formula at the top won't work until you save the file to your hard drive.
Reply
0
0
Julia
Julia
This comment was minimized by the moderator on the site
sorry your formulas and everything are incorrect on this page.
after direct copy/paste your THEORIES were WRONG
ZERO OUT OF TWO ATTEMPTS WORKED USING YOUR SUGGESTIONS
Reply
0
0
Rated 0.5 out of 5
crystal
crystal     Julia
This comment was minimized by the moderator on the site
Hi Julia,
What kind of erro did you get?
To be mentioned, the formula provided in the post can only be applied in English system environment Excel. If you have Excel in a different language than English, please convert the formula from English to the language you are currently using.
You can translate the formula in this page: https://de.excel-translator.de/translator/
Reply
Report
0
0
There are no comments posted here yet

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL