How to display the sheet name in a cell in an Excel spreadsheet

I use Excel a lot, not just for crunching numbers, but for creating teaching resources, lesson planning, managing my accounts and invoices and various other uses. One feature that I often use, is the ability to have the sheet name appearing inside a cell in the spreadsheet – so for example with my invoices – I rename the sheet name with the invoice number, this then updates the invoice within the sheet.

To do this I use the following formula below.

This may seem a complex formula, and it doesn’t matter if you don’t fully understand it (I don’t), you just need to copy and paste this into a cell in the spreadsheet, and the sheet name will appear. If you change the sheet name, the cell will change accordingly.

The only caveat is, that the workbook has to have been saved at some point for this to work – so if you do this with a new workbook, it won’t work until it has been saved.

This technique is unique to Microsoft Excel, it doesn’t work with other spreadsheet tools such as Open office, Google sheets or Apple’s Numbers.

12 Responses

  1. Thanks, Dave, for this useful formula. However, it did not work immediately on my system because copying and pasting from your blog produced ‘left’ and ‘right’ double quotation marks (unicode U+201C and U+201D to be boring) rather than ‘neutral/vertical’ quotation marks (Unicode U+0022), which I think is what you need within Excel functions. So I had to replace all the quotation marks – then it worked perfectly. I am not sure if this is a problem caused by my setup or maybe something that WordPress has done. Here is the formula with – I hope – the correct type of quotation marks around “filename”:


    Btw, I am in the middle of working on a project that uses OpenOffice Calc rather than Excel, and (for what it’s worth) there is an equivalent function in OO Calc:


    The only problem is that unlike Excel the result does not immediately update if you change the sheet name after using the formula. It only changes after you have closed and reopened the sheet or if you press Ctrl+Shift+F9, which forces all Functions to run again in Calc.

  2. Woops! WordPress seems to have changed my neutral quotation marks to left and right as well, making them unworkable in formulas. This seems to be a known issue in WordPress. See:

  3. Hi Terry – thanks for spotting my mistake. I have embedded a Google Doc which contains the code into the blog, which doesn’t look as tidy, but does now work.

    Thanks also for the tip for the Open Office alternative – annoying that it only updates when you close and open the sheet though.

  4. this didn’t work until I array entered it. cmd-enter Mac 2016.
    the square bracket was the clue.

    • Excellent – glad it worked and thanks for sharing the Mac tip

    • Hi, I have been trying to enter this a million of times on my MAC, (copying, manually, different examples I found online) but it keeps telling me there is an error in my formula: Highlighting the “filename” saying : CELL(info_type,[Reference]) with info_type in bold. Please does anybody knows what is happening here? )

  5. This worked perfectly for me and I have tried several different formulas posted that did not work. Thank you so much!

  6. awesome !!!

  7. Dave, what about Excel Online? This does not seem to work there (since I guess there’s no filename to refer to there)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: