• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Excel Formula to Display the Sheet Name in a Cell

Excel Formula to Display the Sheet Name in a Cell

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.

Here is the full Excel formula to display the sheet name in a cell;

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

Watch the Video

Related Posts:

  • Import Multiple Excel Files with Multiple Sheets in Excel
    Import Multiple Excel Files with Multiple Sheets in Excel
  • Advanced Excel Skills
    Advanced Excel Skills
  • Entering Fractions in Excel
    Entering Fractions in Excel
  • Count the Number of Words in a Cell
    Count the Number of Words in a Cell

Reader Interactions

Comments

  1. Neil Watkins says

    19 July 2016 at 10:29 pm

    Great function. I can get staff members to now remember where they put their files by this function. Thanks

    Reply
  2. Tom says

    6 August 2017 at 2:08 am

    Is there any way to reverse engineer this, so the sheet name is determined by a cell value?

    Reply
    • computergaga says

      6 August 2017 at 7:27 pm

      This can only be done with a macro using a statement such as Activesheet.Name = Range(“A1”).Value

      Reply
  3. Brian S Blackwell says

    26 January 2021 at 4:34 pm

    Sorry, but the filename (workbook name) is not the same as the sheet name inside a workbook.

    “A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.”

    Reply
    • Alan Murray says

      26 January 2021 at 4:42 pm

      I know, Brian. The formula returns the filename and extracts the sheet name from it.

      Reply
  4. Love Rai says

    20 July 2021 at 1:49 pm

    Note :- Formula Work only if your Sheet Name in Numeric
    =”=’E:\Contacts\”&”[“&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”‘!$B$1048576″
    Use this Formula To Get Value from other Excel Sheet
    ________________

    Define Path = “=’E:\Contacts\”&”[”
    Give Sheet Name [if Sheet Name in Numericl] = SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1
    Cell No. = !$B$1048576

    Reply
  5. Johan Jordaan says

    24 July 2021 at 10:12 pm

    Is there a difference between Excel in Windows and Excel on Macbook?

    Reply
    • Alan Murray says

      26 July 2021 at 8:12 pm

      Yes, there are differences between the two. Not in formulas but in other areas especially VBA and Power Query.

      Reply
  6. Excel101 says

    24 September 2021 at 7:27 pm

    Hi! I’m just wondering what’s the meaning of the numbers +1 and 32?

    Reply
    • Alan Murray says

      10 November 2021 at 4:55 pm

      The + 1 is to start from the first character after the “]” and the 32 is a number to just ensure we return enough characters. 32 is more character than we require. We do not know how many characters in the sheet name, but we know 32 is more than enough.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·