• Dave Foord
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,624 other followers

  • Dave Foords Twitter

  • Advertisements

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.


20 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: http://www.ironpaper.com/webintel/articles/fix-wordpress-quotes-by-displaying-straight-quotation-marks/#.VS-CCpOzjdU

  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)

  8. […] The mechanism also uses a technique to display the sheet name in a cell in the spreadsheet. More details on this can be found at https://davefoord.wordpress.com/2015/04/16/how-to-display-the-sheet-name-in-a-cell-in-an-excel-sprea… […]

  9. its absolutely fine. It did work for me. Thanx for the tip
    P K Mohanty

  10. The formula does not seem to work in Excel Online. Can anyone help with this?

    • Yes – you are correct, it doesn’t look like this formula works in Excel online, which is a real shame, and I don’t know the solution. I hope that someone else does.

  11. I am hearing that in Excel Online there are a lot of limits to functions as well as items like Pivot Table and Slicers as well.

  12. I was wondering why this formula didn’t work for me (I have previously used it). Your tip on having a “saved file” is what made the difference. Thanks!!!

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: