I recently spent some time working out the rules that apply to worksheet name quoting in Excel. Here are some notes on what I found. If there are any examples not matched by these rules please let me know below.
Worksheet names in Excel need to be single quoted when they are used in range
reference if they contain non-word characters or if they look like cell
references. For example the formula =Sheet1!A1 would appear as ='Sheet 1'!A1
if the referenced sheet name "Sheet 1" contains a space.
The specification that dictates whether an Excel sheet name should be quoted
hasn't been published so the following set of rules have be obtained by
observation and by creating and examining Excel files with different worksheet
names.
Sheet names that contain anything other than a word character or a dot . must
be quoted. In regex terms this would be [^\w\.]. Word characters include
the digits 0-9, underscore _ and Unicode word characters. Excel also treats
Emojis as word characters (although strictly they aren't).
So the following sheet names are quoted: 'Sheet 1', Sheet-1',
'Sheet⟦1'.
The following aren't quoted: Sheet.1, Sheet_1, École1, Sheet😀.
In addition any single quotes within the sheet name (which will cause the
overall sheet name to be quoted under this rule) must in turn be double
quoted. Thus Sheet'1 is quoted as 'Sheet''1' and Sheet''1 is quoted as
'Sheet''''1'.
Sheet names that start with anything other than a non-digit/non-dot word
character must be quoted. In regex terms this would be ^[\d\.]. In
addition, sheet names with an Emoji character in the first position must also
be quoted.
So the following sheet names are quoted: '.Sheet1', '1Sheet1',
'😀Sheet'.
The following aren't quoted: Sheet1, _Sheet1.
Sheet names must not be a valid A1 style cell reference. Valid means that
the row and column range values must also be within Excel's row limits 1 -
1,048,576 and column limits A - XFD. This rule is also case insensitive.
So the following sheet names are quoted: 'A1', 'a1', 'XFD1',
'A1048576', 'XFD1048576'.
The following aren't quoted due to the row/column range being invalid: A,
A0, XFD, A1048577, XFE1.
Sheet names must not start with a valid R1C1 style cell reference.
Other characters after the valid R1C1 reference are ignored in this rule.
Valid means that the row and column range values must also be within Excel's
row limits 1 - 1,048,576 and column limits 1 - 16,384. This rule is also case
insensitive.
So the following sheet names are quoted: 'R', 'C', 'RC', 'rc',
'R1', 'C1', 'RC1', R1_some_other_text, 'C16384Z', 'R1048576Z'.
The following aren't quoted due to the row/column range being invalid:
R0C0, C16385Z, R1048577Z.
There are a few edge cases to this rule:
Sheet names that start with C and RC and that are followed by digits
may need to be quoted even if the column range exceeds 16,384 since they
may qualify as A1 cell references. For example C16385Z is unquoted (>
16,384 and has trailing text) but 'C16385' is quoted because it qualifies
as A1 cell references under Rule 3.
Sheet names that start with a negative R1C1 style cell reference such as
'R-1 or 'C-1' are always quoted even if the range isn't valid due to
the presence of the the minus - character under Rule 1.
Only the first part of the R1C1 reference needs to be valid for sheet
name to be quoted. So 'R1C16385' is quoted since the R1 part is valid
even though the C16385 part isn't. The C16385 part is probably just
treated as trailing text in this case.
One other restriction to bear in mind is that worksheet names cannot contain any
of the following characters: [ ] : * ? / \. This rule for worksheet names are
explained in the Microsoft Office documentation on how to [Rename a worksheet].
Question
I recently spent some time working out the rules that apply to worksheet name quoting in Excel. Here are some notes on what I found. If there are any examples not matched by these rules please let me know below.
Worksheet names in Excel need to be single quoted when they are used in range reference if they contain non-word characters or if they look like cell references. For example the formula
=Sheet1!A1would appear as='Sheet 1'!A1if the referenced sheet name "Sheet 1" contains a space.The specification that dictates whether an Excel sheet name should be quoted hasn't been published so the following set of rules have be obtained by observation and by creating and examining Excel files with different worksheet names.
Sheet names that contain anything other than a word character or a dot
.must be quoted. In regex terms this would be[^\w\.]. Word characters include the digits0-9, underscore_and Unicode word characters. Excel also treats Emojis as word characters (although strictly they aren't).So the following sheet names are quoted:
'Sheet 1',Sheet-1','Sheet⟦1'.The following aren't quoted:
Sheet.1,Sheet_1,École1,Sheet😀.In addition any single quotes within the sheet name (which will cause the overall sheet name to be quoted under this rule) must in turn be double quoted. Thus
Sheet'1is quoted as'Sheet''1'andSheet''1is quoted as'Sheet''''1'.Sheet names that start with anything other than a non-digit/non-dot word character must be quoted. In regex terms this would be
^[\d\.]. In addition, sheet names with an Emoji character in the first position must also be quoted.So the following sheet names are quoted:
'.Sheet1','1Sheet1','😀Sheet'.The following aren't quoted:
Sheet1,_Sheet1.Sheet names must not be a valid
A1style cell reference. Valid means that the row and column range values must also be within Excel's row limits 1 - 1,048,576 and column limitsA-XFD. This rule is also case insensitive.So the following sheet names are quoted:
'A1','a1','XFD1','A1048576','XFD1048576'.The following aren't quoted due to the row/column range being invalid:
A,A0,XFD,A1048577,XFE1.Sheet names must not start with a valid
R1C1style cell reference. Other characters after the validR1C1reference are ignored in this rule. Valid means that the row and column range values must also be within Excel's row limits 1 - 1,048,576 and column limits 1 - 16,384. This rule is also case insensitive.So the following sheet names are quoted:
'R','C','RC','rc','R1','C1','RC1',R1_some_other_text,'C16384Z','R1048576Z'.The following aren't quoted due to the row/column range being invalid:
R0C0,C16385Z,R1048577Z.There are a few edge cases to this rule:
Sheet names that start with
CandRCand that are followed by digits may need to be quoted even if the column range exceeds 16,384 since they may qualify asA1cell references. For exampleC16385Zis unquoted (> 16,384 and has trailing text) but'C16385'is quoted because it qualifies asA1cell references under Rule 3.Sheet names that start with a negative
R1C1style cell reference such as'R-1or'C-1'are always quoted even if the range isn't valid due to the presence of the the minus-character under Rule 1.Only the first part of the
R1C1reference needs to be valid for sheet name to be quoted. So'R1C16385'is quoted since theR1part is valid even though theC16385part isn't. TheC16385part is probably just treated as trailing text in this case.One other restriction to bear in mind is that worksheet names cannot contain any of the following characters:
[ ] : * ? / \. This rule for worksheet names are explained in the Microsoft Office documentation on how to [Rename a worksheet].[Rename a worksheet]: https://support.office.com/en-ie/article/me-a-worksheet-3f1f7148-ee83-404d-8ef0-9ff99fbad1f9