When does Excel surround sheet names with single q

Question

I have an .xlsx file that opens in Excel. However, if I remove the single quotes around the sheet name (which does not contain spaces) in the defined names field in workbook.xml, Excel repairs the file by removing that defined name.

Can anyone tell the criteria when do we have to surround the sheetname with single quotes?

The files are present here : https://www.dropbox.com/sh/eziv9jlbpsppw83/AACwurdX_aOhbK4R06FRizd3a?dl=0

00007113-ENGLISH_original.xlsx - Opens in Mac Excel 2011

00007113-ENGLISH_exported.xlsx - Does not open (Repair dialog)

Answer 1

After some testing, I have found that Excel will quote the worksheet name if any of the following is true :

• it contains any space
• it contains any of these characters: ()'$,;-{} (and probably more, I did not test exhaustively)
• it is a valid cell reference in A1 notation, e.g. B1048576 is quoted, B1048577 is not
• it is a valid cell reference in R1C1 notation, e.g. RC, RC2, R5C, R-4C, RC-8, R, C
• it is a valid natural number

I'm making my answer community wiki, feel free to complete this.

Answer 2

I had this problem and found that the reason is as follows (a) Sheet names that have spaces get surrounded by the quotes (b) Sheet names that use underscore or simply do not have spaces are not

Answer 3

There seems not to be a general description.

But I can tell you why Excel surrounds the sheet name RC2ACQuotas with quotes in the reference 'RC2ACQuotas'!$A$1:$Q$213. It is because RC2 also is a cell reference in R1C1 notation. It means RowColumn2 = this row column 2.

This seems not to be consequent since Excel surrounds a sheet name A123 with quotes but A123Test not. But sheet names starting with a cell reference in R1C1 notation will always be surrounded.