Your Sheet Names Are Killing My Formulas

Have you read all the Excel horror stories and costume ideas in the Very Scary Fall Giveaway for Excel Nerds? There's some truly frightening stuff there! The entry deadline is tomorrow at noon, Eastern Daylight Time, so get moving if you haven't entered already.

My Horror Story

One of my Excel horror stories involves sheet names. I set up a client's workbook with pre-formatted data entry sheets, so sales managers could plan their annual product promotions. They would rename the sheets while working, to make it easier to navigate the completed workbook.
On a hidden summary sheet, I added formulas to calculate the sheet names. Then, INDIRECT formulas pulled data from specific cells on each sheet, and other formulas created grand totals. At the front of the workbook, the summary data was displayed in a monthly calendar, for sales managers to review. It was a work of art!

The Scary Phone Call

Everything worked well in testing, so we distributed the files to all the sales managers, and they started filling in their data. The next day, the phone rang – some of the workbooks were "broken." Budget deadlines were looming, and the sales managers with broken files were in a panic. They sent me a couple of problem files, so I could figure out what was wrong.
On the Summary sheet, some of the formulas were working correctly, but others showed #REF! errors. Comparing the good and bad sheets, I couldn't see any problems with the data that had been entered.
Finally, after checking a few of the problem sheets, I spotted a similarity. All of them included an apostrophe in the sheet name! I removed the apostrophes, and the problem was solved. All the data showed up in the summary sheets, and the world was in harmony once again.

Sheet Naming Rules

I hadn't anticipated that problem, since I never use apostrophes in sheet names. They're valid characters for a sheet name, but maybe they shouldn't be.
It's hard to find the sheet naming rules in Excel's help, but you may have seen an error message that lists them.

  1. The name can't be more than 31 characters, and you can't leave the sheet tab blank
  2. Only a few characters are invalid:

  : / ? * [ ]


Sheet Naming Suggestions

In addition to those rules, I have a couple of guidelines of my own.

  1. Use only letters, numbers and underscores in sheet names. Sometimes I have to use a space character, if a client requests specific sheet names, but I try to avoid it. For example, I'd use SalesData or Sales_Data, not Sales Data.
  2. Use different names for sheets and named ranges, to avoid confusion.

Your Sheet Naming Rules

  • What kind of names do you use for worksheets?
  • Any characters that you avoid or problems you've run into?


0 thoughts on “Your Sheet Names Are Killing My Formulas”

  1. Dear Debra,
    Thabnks for a most interesting blog, I have learnt heaps from you.

    The formula to calculate the sheet names, was that an Excel formula or VBA. I know how to do it in VBA, but not with an Excel formula.


  2. I'd suggest to avoid the exclamation mark in sheetnames too, formulas will become very confusing:

    ='Sheet1 !'!A2

    Note that for Excel XP and older you can even use the [ and ] in a sheetname, Excel 2003 and higher prevent this.

  3. Hi Debra,
    I myself don't have any sheet-naming rules. That is probably due to the fact that I rarely use INDIRECT() and other volatile functions as a general rule. However, if I desperately needed to use INDIRECT(), I'd probably have as a rule something like this (assuming the sheet names are correct, say, coming from a defined name containg =GET.WORKBOOK(1+0*NOW())):


    1. duplicate any apostrophes in the sheet name
    2. add opening and closing apostrophes to the sheet name

    My 2 cents.

  4. Hi Debra,

    I use the same naming convention as yourself. Underscores where psaces necessary. I find that when setting up a contents page with links, the links don't get along well with the spaces in the sheet names. Underscores are generally no prob!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.