Excel Sheet Names Cause Formula Errors

Do you have Excel horror stories, that you like to tell around the campfire, to scare your friends?

One of my recent Excel horror stories involves Excel 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 data entry sheets while working, to make it easier to navigate the completed workbook.

Hidden Sheet With Formulas

On a hidden summary sheet in the workbook, I added formulas to calculate the sheet names.

In another column on that sheet, a few Excel INDIRECT function formulas pulled data from specific cells on each data entry 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.

Summary Sheet Formula Errors

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, at first glance.

Summary Sheet Formula Errors
Summary Sheet Formula Errors

Sheet Name Apostrophes

Finally, after checking a few of the problem sheets, I spotted a similarity.

  • All of the problem sheets 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.

Note: For the next version of the workbook, I updated the workbook’s Summary sheet formulas, using the Excel SUBSTITUTE function.

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 Excel error message that lists them.

  1. The name can’t be more than 31 characters
  2. You can’t leave the sheet tab blank
  3. Only a few characters are listed as invalid, like the following ones from the error message below:
  • : \ / ? * [ ]
  • colon, back slash, forward slash, question mark, asterisk, open square bracket, close square bracket

Apostrophes are okay though!

Excel error message: You typed an invalid name for a sheet or chart"
Excel error message: You typed an invalid name for a sheet or chart”

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.
    • And please – don’t use apostrophes!
  2. Use different names for worksheets and named ranges, to avoid confusion.

There’s lots more information about Excel names on my Contextures site, and sample files to download.

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 “Excel Sheet Names Cause Formula Errors”

  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.

    Regards
    Kanti

  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())):

    =INDIRECT(“‘”&SUBSTITUTE(A1,”‘”,”””)&”‘!A1?)

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

    My 2 cents.

  4. Dear Debra,

    Thanks for pointing me to the Chip Pearson page. The Cell formula slipped my mind.

    regards

    kanti

  5. 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 to Jan Karel Pieterse Cancel reply

Your email address will not be published.

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