Save content
Have you found this content useful? Use the button above to save it to your profile.

Excel tip: Forbidden characters in sheet names

by
14th Jul 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

AccountingWEB technology editor-at-large David H Ringstrom recently invoked the memory of US comedian George Carlin in a recent tutorial on Excel worksheet naming conventions. This tip sets out what you can and cannot do.

George Carlin’s routine concerned dirty words you cannot say on television, but in their wisdom, Microsoft’s Excel programmers also found it necessary to ban some of the characters on your keyboard from making an appearance in worksheet names.

You also aren’t allowed to name an Excel worksheet “History”. Here’s why.

Excel uses default worksheets names starting with Sheet1, Sheet2, and so on. It’s relatively easy to change a sheet name in the following ways shown here:

You can double-click or right-click on a worksheet tab to rename it

  • Double-click the worksheet tab, and type a new name.
  • Right-click on the worksheet tab, and choose Rename as illustrated in Figure 1.
  • Display the Visual Basic Editor, turn on both the Project Explorer and Properties windows, and then change the Name property as illustrated in Figure 2. Notice the nuance here; you can’t change the (Name) property as that’s a hidden code name for the worksheet that can be used within Excel macros.
  • Worksheet tab names can also be changed by way of Excel macros, as well.

You can also rename a worksheet within the Visual Basic Editor:

You can also rename a worksheet within the Visual Basic Editor

Whichever technique you choose, remember that worksheet tab names cannot exceed 31 characters - and you won’t be allowed to use any of the following seven characters:

  1. \
  2. /
  3. *
  4. [
  5. ]
  6. :
  7. ?

When you attempt to enter any of the forbidden symbols, you won’t see any kind of error message. Instead, nothing will actually appear in the worksheet tab when you type. Normal alpha-numeric characters are acceptable and if you need to separate or highlight your sheet titles you can use dashes, punctuation such as commas, full stops, question marks and the exclamation point, and symbols including:

  •  | (pipe symbol)
  • + (plus)
  • @
  • #
  • $
  • £
  • ^
  • &
  • (
  • )
  • _ (underscore)

If you have a taste for Spanish punctuation, you can also use an upside down question mark by holding down the Alt key while type 0191 on the number pad.

As mentioned, if you try to use the word “History” as a sheet name, you’ll be met with the response, “History is a reserved name.”

The development team at Microsoft appear to have chosen “History” for a hidden worksheet to handle Excel’s Track Changes facility. Ringstrom was a little frustrated with this decision.

“If I were one of the programmers working on Excel, I’d have the last laugh and name that tab “[\*:/]?” instead of making legions of Excel users say “@^#$*!” when they can’t use the word History as a sheet name,” he noted.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.